在幾年內對多列使用COUNT()

我有一個查詢，顯示當前年份的井數。我想顯示過去 5 年的計數/年。我怎么能在單個查詢中做到這一點？

今年，我使用YEAR(GETDATE()). 在過去的五年中，我想我可以使用：YEAR(GETDATE())-1，YEAR(GETDATE())-2，YEAR(GETDATE())-23等我也想過一個CASE WHEN條款可以作業，但不知道如何使用它的下SELECT發言。

這是我當前的查詢：

SELECT
    COALESCE(w.WellType, 'Totals') AS 'WellTypes',
    COUNT(DISTINCT(w.WellID)) AS '2021'
FROM Well w
    LEFT JOIN Construct c ON c.WellKey = w.PKey
    LEFT JOIN ConstructDate cd ON c.PKey = cd.ConstructKey
WHERE
    YEAR(cd.EventDate) = YEAR(GETDATE())
    AND cd.Event = 'LATERALSTATUS'
    AND cd.Comment = 'PA'
GROUP BY ROLLUP(w.WellType)
ORDER BY
    CASE w.WellType
        WHEN 'OW' THEN 1
        WHEN 'GW' THEN 2
        WHEN 'D' THEN 3
        WHEN 'OWI' THEN 4
        WHEN 'WI' THEN 5
    END DESC

這是我目前的結果：

這就是我想得到的：

I'm working on getting this in db-fiddle but I've never used it before. I know this is super long but for now, here's the plain data in a table. The "EventDate", "Comment", and "Event" columns are from a table called, "ConstructDate". The "WellType" column is from a table called, "Well".

uj5u.com熱心網友回復：

當你需要不同的約束來聚合你想要的東西時，這很棘手。我不會使用計數，我會用 CASE 陳述句對實體進行求和。以下是您可以在 SQL Server 上的會話中運行的示例：

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP(
    WellType            NVARCHAR(10)
    ,EventDate          DATE
)
INSERT INTO #TEMP (WellType, EventDate)
VALUES ('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')
    ,('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')

SELECT 
    WellType
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) [THIS YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-1,GETDATE())) THEN 1 ELSE 0 END) [LAST YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-2,GETDATE())) THEN 1 ELSE 0 END) [2 YEARS AGO]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-3,GETDATE())) THEN 1 ELSE 0 END) [3 YEARS AGO]
FROM #TEMP
GROUP BY WellType

我還使用 GETDATE() 函式來確定要求和的年份。這應該做你想做的。

SELECT 陳述句之前和之后的輸出：

uj5u.com熱心網友回復：

如果沒有涉及的表和相關資料，我無法重現您自己的查詢，但是有多種方法可以將您需要的資料轉入列中。

一種這樣的方法是使用 a conditional case expression，另一種是使用window analytic functions：

select distinct welltype, 
    Count(case when Year(Eventdate)=Year(GetDate())-0 then 1 end) over(partition by welltype ) [2021],
    Count(case when Year(Eventdate)=Year(GetDate())-1 then 1 end) over(partition by welltype ) [2020],
    Count(case when Year(Eventdate)=Year(GetDate())-2 then 1 end) over(partition by welltype ) [2019],
    Count(case when Year(Eventdate)=Year(GetDate())-3 then 1 end) over(partition by welltype ) [2018],
    Count(case when Year(Eventdate)=Year(GetDate())-4 then 1 end) over(partition by welltype ) [2017]
from t 

演示小提琴

uj5u.com熱心網友回復：

不幸的是，我無法在沒有資料的情況下驗證請求。我會嘗試使用“Grouping Set”運算子結合“Case When”結構進行子查詢，然后使用“SUM”聚合函式。

With Cte As (Select YEAR(GetDate()) As [YEAR], YEAR(GetDate())-4 As [first]             
             Union All 
             Select [YEAR]-1 As [YEAR], [first]
             From cte
             Where [YEAR] > [first])
Select WellTypes, SUM([2021]), SUM([2020]), SUM([2019]), SUM([2018]), SUM([2017])
From
(Select
    COALESCE(w.WellType, 'Totals') As WellTypes,
    Case When cte.[YEAR] = YEAR(GetDate()) Then COUNT(DISTINCT(w.WellID)) End As [2021],
    Case When cte.[YEAR] = Year(GetDate())-1 Then COUNT(DISTINCT(w.WellID)) End As [2020],
    Case When cte.[YEAR] = Year(GetDate())-2 Then COUNT(DISTINCT(w.WellID)) End As [2019],
    Case When cte.[YEAR] = Year(GetDate())-3 Then COUNT(DISTINCT(w.WellID)) End As [2018],
    Case When cte.[YEAR] = Year(GetDate())-4 Then COUNT(DISTINCT(w.WellID)) End As [2017]
From Well w
    Inner Join Construct c ON c.WellKey = w.PKey
    Inner Join ConstructDate cd ON c.PKey = cd.ConstructKey
    Inner Join cte On (YEAR(cd.EventDate)=cte.[YEAR])
Where
    And cd.Event = 'LATERALSTATUS'
    And cd.Comment = 'PA'
Group by Grouping Sets ((cte.[YEAR], w.WellType), (cte.[YEAR]))) As T
Group by WellTypes
Order by
    Case WellTypes
        When 'OW' Then 1
        When 'GW' Then 2
        When 'D' Then 3
        When 'OWI' Then 4
        When 'WI' Then 5
    End Desc

標籤：sql sql-server tsql count sql-server-2012

上一篇：加入沒有主鍵的表，外鍵加入表時也會回傳空值

下一篇：如何在SSIS中拆分一行資料并將每一行（但不同的列）插入到兩個表（具有FK關系）中？