Объединение запросов, оператор Union

@Skrejet_78 · Регистрация: 14.04.2021

Студворк — интернет-сервис помощи студентам

Всем привет!
Ни как не получается довести скрипт до логического завершения.
Прошу Вашей помощи.
Задача:
Создать отчет затрат семьи Quincey за 3 квартал 2005 года. Отсортировать по возрастанию member_name, затем по убыванию поля costs. Последней строкой вывести итог по всей семье. Для этого необходимо под колонкой good_name вывести слово "Total:", а под costs - общую сумму всех затрат, оставив первые два поля пустыми:

Вот на чём остановился я:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH report 
    AS (
        SELECT  
 FamilyMembers.member_name, 
 FamilyMembers.status, 
 Goods.good_name, 
 SUM (Payments.amount * Payments.unit_price) AS costs 
 --, Payments.date --выведено для визуального отбора по кварталам--
FROM   FamilyMembers
INNER JOIN Goods ON FamilyMembers.member_id = Goods.good_id
INNER JOIN Payments ON FamilyMembers.member_id =   Payments.payment_id
WHERE FamilyMembers.member_name LIKE '%Quincey' AND 
Payments.date > '2005-06-01' AND Payments.date < '2005-10-01'
GROUP BY FamilyMembers.member_id
    )
SELECT * FROM report 
UNION 
SELECT 
member_name = NULL, 
STATUS = NULL, 
'Total:', costs
FROM report
ORDER BY member_name ASC, costs DESC

@Аватар · 21.04.2021, 10:12

все там соблюдается, его же про возрастанию нужно ) это называется смотрю в книгу вижу фигу )

@alena86 · 21.04.2021, 11:05

Добрый день. Тоже решаю эту задачку..
Я новичок, прошу не судить строго..
Вам не кажется, что у вас неправильное объединение таблиц по полям?
FamilyMembers.member_id не должно равняться Goods.good_id, а FamilyMembers.member_id не равно Payments.payment_id..

Мой код на данный момент:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH report AS (SELECT member_name, STATUS, good_name, amount*unit_price AS costs
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id
    JOIN goods ON good = good_id
    WHERE MONTH(DATE) IN (07,08,09)
    )
 
SELECT * FROM report
UNION 
SELECT '',
'',
"Total:",
(SELECT SUM(costs) FROM report)
ORDER BY member_name, costs DESC

Пока не поняла, почему ругается на группировку в подзапросе report, поэтому пока убрала..

@alena86 · 21.04.2021, 19:14

Итоговый код, который принял тренажер:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH report AS (SELECT member_name, STATUS, good_name, amount*unit_price AS costs
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id
    JOIN goods ON good = good_id
    WHERE MONTH(DATE) IN (07,08,09)
    )
 
SELECT * FROM report
UNION 
SELECT NULL,
NULL,
"Total:",
(SELECT SUM(costs) FROM report) FROM report
ORDER BY CASE WHEN member_name IS NULL THEN 1 ELSE 0 END,member_name, costs DESC

@SQL_MISTRESS · 01.06.2021, 17:03

ап

этот код работает и в нем нет команд которых до этого не было в учебнике:

SQL
1
2
3
4
5
6
7
8
9
10
11
WITH a AS 
(SELECT member_name, STATUS, good_name, SUM(amount*unit_price) AS costs 
FROM Payments, Goods, FamilyMembers
WHERE good_id=good AND member_id=family_member
AND member_id IN (SELECT family_member FROM Payments)
AND YEAR(DATE)=2005 AND quarter(DATE)=3
GROUP BY member_name, STATUS, good_name
ORDER BY member_name, costs DESC)
SELECT * FROM a
UNION
SELECT NULL, NULL, 'Total:', SUM(costs) AS costs FROM a

строка 5 кстати не нужна без нее тоже все ок выходит и вроде как при других условиях она тоже лишняя

@Irina-1989 · 01.06.2021, 22:09

T-SQL
1
2
WHERE FamilyMembers.member_name LIKE '%Quincey' AND 
Payments.date > '2005-06-01' AND Payments.date < '2005-10-01'

