新手请问：如何统计组合出现的次数？

[bh586]

有如下表格：
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算同一种情况。

[子陌红尘]

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 end

[子陌红尘]

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
*/

[hycheng163]

子陌红尘老兄，强阿，佩服

[xeqtr1982]

学习一下:)

[bugchen888]

佩服佩服。。。。。

[zjcxc]

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, n2

[bh586]

请问：如果再增加一列N3。N1，N2，N3无规则变化，要求统计N1/N2/N3组合的出现次数。

如 0.11/0.55/7.5 7.5/0.55/0.11算同一种情况

[子陌红尘]

难怪眼熟：

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)