34,575
社区成员
发帖
与我相关
我的任务
分享
with temp1 as
(select 1 as id,1 as uid1,2 as uid2 ,'你好'message union all
select 2 as id,2 as uid1,1 as uid2 ,'你好啊'message union all
select 3 as id,1 as uid1,2 as uid2 ,'想认识你'message union all
select 4 as id,2 as uid1,1 as uid2 ,'你谁啊'message union all
select 5 as id,1 as uid1,3 as uid2 ,'你好'message union all
select 6 as id,4 as uid1,1 as uid2 ,'你好'message ),
temp2 as
(
select id
,case when uid1 = 1 then 1 when uid2 = 1 then 1 end uid1--将所有1参与的对话中 1的人员ID都处理到uid1
,case when uid1 = 1 then uid2 when uid2 = 1 then uid1 end uid2--将其他人的人员ID都处理到uid2,这样就很方便了
,message
from temp1
where (uid1=1 or uid2=1)
),
resulttable as
(
select MAX(id) id,uid1,uid2,COUNT(id) rowscount from temp2 group by uid1,uid2
)
select a.uid2,b.message,a.rowscount from resulttable a
left join temp1 b on a.id = b.id
uid2 message rowscount
----------- -------- -----------
2 你谁啊 4
3 你好 1
4 你好 1
(3 行受影响)
DECLARE @from INT
SET @from=1
;WITH a0 (id,[from],[to],[message]) AS
(
SELECT 1, 1, 2,'你好' UNION ALL
SELECT 2, 2, 1,'你好啊' UNION ALL
SELECT 3, 1, 2,'想认识你' UNION ALL
SELECT 4, 2, 1,'你谁啊。。' UNION ALL
SELECT 5, 1, 3,'你好' UNION ALL
SELECT 6, 4, 1,'你好'
)
,a1 AS
(
SELECT
CASE WHEN [from]=@from THEN [to] ELSE [from] END [to],
(SELECT MAX(id) FROM a0 b WHERE (b.[from]=a.[from] AND b.[to]=a.[to]) OR (b.[from]=a.[to] AND b.[to]=a.[from])) id
FROM a0 a
WHERE [from]=@from OR [to]=@from
)
,a2 AS
(
SELECT [to],MAX(id) id,COUNT(*) cnt
FROM a1
GROUP BY [to]
)
SELECT [to],(SELECT [message] FROM a0 WHERE id=a.id) [message],cnt
FROM a2 a
ORDER BY 1
WITH a0 (id,nFROM,nTO,mes) AS
(
SELECT 1, 1, 2,'你好' UNION ALL
SELECT 2, 2, 1,'你好啊' UNION ALL
SELECT 3, 1, 2,'想认识你' UNION ALL
SELECT 4, 2, 1,'你谁啊。。' UNION ALL
SELECT 5, 1, 3,'你好' UNION ALL
SELECT 6, 4, 1,'你好'
)
,a1 AS
(
SELECT
CASE WHEN nFROM=1 THEN nTO ELSE nFROM END nTO,
(SELECT MAX(id) FROM a0 b WHERE (b.nFROM=a.nFROM AND b.nTO=a.nTO) OR (b.nFROM=a.nTO AND b.nTO=a.nFROM)) id
FROM a0 a
WHERE nFROM=1 OR nTO=1
)
,a2 AS
(
SELECT nTO,MAX(id) id,COUNT(*) cnt
FROM a1
GROUP BY nTO
)
SELECT nTO,(SELECT mes FROM a0 WHERE id=a.id) mes,cnt
FROM a2 a
ORDER BY 1