|
Тестовое задание SQL
#40011765
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Всем привет!
Прошу в помощи с запросом для БД, только начал изучать SQL.
Есть база данных(скриншот задание):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE IF NOT EXISTS `cert` (
`type` varchar(200) NOT NULL,
`amount` int(4) NOT NULL,
`year` int(4) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `cert` (`type`, `amount`, `year`) VALUES
('Квалифицированный', '500', '2018'),
('Неквалифицированный', '90', '2018'),
('Квалифицированный', '300', '2019'),
('Неквалифицированный', '250', '2019'),
('Квалифицированный', '600', '2020'),
('Неквалифицированный', '500', '2020');
Написал: 1.
2.
3.
SELECT type, (SELECT amount FROM cert where year=2018 ) as "2018", amount as "2019", amount as "2020"
FROM cert
group by type
1.
2.
3.
SELECT distinct amount
FROM cert
where year=2018
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT type, (SELECT max(amount) FROM cert where year=2018) as "2018",
(SELECT max(amount) FROM cert where year=2019) as "2019",
(SELECT max(amount) FROM cert where year=2020) as "2020"
FROM cert
UNION
SELECT type, (SELECT min(amount) FROM cert where year=2018 limit 1) as "2018",
(SELECT min(amount) FROM cert where year=2019 limit 1) as "2019",
(SELECT min(amount) FROM cert where year=2020 limit 1) as "2020"
FROM cert
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select *
from(
SELECT type, (SELECT max(amount) FROM cert where year=2018) as "2018",
(SELECT max(amount) FROM cert where year=2019) as "2019",
(SELECT max(amount) FROM cert where year=2020) as "2020"
FROM cert
UNION
SELECT type, (SELECT min(amount) FROM cert where year=2018 limit 1) as "2018",
(SELECT min(amount) FROM cert where year=2019 limit 1) as "2019",
(SELECT min(amount) FROM cert where year=2020 limit 1) as "2020"
FROM cert
) as result
group by type
Но выводит не все необходимые значения.
Можете помочь с написанием запроса?
Модератор: Тема закрыта, поскольку 22220536 |
|
|
