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
