如果您為我提供 Oracle 或 SQL Server 或 MySQL 或 PostgreSQL 的解決方案，它不會計量。
我所需要的只是一種不同的方法，另一種思維方式。謝謝 每個專案的開始日期和結束日期以及天數 - 每個專案持續了多長時間。專案不重疊。我試圖使用滯后和領先來解決它，但我沒有成功。通常我會尋找幾個解決問題的方法，但不幸的是我在谷歌上只找到了一個解決這個問題的方法。如果這是我學習的唯一方法，我對其他方法感興趣。這是我在谷歌上找到的解決方案
WITH STARTDATES AS (
SELECT startdate
FROM project
WHERE startdate NOT IN (SELECT enddate FROM project) ),
-- get end dates not present in start date column (these are “true” project end dates)
ENDDATES AS (
SELECT enddate
FROM project
WHERE enddate NOT IN (SELECT startdate FROM project) ),
-- filter to plausible start-end pairs (start < end), then find correct end date for each start date (the minimum end date, since there are no overlapping projects)
t3 AS (
SELECT startdate, min(enddate) AS enddate
FROM STARTDATES, ENDDATES
WHERE startdate < enddate
GROUP BY startdate )
SELECT startdate, enddate, enddate - startdate AS project_duration
FROM t3
ORDER BY 3,1;
先感謝您
uj5u.com熱心網友回復：
您可以嘗試使用LAG視窗函式獲取下一行STARTDATE然后比較減法ENDDATE
然后使用條件聚合函式grp進行分組。
SELECT MIN(STARTDATE) STARTDATE,
MAX(ENDDATE) ENDDATE,
COUNT(*) DURATION
FROM (
SELECT t1.*,SUM(CASE WHEN t1.daydiff = 0 THEN 0 ELSE 1 END) OVER(ORDER BY STARTDATE) grp
FROM (
SELECT T.*,LAG(ENDDATE) OVER(ORDER BY STARTDATE) - STARTDATE daydiff
FROM T
) t1
) t1
GROUP BY grp;
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432919.html