新手请问：如何统计组合出现的次数？
有如下表格：
id n1 n2 TIME
001 3 4 ...
001 3 5 ...
002 4 2.4 ...
003 5 5.5 ...
003 6 5.5 ...
003 5 5.5 ...
004 5 3 ...
005 2.1 4.4 ...
... ... ...
... ... ...
N1和N2两列无规则变化，要求统计N1和N2组合的出现次数。
其中，ID：004 和(第二条)ID 001算同一种情况。
问题点数：100、回复次数：8Top
1 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-02-26 20:13:44 得分 50
select
case when n1>n2 then n2 else n1 end as n1,
case when n1>n2 then n1 else n2 end as n2,
count(*)
from
表
group by
case when n1>n2 then n2 else n1 end,
case when n1>n2 then n1 else n2 endTop
2 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-02-26 20:16:51 得分 0
declare @t table(id varchar(10),n1 numeric(2,1),n2 numeric(2,1))
insert into @t select '001',3 ,4
insert into @t select '001',3 ,5
insert into @t select '002',4 ,2.4
insert into @t select '003',5 ,5.5
insert into @t select '003',6 ,5.5
insert into @t select '003',5 ,5.5
insert into @t select '004',5 ,3
insert into @t select '005',2.1 ,4.4
select
case when n1>n2 then n2 else n1 end as n1,
case when n1>n2 then n1 else n2 end as n2,
count(*) as num
from
@t
group by
case when n1>n2 then n2 else n1 end,
case when n1>n2 then n1 else n2 end
/*
n1 n2 num
---- ---- -----------
2.4 4.0 1
3.0 4.0 1
2.1 4.4 1
3.0 5.0 2
5.0 5.5 2
5.5 6.0 1
*/Top
3 楼hycheng163（）回复于 2006-02-26 21:54:40 得分 0
子陌红尘老兄，强阿，佩服Top
4 楼xeqtr1982（Visual C# .NET）回复于 2006-02-27 09:31:51 得分 0
学习一下:)Top
5 楼bugchen888（臭虫）回复于 2006-02-27 09:37:15 得分 0
佩服佩服。。。。。Top
6 楼zjcxc（邹建）回复于 2006-02-27 10:57:08 得分 50
select n1,n2, cnt=count(*)
from (
select n1, n2 from tb where n1>=n2
union all
select n2, n1 from tb where n2>n1
)a
group by n1, n2Top
7 楼bh586（）回复于 2006-03-01 20:24:17 得分 0
请问：如果再增加一列N3。N1，N2，N3无规则变化，要求统计N1/N2/N3组合的出现次数。
如 0.11/0.55/7.5 7.5/0.55/0.11算同一种情况
Top
8 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2006-03-06 10:29:45 得分 0
难怪眼熟：
declare @t table(id int,n1 numeric(5,2),n2 numeric(5,2),n3 numeric(5,2))
insert into @t select '001',3 ,4 ,0.12
insert into @t select '001',3 ,5 ,0.5
insert into @t select '002',4 ,2.4,4
insert into @t select '003',5 ,5.5,1.2
insert into @t select '003',6 ,5.5,18
insert into @t select '003',5 ,5.5,1.2
insert into @t select '004',0.5,5 ,3
insert into @t select '005',2.1,4.4,27
select
(case when n1>=n2 and n1>=n3 then n1 when n2>=n1 and n2>=n3 then n2 else n3 end) as n1,
(case when (n1>=n2 and n1<=n3) or (n1<=n2 and n1>=n3) then n1
when (n2>=n1 and n2<=n3) or (n2<=n1 and n2>=n3) then n2
else n3 end) as n2,
(case when n1<=n2 and n1<=n3 then n1 when n2<=n1 and n2<=n3 then n2 else n3 end) as n3,
count(*) as num
from
@t
group by
(case when n1>=n2 and n1>=n3 then n1 when n2>=n1 and n2>=n3 then n2 else n3 end),
(case when (n1>=n2 and n1<=n3) or (n1<=n2 and n1>=n3) then n1
when (n2>=n1 and n2<=n3) or (n2<=n1 and n2>=n3) then n2
else n3 end),
(case when n1<=n2 and n1<=n3 then n1 when n2<=n1 and n2<=n3 then n2 else n3 end)Top
