我有這個例子，其中 Id1 作為組，Id2 是組中唯一的 Id。
如果Id2和Id1相同，我想得到它的行作為整個組的代表。
如果沒有與 Id2 匹配的 Id1，那么我想根據 Id2 的升序獲取第一行。
我就是這樣做的，但我只是想知道如何簡化 tsql：
WITH cte AS
(
SELECT 'A' AS Id1, '1' AS Id2, 'DSFSF' AS _detail
UNION ALL
SELECT 'A' AS Id1, '2' AS Id2, 'ASDF' AS _detail
UNION ALL
SELECT 'A' AS Id1, 'A' AS Id2, '434242' AS _detail
UNION ALL
SELECT 'B' AS Id1, '1' AS Id2, 'gsreew' AS _detail
UNION ALL
SELECT 'B' AS Id1, '2' AS Id2, 'werw' AS _detail
UNION ALL
SELECT 'B' AS Id1, '3' AS Id2, '67575' AS _detail
),
cte2 AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Id1 ORDER BY Id2) AS rn,
CASE
WHEN Id1 = Id2 THEN 1 ELSE 0
END AS _matchedid
FROM
cte
),
cte3 AS
(
SELECT
Id1, SUM(_matchedid) AS _matched
FROM
cte2
GROUP BY
Id1
)
SELECT *
FROM cte2 a
INNER JOIN cte3 b ON a.Id1 = b.Id1
WHERE b._matched = 1 AND a.Id1 = a.Id2
UNION ALL
SELECT *
FROM cte2 a
INNER JOIN cte3 b ON a.Id1 = b.Id1
WHERE b._matched = 0 AND a.rn = 1
uj5u.com熱心網友回復：
您可以為此使用條件行編號解決方案
WITH cte AS
(
SELECT 'A' AS Id1, '1' AS Id2, 'DSFSF' AS _detail
UNION ALL
SELECT 'A' AS Id1, '2' AS Id2, 'ASDF' AS _detail
UNION ALL
SELECT 'A' AS Id1, 'A' AS Id2, '434242' AS _detail
UNION ALL
SELECT 'B' AS Id1, '1' AS Id2, 'gsreew' AS _detail
UNION ALL
SELECT 'B' AS Id1, '2' AS Id2, 'werw' AS _detail
UNION ALL
SELECT 'B' AS Id1, '3' AS Id2, '67575' AS _detail
),
cteWithRn AS
(
SELECT *
ROW_NUMBER() OVER (PARTITION BY Id1, CASE WHEN Id1 = Id2 THEN 0 ELSE 1 END, Id2) AS rn
FROM cte
)
SELECT *
FROM cteWithRn
WHERE rn = 1;
uj5u.com熱心網友回復：
一個想法使用TOP (1) WITH TIES和ROW_NUMBER條件ORDER BY：
SELECT TOP (1) WITH TIES *
FROM CTE
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY CASE ID1 WHEN ID2 THEN NULL ELSE ID2 END);
請注意你的列ID2是一個varchar，然后像一個值'10'將具有更低的比類似值'2'。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/372102.html
標籤：sql-server 查询语句