- период не правильный , 3 квартал это 01.07.2005 по 30.09.2005

T-SQL
1
INNER JOIN Goods ON FamilyMembers.member_id = Goods.good_id

- вот это вообще не понятно для чего? получается у тебя в таблицу не попадают товары, которые не совпадают по member_id.

Я по другому решила.... Но тоже пишет ответ не верный....

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH report (member_name, status, good_name,costs)
    AS (
        SELECT (SELECT member_name FROM FamilyMembers WHERE family_member = member_id) AS member_name,(SELECT status FROM FamilyMembers WHERE family_member = member_id) AS status, (SELECT good_name FROM Goods WHERE Payments.good = Goods.good_id) AS good_name,
    amount*unit_price AS costs 
        FROM Payments  
        WHERE date BETWEEN  '2005-07-01' AND '2005-09-30'
        HAVING member_name LIKE '% Quincey')
SELECT * FROM report 
 
UNION 
SELECT member_name =NULL,status = NULL, 'TOTAL:', (SELECT SUM(costs)  FROM  report) 
FROM report 
ORDER BY CASE WHEN member_name IS NULL THEN 1 ELSE 0 END,member_name, costs DESC

@n1ckgorbachev · 29.06.2021, 21:52

Мой вариант решения, который сработал.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH report AS (
        SELECT  member_name, 
                STATUS, 
                good_name, 
                SUM(amount*unit_price) AS costs 
        FROM FamilyMembers JOIN Payments
        ON member_id = family_member
        JOIN Goods ON good_id = good
        WHERE member_name LIKE "%Quincey" 
        AND DATE LIKE "2005-07%" 
        OR DATE LIKE "2005-08%"
        OR DATE LIKE "2005-09%"
        GROUP BY member_name, STATUS, good_name
        ORDER  BY member_name, costs DESC
    )
SELECT * FROM report 
UNION 
SELECT NULL, NULL, "Total:", SUM(costs) FROM report

@CactiLian · 12.07.2021, 13:07

Сообщение от n1ckgorbachev

DATE LIKE "2005-07%"
OR DATE LIKE "2005-08%"
OR DATE LIKE "2005-09%"

эти строчки можно вполне заменить на

SQL
1
DATE BETWEEN  '2005-07-01' AND '2005-09-30'

@ra108 · 21.01.2022, 10:53

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH report 
    AS (
        SELECT  
            member_name, 
            STATUS, 
            good_name, 
            SUM(amount * unit_price) AS costs 
        FROM
            FamilyMembers,
            Payments,
            Goods
        WHERE  
            family_member = member_id
            AND good_id = good
            AND member_name LIKE '%Quincey%'
            AND DATE BETWEEN '2005-07-01T00:00:00.000Z' AND '2005-10-01T00:00:00.000Z'
        GROUP BY
            member_name, 
            STATUS, 
            good_name
    )
SELECT * FROM report 
UNION 
SELECT
    NULL AS member_name, 
    NULL AS STATUS,
    'Total:' AS good_name
    ,SUM(costs) AS costs
FROM   report 
ORDER BY CASE WHEN member_name IS NULL THEN 1 ELSE 0 END,member_name ASC, costs DESC

@Groofi · 12.05.2022, 10:48

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH report AS (
    SELECT member_name, STATUS, 
        good_name , SUM(unit_price * amount) AS costs
    FROM FamilyMembers, Goods, Payments
    WHERE good_id=good AND member_id=family_member AND member_id  
        AND YEAR(DATE)=2005 AND quarter(DATE)=3
    GROUP BY member_name, STATUS, good_name
    ORDER BY member_name, costs DESC
)
SELECT * FROM report
UNION
SELECT DISTINCT  NULL, NULL, "Total:", SUM(costs) AS costs
FROM report

По моему самое красивое и краткое решение!

@PaulWist · 12.05.2022, 12:04

Сообщение от Groofi

T-SQL
1
AND YEAR(DATE)=2005 AND quarter(DATE)=3

По моему самое красивое и краткое решение!

