如表MSN中,字段FromUsername是发送方的账号,字段ToUsername是接收方的账号。
我想得到是该表中FromUsername和ToUsername的交互次数,即聊天次数,该如何?
(注:FromUsername和ToUsername可能为空,一般情况下FromUsername和ToUsername是一一对应的,
如果有为空的这里暂时不考虑)。
如:FromUsername为[email protected],ToUsername为[email protected]。
[email protected]对[email protected]发送了10条,[email protected]回复
了8条,则他们之间的交互记录是18。或者谁能针对一张这样的表提出一些比较有意义的统计分析结果也行,给出自已的查询语句。另:下面的这条语句谁能帮忙分析一下?我查出的结果和我想像中的不一样。
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM MSN AS A1 INNER JOIN
MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername
GROUP BY A1.FromUsername, A1.ToUsername
我想得到是该表中FromUsername和ToUsername的交互次数,即聊天次数,该如何?
(注:FromUsername和ToUsername可能为空,一般情况下FromUsername和ToUsername是一一对应的,
如果有为空的这里暂时不考虑)。
如:FromUsername为[email protected],ToUsername为[email protected]。
[email protected]对[email protected]发送了10条,[email protected]回复
了8条,则他们之间的交互记录是18。或者谁能针对一张这样的表提出一些比较有意义的统计分析结果也行,给出自已的查询语句。另:下面的这条语句谁能帮忙分析一下?我查出的结果和我想像中的不一样。
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM MSN AS A1 INNER JOIN
MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername
GROUP BY A1.FromUsername, A1.ToUsername
FROM (
SELECT
FromUserName,
ToUserName
FROM MSN
UNION ALL
SELECT
ToUserName,
FromUserName
FROM MSN
) AS A
GROUP BY FromUserName,ToUserName
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM MSN A1
GROUP BY A1.FromUsername, A1.ToUsername
(
@FromUsername varchar(50),
@ToUsername varchar(50)
)
as
SELECT COUNT(*) AS counts
FROM MSN where FromUsername = @FromUsername AND ToUsername = @ToUsername
FROM MSN
GROUP BY FromUsername, ToUsername
(
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select 'aa','bb'
union all select 'aa','cc'
union all select 'tt','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'gg','ee'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'bb','dd'
union all select 'aa','cc'
union all select 'tt','bb'SELECT COUNT(*) AS counts
FROM #MSN where FromUsername = N'aa' AND ToUsername = N'cc'counts
-----------
7(1 行受影响)
create table msn(FromUsername varchar(30),ToUsername varchar(30))
go
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
insert msn select '[email protected]','[email protected]'
go
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM MSN AS A1 inner JOIN
MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername
GROUP BY A1.FromUsername, A1.ToUsername
/*
FromUsername ToUsername counts
------------------------------ ------------------------------ -----------
[email protected] [email protected] 80
[email protected] [email protected] 80(所影响的行数为 2 行)*/
( id int identity(1,1),
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
;with t
as
(
select id, Min_abcd=min([value]) ,Max_abcd=max([value])
from #msn
unpivot([value] for [abcd] in ([FromUsername], [ToUsername])) as U
group by ID
)
select Min_abcd+'---'+Max_abcd,count(1)as cnt
from t
group by Min_abcd+'---'+Max_abcddrop table #msn/*
cnt
------------------------------------------------------ -----------
[email protected]@hotmail.com 8
[email protected]@hotmail.com 1
[email protected]@hotmail.com 1
*/
[email protected] [email protected] 160这种数据吧,呵,,,
( id int identity(1,1),
FromUsername varchar(50),
ToUsername varchar(50)
)
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]'
insert #msn select '[email protected]','[email protected]';with t
as
(
select id, Min_abcd=min([value]) ,Max_abcd=max([value])
from #msn
unpivot([value] for [abcd] in ([FromUsername], [ToUsername])) as U
group by ID
)
select Min_abcd+'---'+Max_abcd,count(1)as cnt
from t
group by Min_abcd+'---'+Max_abcddrop table #msn/*
cnt
--------- -----------
[email protected]@hotmail.com 18
*/
[email protected]@hotmail.com 这只是显示的格式,表示两个人通讯过呀,上面的不是你想要的结果 ?
( id int identity(1,1),
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
union all select '[email protected]','[email protected]'
;with t
as
(
select id, Min_abcd=min([value]) ,Max_abcd=max([value])
from #msn
unpivot([value] for [abcd] in ([FromUsername], [ToUsername])) as U
group by ID
)
select Min_abcd+'---'+Max_abcd,count(1)as cnt
from t
group by Min_abcd+'---'+Max_abcddrop table #msn/*
------------------------------------------------------ -----------
[email protected]@hotmail.com 7
[email protected]@hotmail.com 1
[email protected]@hotmail.com 2
[email protected]@hotmail.com 1
[email protected]@hotmail.com 1
[email protected]@hotmail.com 1
[email protected]@hotmail.com 83
[email protected]@hotmail.com 11
[email protected]@hotmail.com 11
*/
(
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select 'aa','bb'
union all select 'aa','cc'
union all select 'tt','cc'
union all select 'tt','cc'
union all select 'tt','cc'
union all select 'tt','cc'
union all select 'tt','cc'
union all select 'tt','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'gg','ee'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'bb','dd'
union all select 'aa','cc'
union all select 'tt','bb'
union all select 'cc','bb'
union all select 'cc','bb'
union all select 'cc','aa'
union all select 'cc','aa'
union all select 'cc','aa'
union all select 'cc','aa'
union all select 'cc','aa'
union all select 'cc','bb';with temp as
(
SELECT FromUsername, ToUsername, COUNT(*) AS counts
FROM #MSN AS A1 group by FromUsername,ToUsername
)select distinct a.FromUsername Username1,a.ToUsername Username2,isnull(a.counts,0)+isnull(b.counts,0) as counts
from temp a left join temp b on a.FromUsername=b.ToUsername and a.ToUsername=b.FromUsername
drop table #MSN
(
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select 'aa','bb'
union all select 'aa','cc'
union all select 'tt','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'gg','ee'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'bb','dd'
union all select 'aa','cc'
union all select 'tt','bb'
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM #MSN AS A1 full JOIN
#MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername
GROUP BY A1.FromUsername, A1.ToUsername
FROM MSN AS A1 full JOIN
MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername
GROUP BY A1.FromUsername, A1.ToUsername
(
FromUsername varchar(50),
ToUsername varchar(50)
)
insert into #MSN select 'aa','bb'
union all select 'aa','cc'
union all select 'tt','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'gg','ee'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'aa','cc'
union all select 'bb','dd'
union all select 'aa','cc'
union all select 'tt','bb'
SELECT A1.FromUsername, A1.ToUsername, COUNT(*) AS counts
FROM #MSN AS A1 full JOIN
#MSN AS A2 ON A1.FromUsername = A2.ToUsername AND A1.ToUsername = A2.FromUsername where A1.FromUsername is not null and A1.ToUsername is not null
GROUP BY A1.FromUsername, A1.ToUsername