是这样，我有张表，字段是这样的
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，实在有点搞不懂。。。
------解决思路----------------------
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
------解决思路----------------------
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 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 行受影响)