T-SQL 根據給定列中的值對記錄集進行分組和求和

舉個例子：

SELECT * FROM [Orders] WHERE DATE = '2021-09-10'

Date        FirstName  LastName   Adults  Item       Description
----------  ---------  ---------  ------  ---------  --------------
2021-09-10  David      Smith      3       Breakfast  Eggs and beans
2021-09-10  Jennifer   Ford       2       Breakfast  Eggs and beans
2021-09-10  Thomas     Oliver     1       Breakfast  Eggs and beans
2021-09-10  Lisa       Parker     2       Breakfast  Continental
2021-09-10  David      Smith      3       Dinner     Steak
2021-09-10  Tom        Haverford  1       Lunch      Hamburger
2021-09-10  Reanu      Keeves     2       Dinner     Steak

..我需要分組和求和，以便所有不同的

$$ Breakfast $$項目組合為一行；求和$$ Adults $$，而其餘作為單獨的記錄輸出：

Date        FirstName  LastName   Adults  Item       Description
----------  ---------  ---------  ------  ---------  --------------
2021-09-10                        6       Breakfast  Eggs and beans
2021-09-10                        2       Breakfast  Continental
2021-09-10  David      Smith      3       Dinner     Steak
2021-09-10  Tom        Haverford  1       Lunch      Hamburger
2021-09-10  Reanu      Keeves     2       Dinner     Steak

有沒有一種方便的方法可以將其建構為單個查詢？

您不需要UNION，您可以在一個查詢中執行此操作。

這只是仔細選擇分組值的問題：

SELECT
   [Date],
   FirstName = CASE WHEN Item <> 'Breakfast' THEN FirstName END,
   LastName  = CASE WHEN Item <> 'Breakfast' THEN LastName  END,
   Adults = SUM(Adults),
   Item,
   Description
FROM
   [Orders]
WHERE
   [Date] = '2021-09-10'
GROUP BY
   [Date],
   Item,
   Description,
   CASE WHEN Item <> 'Breakfast' THEN FirstName END,
   CASE WHEN Item <> 'Breakfast' THEN LastName END;

db<>小提琴

您可以通過這種方式聯合兩個查詢：

SELECT
   [Date],
   '' as FirstName,
   '' as LastName,
   SUM(Adults) as Adults,
   Item,
   Description
FROM
   [Orders]
GROUP BY
   [Date],
   Item,
   Description
HAVING
   [Date] = '2021-09-10'
   AND Item = 'Breakfast'

UNION

SELECT
   [Date],
   FirstName,
   LastName,
   Adults,
   Item,
   Description
FROM
   [Orders]
WHERE
   [Date] = '2021-09-10'
   AND Item <> 'Breakfast'    

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/299376