关于对话的一张表,如何选出某人和所有人的最后一次对话?

wentihenduo 2014-09-19 05:57:44
是这样,我有张表,字段是这样的
id, from, to, message
其中id是对话的id,默认int自增加1
from和to是用户的id,也是数字。
message是对话内容。

比如用户1和2之间的对话,from和to可以互换,因为可能是1给2发送信息,也可能是2给1发送信息。

比如表里面的内容是这样:
1 1 2 你好
2 2 1 你好啊
3 1 2 想认识你
4 2 1 你谁啊。。
5 1 3 你好
6 4 1 你好

这里有4个用户,我想选出用户1的所有对话,包含最后一次对话的内容,还有和每个用户分别对话的总数
结果应该是
2 你谁啊 4 (总共有4次对话,最后一次是“你谁啊”)
3 你好 1(总共只有1次对话,1发给3的)
4 你好 1 (总共只有1次对话,4发给1的)

请问这样的表,应该怎么写sql语句啊。。。 对于这样的group by,实在有点搞不懂。。。
...全文
152 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
jayzhihui 2014-09-21
  • 打赏
  • 举报
回复
lzw_0736 0736常德区号,老乡~~
frankl123 2014-09-21
  • 打赏
  • 举报
回复

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 行受影响)
wentihenduo 2014-09-21
  • 打赏
  • 举报
回复
引用 2 楼 lzw_0736 的回复:

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
谢谢您的回复! 我在这里也问了同样的问题:http://bbs.csdn.net/topics/390890007,其中有个方法是使用了over函数加row_number的方法,效率似乎更高,我最终会使用他的方法。您有兴趣也可以看看。 谢谢!
lzw_0736 2014-09-20
  • 打赏
  • 举报
回复

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
lzw_0736 2014-09-20
  • 打赏
  • 举报
回复

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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