SQLSum對具有臨時創建的列的條目的多個列進行求和

我有下表，例如：

它包含工人作業的日期和小時數。

現在我想創建一個如下所示的表格，并選擇顯示每個作業日的小時數。“計數”列應代表工人有作業時間的天數。“總和”列應匯總本周的小時數。所以最終的結果應該是這樣的：

到目前為止，我的宣告是：

SELECT worker,
CASE WHEN DATEPART(weekday,date) = 1 THEN amount END AS mon,
CASE WHEN DATEPART(weekday,date) = 2 THEN amount END AS tue,
CASE WHEN DATEPART(weekday,date) = 3 THEN amount END AS wed,
CASE WHEN DATEPART(weekday,date) = 4 THEN amount END AS thu,
CASE WHEN DATEPART(weekday,date) = 5 THEN amount END AS fri,
CASE WHEN DATEPART(weekday,date) = 6 THEN amount END AS sat,
CASE WHEN DATEPART(weekday,date) = 7 THEN amount END AS sun
FROM table

所以現在我需要幫助來獲取最后兩列。誰能解釋一下，如何在一個條目中匯總/計算多列的值？

謝謝你。

uj5u.com熱心網友回復：

我們使用該函式SUM以便能夠聚合不同的行。SELECT我們在和 中添加周數，GROUP BY以便分隔周并知道我們正在查看一年中的哪一周。如果同一張表的使用時間足夠長，我們還可以添加年份。

SET DATEFIRST 1;
SELECT 
  DATEPART(week, date) AS "week",
  worker,
  CASE WHEN DATEPART(weekday,date) = 1 THEN amount END ) AS mon,
  SUM( CASE WHEN DATEPART(weekday,date) = 2 THEN amount END ) AS tue,
  SUM( CASE WHEN DATEPART(weekday,date) = 3 THEN amount END ) AS wed,
  SUM( CASE WHEN DATEPART(weekday,date) = 4 THEN amount END ) AS thu,
  SUM( CASE WHEN DATEPART(weekday,date) = 5 THEN amount END ) AS fri,
  SUM( CASE WHEN DATEPART(weekday,date) = 6 THEN amount END ) AS sat,
  SUM( CASE WHEN DATEPART(weekday,date) = 7 THEN amount END ) AS sun,
  COUNT(DISTINCT DATEPART(weekday,date) ) AS "count",
amount AS "sum"
FROM table
GROUP BY 
  DATEPART(week, date),
  worker
ORDER BY 
  DATEPART(week, date),
  worker;

uj5u.com熱心網友回復：

條件聚合是一個選項（不要忘記使用 設定一周的第一天SET DATEFIRST）：

SET DATEFIRST 1

SELECT 
   worker,
   SUM(CASE WHEN DATEPART(weekday, date) = 1 THEN amount END) AS mon,
   SUM(CASE WHEN DATEPART(weekday, date) = 2 THEN amount END) AS tue,
   SUM(CASE WHEN DATEPART(weekday, date) = 3 THEN amount END) AS wed,
   SUM(CASE WHEN DATEPART(weekday, date) = 4 THEN amount END) AS thu,
   SUM(CASE WHEN DATEPART(weekday, date) = 5 THEN amount END) AS fri,
   SUM(CASE WHEN DATEPART(weekday, date) = 6 THEN amount END) AS sat,
   SUM(CASE WHEN DATEPART(weekday, date) = 7 THEN amount END) AS sun,
   COUNT(DISTINCT DATEPART(weekday, date)) AS [count],
   SUM(amount) AS [sum]
FROM (VALUES
   ('jeff', CONVERT(date, '20220404'), 4.00),
   ('jeff', CONVERT(date, '20220405'), 2.00),
   ('jeff', CONVERT(date, '20220408'), 3.50),
   ('dave', CONVERT(date, '20220404'), 1.00),
   ('dave', CONVERT(date, '20220407'), 6.50)
) t (worker, date, amount)
GROUP BY worker
ORDER BY worker

結果：

如果要根據時間間隔對輸入資料進行匯總，則需要另一種說法：

SET DATEFIRST 1

SELECT 
   worker,
   --DATEPART(week, date) AS week,
   DATEPART(month, date) AS month,
   SUM(CASE WHEN DATEPART(weekday, date) = 1 THEN amount END) AS mon,
   SUM(CASE WHEN DATEPART(weekday, date) = 2 THEN amount END) AS tue,
   SUM(CASE WHEN DATEPART(weekday, date) = 3 THEN amount END) AS wed,
   SUM(CASE WHEN DATEPART(weekday, date) = 4 THEN amount END) AS thu,
   SUM(CASE WHEN DATEPART(weekday, date) = 5 THEN amount END) AS fri,
   SUM(CASE WHEN DATEPART(weekday, date) = 6 THEN amount END) AS sat,
   SUM(CASE WHEN DATEPART(weekday, date) = 7 THEN amount END) AS sun,
   COUNT(DISTINCT DATEPART(weekday, date)) AS [count],
   SUM(amount) AS [sum]
FROM (VALUES
   ('jeff', CONVERT(date, '20220404'), 4.00),
   ('jeff', CONVERT(date, '20220405'), 2.00),
   ('jeff', CONVERT(date, '20220408'), 3.50),
   ('dave', CONVERT(date, '20220404'), 1.00),
   ('dave', CONVERT(date, '20220407'), 6.50)
) t (worker, date, amount)
--GROUP BY worker, DATEPART(week, date)
--ORDER BY worker, DATEPART(week, date)
GROUP BY worker, DATEPART(month, date)
ORDER BY worker, DATEPART(month, date)

uj5u.com熱心網友回復：

相反，我會做很多聚合、案例和日期部分，我會用它做一個資料透視表，如下所示。（請注意，我的 days-weekdays 參考可能與您的不同）

測驗資料：

declare @tbl table (worker varchar(20), [date] date, amount dec(5,2))
INSERT INTO @tbl
SELECT              'jeff', '04-04-2022', 4.00
UNION SELECT    'jeff', '05-04-2022', 2.00
UNION SELECT    'jeff', '08-04-2022', 3.50
UNION SELECT    'dave', '04-04-2022', 1.00
UNION SELECT    'dave', '07-04-2022', 6.50

實際代碼：

SELECT 
        worker
    , [1] as sun
    , [2] as mon
    , [3] as tue
    , [4] as wed
    , [5] as thu
    , [6] as fri
    , [7] as sat
    , [count]
    , [sum]
FROM (select 
            worker
        , amount
        , datepart(weekday, t.[date]) dp
        , COUNT(amount) OVER( partition by worker) as [count]
        , SUM(amount) OVER( partition by worker) as [sum]
    from @tbl t
) p
PIVOT(
    SUM(amount)
    FOR dp in ([1], [2], [3], [4], [5], [6], [7])
) piv

標籤：sql sql服务器 数据库 tsql

上一篇：需要從列名基于另一個表中的值的列中獲取值

下一篇：在同一請求中使用STRING_AGG和LAST_VALUE函式