Это называется "смерть серверу", особенно quarter(DATE)

@Artem2212 · 17.07.2022, 12:37

ORDER BY CASE WHEN member_name IS NULL THEN 1 ELSE 0 END,member_name, costs DESC

Начал изучать SQL. Как последняя строка сортировку делает с case?

member_name через запятую после case уже отдельно сортируется?

@Аватар · 17.07.2022, 12:54

Сообщение от Artem2212

Как последняя строка сортировку делает с case?

с кэйсом ни как, с его результатом - с 0 или 1

Сообщение от Artem2212

member_name через запятую после case уже отдельно сортируется?

посмотри результат сортировки и вопрос отпадет )

@Skrips91 · 01.09.2022, 15:18

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Самое краткое))
 
WITH report AS (
    SELECT member_name, STATUS, 
        good_name , SUM(unit_price * amount) AS costs
    FROM FamilyMembers, Goods, Payments
    WHERE good_id=good AND member_id=family_member AND member_id  
        AND YEAR(DATE)=2005 AND quarter(DATE)=3
    GROUP BY 1,2,3
    ORDER BY 1,4 DESC
)
SELECT * FROM report
UNION
SELECT NULL, NULL, "Total:", SUM(costs) AS costs
FROM report

Новые блоги и статьи Все статьи Все блоги /
Hyper-V: Компьютер должен поддерживать доверенный платформенный модуль 2.0. Maks 31.08.2026 При установке Windows 11 на виртуальную машину Hyper-V 2-го поколения вылезла такая ошибка: Решение: в параметрах виртуальной машины, в разделе "Безопасность" (Security) активировать флаг. . .	Архитектура биовида Стива в Майнкрафте: Зачем бонобо кубический каннибализм anaschu 30.08.2026 Кубический Вагинокапитализм в Minecraft: Математический инвариант ОДУ и рок Стивов-бонобо Главная задача разработанной «Модели Всего» — наглядно продемонстрировать наличие системной «судьбы». . .	Оттачиваю умение писать js программы. russiannick 30.08.2026 Проектом выходного дня стало написание Книги шифров Виженера. Итогом стала версия 200, синий туман. Синий туман назван так, потому что замораживает текст под собой. Нажатие синих кнопок управляют. . .	мат медиц модель 30. презентация проекта anaschu 27.08.2026 хоп хоп хоп хидахоп, а я кладую))
Как у меня протекала болезнь zorxor 27.08.2026 Здравствуйте, друзья! Эта запись блога предназначена именно для вас - для моих дорогих друзей, которые знали меня лично. Чтобы ответить на вопрос - а что же со мной произошло на самом деле? Я учился. . .	Нашел вот забавное видео о измерениях. Лучшее что я видел на эту тему kumehtar 26.08.2026 ILETXiw9bMQ Основная суть и тезисы по измерениям: 0D (Нулевое измерение): точка, не имеющая длины, ширины, высоты или объема. Объект не может перемещаться в 0D. 1D (Первое измерение):. . .	[EasyBuilder Pro] Памятка по разработке для панелей Weintek ФедосеевПавел 26.08.2026 Памятка по разработке для панелей Weintek ВВЕДЕНИЕ Ранее, при реализации проектов основное внимание уделял разработке управляющей программы для контроллера, а панели оператора доставалось время. . .	Модель по догадкам anaschu 25.08.2026 Прошло две недели. Я уже рассказывал, как разговаривал с сотрудниками у сортировки и как понял, что главная ветка — не про приёмку, а про отбор. Но тогда я думал, что понял механику. На этой неделе я. . .

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	21.04.2021, 10:12
	все там соблюдается, его же про возрастанию нужно ) это называется смотрю в книгу вижу фигу ) 0

@Artem2212 0 / 0 / 0 Регистрация: 17.07.2022 Сообщений: 1
	17.07.2022, 12:37
	ORDER BY CASE WHEN member_name IS NULL THEN 1 ELSE 0 END,member_name, costs DESC Начал изучать SQL. Как последняя строка сортировку делает с case? member_name через запятую после case уже отдельно сортируется? 0