• 主页

# 这样一个场景要怎样查询呢

http://bbs.csdn.net/topics/390313485?page=1#post-393183709

Message表中有字段 ID FromID ToID

ID FromID ToID IsNew
1 103 102 true
2 102 103 false
3 103 102 true
4 104 102 true
5 105 102 true

from代表发送方，to代表接收方。

ID FromID ToID AllCount NewCount
1 103 102 3 2

4 104 102 1 1
5 105 102 1 1
...全文
123 点赞 收藏 5

5 条回复

snbxp 2012年12月12日
``````
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
``````

declare @message table(id int,fromid int,toid int,isnew int) insert into @message select 1 , 103 , 102,1 union all select 2 , 102 , 103,0 union all select 3 , 103 , 102,1 union all select 4 , 104 , 102,1 union all select 5 , 105 , 102,1 ; with tb as( select id,fromid,toid,isnew, case when fromid<toid then fromid else toid end as newfromid, case when fromid<toid then toid else fromid end as newtoid from @message ) select b.id,c.fromid,c.toid,b.allcount,b.newcount from ( select MIN(id) id,COUNT(1) allcount,SUM(isnew) as newcount from ( select id,fromid,toid,isnew,dense_rank() over(order by newfromid,newtoid) as rowid from tb) as a group by rowid) as b inner join tb as c on b.id=c.id

1.1w+

6.8w+

MS-SQL Server 应用实例