這就是資料的樣子。這是一張長桌
我需要計算每天雇傭的人數
如何撰寫 SQL Server 邏輯來得到這個結果？我嘗試創建一個 DATES 表然后加入，但這導致錯誤，因為表太大。我需要遞回邏輯嗎？
uj5u.com熱心網友回復：
對于未來的問題，不要發布資料影像。相反，使用像dbfiddle這樣的服務。無論如何，我都會為答案添加草圖，如果問題準備得更好，您可能會得到完整的答案。無論如何，它是這樣的：
-- extrema is the least and the greatest date in staff table
with extrema(mn, mx) as (
select least(min(hired),min(retired)) as mn
, greatest(max(hired),max(retired)) as mx
from staff
), calendar (dt) as (
-- we construct a calendar with every date between extreme values
select mn from extrema
union all
select dateadd(day, 1, d)
from calendar
where dt < (select mx from extrema)
)
-- finally we can count the number of employed people for each such date
select dt, count(1)
from calendar c
join staff s
on c.dt between s.hired and s.retired
group by dt;
如果您發現自己經常進行此類計算，那么創建日歷表是個好主意。您可以向它添加其他屬性，例如它是否是一周中間的一天等。
約束為：
CHECK(hired <= retired)
第一部分可以簡化為：
with extrema(mn, mx) as (
select min(hired) as mn
, max(retired) as mx
from staff
),
uj5u.com熱心網友回復：
假設當前員工的退休日期為 NULL
Declare @Date1 date = '2015-01-01'
Declare @Date2 date = getdate()
Select A.Date
,HeadCount = count(B.name)
From ( Select Top (DateDiff(DAY,@Date1,@Date2) 1)
Date=DateAdd(DAY,-1 Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1,master..spt_values n2
) A
Left Join YourTable B on A.Date >= B.Hired and A.Date <= coalesce(B.Retired,getdate())
Group BY A.Date
uj5u.com熱心網友回復：
為此，您需要一個日歷表。您從日歷開始，然后使用BETWEEN邏輯將其他所有內容 LEFT JOIN 。
您可以使用真正的桌子。或者您可以動態生成它，如下所示：
WITH
L0 AS ( SELECT c = 1
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT c = 1 FROM L0 A, L0 B, L0 C, L0 D ),
Nums AS ( SELECT rownum = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM L1 ),
Dates AS (
SELECT TOP (DATEDIFF(day, '20141231', GETDATE()))
Date = DATEADD(day, rownum, '20141231')
FROM Nums
)
SELECT
d.Date,
NumEmployed = COUNT(*)
FROM Dates d
JOIN YourTable t ON d.Date BETWEEN t.Hired AND t.Retired
GROUP BY
d.Date;
如果您的日期有時間組件，那么您需要使用>= AND <邏輯
uj5u.com熱心網友回復：
嘗試限制日期表的范圍。在這個例子中，我有一個名為 TallyStickDT 的日期表。
SELECT dt, COUNT(name)
FROM (
SELECT dt
FROM tallystickdt
WHERE dt >= (SELECT MIN(hired) FROM #employees)
AND dt <= GETDATE()
) A
LEFT OUTER JOIN #employees E ON A.dt >= E.Hired AND A.dt <= e.retired
GROUP BY dt
ORDER BY dt
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/364501.html
標籤：sql sql-server 查询语句