Объединение запросов, оператор 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

Новые блоги и статьи Все статьи Все блоги /
мат медиц модель 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 Прошло две недели. Я уже рассказывал, как разговаривал с сотрудниками у сортировки и как понял, что главная ветка — не про приёмку, а про отбор. Но тогда я думал, что понял механику. На этой неделе я. . .	Запись в регистр сведений независимо от заполненности табличной части Maks 25.08.2026 Реализация из решения ниже выполнена на нетиповом документе с несколькими табличными частями, разработанного в КА2. Задача: Обеспечить запись документа в регистр сведений независимо от. . .	Ноутбук Альфария kumehtar 24.08.2026 Встретился тут в сети ноутбук Альфария, примарха Альфа-Легиона. Хотя возможно, это ноутбук Омегона, разумеется. Ну как вам?	Мастера простых решений DevAlt 23.08.2026 В сишарп стэках winforms, да и wpf существует сложная система связывания источниках данных и элементов формы(текстовые поля и метки), опирается все это на технологию событий и мета. . .

@Аватар 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