27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE test1 (ID INT, FromID INT, ToID INT, IsNew VARCHAR(20))
INSERT INTO test1
SELECT 1,103, 102,'true' UNION ALL
SELECT 2,102, 103,'false' UNION ALL
SELECT 3,103, 102,'true' UNION ALL
SELECT 4,104, 102,'true' UNION ALL
SELECT 5,105, 102,'true'
SELECT
Min(ID) ID,
CASE WHEN FromID>=ToID THEN FromID ELSE ToID END FromID,
CASE WHEN FromID<ToID THEN FromID ELSE ToID END ToID,
COUNT(1) AllCount,
COUNT(CASE WHEN IsNew='true' THEN 1 ELSE NULL END) NewCount
FROM TEST1
GROUP BY
CASE WHEN FromID>=ToID THEN FromID ELSE ToID END,
CASE WHEN FromID<ToID THEN FromID ELSE ToID END
------------------
ID FromID ToID AllCount NewCount
----------- ----------- ----------- ----------- -----------
1 103 102 3 2
4 104 102 1 1
5 105 102 1 1