我有一個文章日志表。我需要獲取所有只有一個日志的文章，或者如果日志數量超過 1：如果文章有任何日志狀態 = 103，則只需要獲取此日志之后的行，其他情況下所有日志。因此，從以下資料集中，我只想獲取 ID 為 1383 和 284653 的行。
| ID | 文章 | 版本 | 狀態 ID | 添加日期 |
|---|---|---|---|---|
| 1383 | 1481703 | 0 | 42 | 2011-11-25 09:23:42.000 |
| 284645 | 435545 | 1 | 41 | 2021-11-02 18:29:42.000 |
| 284650 | 435545 | 2 | 41 | 2021-11-02 18:34:58.000 |
| 284651 | 435545 | 2 | 103 | 2021-11-02 18:34:58.000 |
| 284653 | 435545 | 3 | 41 | 2021-11-02 18:38:33.000 |
任何想法如何正確處理它？提前致謝
uj5u.com熱心網友回復：
您可以在此處使用視窗函式。正在運行的組合COUNT和一個視窗COUNT會做的伎倆
使用視窗函式而不是自聯接的好處是您只需掃描一次基表。
SELECT
Id,
Article,
Version,
StatusId,
AddedDate
FROM (
SELECT *,
HasPrev103 = COUNT(CASE WHEN StatusId = 103 THEN 1 END) OVER
(PARTITION BY Article ORDER BY AddedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
Has103 = COUNT(CASE WHEN StatusId = 103 THEN 1 END) OVER (PARTITION BY Article),
Count = COUNT(*) OVER (PARTITION BY Article)
FROM YourTable t
) t
WHERE (Has103 > 0 AND HasPrev103 > 0) OR Count = 1;
資料庫<>小提琴
uj5u.com熱心網友回復：
CREATE TABLE #Article (
Id int NOT NULL PRIMARY KEY,
Article int NOT NULL,
Version int NOT NULL,
StatusId int NOT NULL,
DateAdded datetime NOT NULL
)
INSERT INTO #Article (Id, Article, Version, StatusId, DateAdded)
VALUES
(1383, 1481703, 0, 42, '2011-11-25 09:23:42.000'),
(284645, 435545, 1, 41 , '2021-11-02 18:29:42.000'),
(284650, 435545, 2, 41 , '2021-11-02 18:34:58.000'),
(284651, 435545, 2, 103, '2021-11-02 18:34:58.000'),
(284653, 435545, 3, 41 , '2021-11-02 18:38:33.000')
SELECT *
FROM #Article a
LEFT JOIN (
-- Get articles that appear only once.
SELECT Article
FROM #Article
GROUP BY Article
HAVING COUNT(*) = 1
) AS o
ON a.Article = o.Article
LEFT JOIN (
-- Get the 103s and their corresponding date.
SELECT Article, DateAdded
FROM #Article
WHERE StatusId = 103
) AS s
ON a.Article = s.Article AND s.DateAdded < a.DateAdded
WHERE o.Article IS NOT NULL OR (s.Article IS NOT NULL AND a.DateAdded > s.DateAdded)
DROP TABLE #Article
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/347866.html
標籤：sql-server 查询语句
上一篇：為什么我無法使用NodeJS連接到SQLServer？
下一篇：SQL組合列