declare @t table(A varchar(2),B varchar(2),C varchar(2))
insert into @t select
'H1', '10', 'A1' union all select
'H2', '20', 'A2' union all select
'H3', '30', 'A3' union all select
'H3', '40', 'A4' union all select
'H3', '30', 'A5' union all select
'H4', '10', 'A3' union all select
'H4', '10', 'A6' union all select
'H5', '10', 'A7'
select a.*,b.D,c.E from
@t a,
(select A,B,count(1) D from @t group by A,B) b,
(select C,count(1) E from @t group by C) c
where a.A=b.A and a.B=b.B and a.C=c.C
order by b.D desc,c.E desc
--结果(所影响的行数为 8 行)A B C D E
---- ---- ---- ----------- -----------
H4 10 A3 2 2
H3 30 A3 2 2
H3 30 A5 2 1
H4 10 A6 2 1
H5 10 A7 1 1
H3 40 A4 1 1
H1 10 A1 1 1
H2 20 A2 1 1(所影响的行数为 8 行)
insert into @t select
'H1', '10', 'A1' union all select
'H2', '20', 'A2' union all select
'H3', '30', 'A3' union all select
'H3', '40', 'A4' union all select
'H3', '30', 'A5' union all select
'H4', '10', 'A3' union all select
'H4', '10', 'A6' union all select
'H5', '10', 'A7'
select a.*,b.D,c.E from
@t a,
(select A,B,count(1) D from @t group by A,B) b,
(select C,count(1) E from @t group by C) c
where a.A=b.A and a.B=b.B and a.C=c.C
order by b.D desc,c.E desc
--结果(所影响的行数为 8 行)A B C D E
---- ---- ---- ----------- -----------
H4 10 A3 2 2
H3 30 A3 2 2
H3 30 A5 2 1
H4 10 A6 2 1
H5 10 A7 1 1
H3 40 A4 1 1
H1 10 A1 1 1
H2 20 A2 1 1(所影响的行数为 8 行)
D = (select count(*) from table where A = a.A and B = a.B),
E = (select count(*) from table C = a.C)
from table
select *,
D = (select count(*) from table where A = a.A and B = a.B),
E = (select count(*) from table C = a.C)
from table a /*补上表别名*/
select a,b,c,(select count(*) from @t t1 where a=ls.a and b=ls.b) as d,(select count(*) from @t t1 where c=ls.c ) as e
from @t ls
order by d desc,e desc