50分：涉及两字段的统计问题
该表模拟两人通话记录，表结构如下：
from       to
a               b
b               a
c               d
d               c
a               b
a               b
b               a
d               c
。。。

现要统计两两联系次数。
如该表统计后应为：
m1       m2       times
a         b             5
c         d             3

各位大虾赐教。


------解决方案--------------------
create table T([from] varchar(10), [to] varchar(10))
insert into T
select 'a ', 'b ' union all
select 'b ', 'a ' union all
select 'c ', 'd ' union all
select 'd ', 'c ' union all
select 'a ', 'b ' union all
select 'a ', 'b ' union all
select 'b ', 'a ' union all
select 'd ', 'c '

select m1,m2,count(*) as times
from
(select m1=case when [from] <[to] then [from] else [to] end,
m2=case when [from] <[to] then [to] else [from] end
from T) A
group by m1,m2

/*
m1 m2 times
---------- ---------- -----------
a b 5
c d 3

*/

drop table t
------解决方案--------------------
select from ,to ,count(*) from
(
select from,to from tb
union all
select to from,from to from tb
) t
where from < to
group by from ,to

------解决方案--------------------
create table t(f1 varchar(10), t1 varchar(10))
insert t

select 'a ', 'b ' union all
select 'b ', 'a ' union all
select 'c ', 'd ' union all
select 'd ', 'c ' union all
select 'a ', 'b ' union all
select 'a ', 'b ' union all
select 'b ', 'a ' union all
select 'd ', 'c '

select min(f1),max(t1) ,count(c)
from (select c=ASCII (f1) +ascii(t1),f1,t1 from t) d
group by c