求SQL语句
表A 有字段B/C
B C
1 22
1 22
1 22
1 23
2 33
2 33
3 44
3 45
---------
要根据字段B查出对应C相同超过2行的记录,例如 B为1时,C相同的有3行,B为2,C相同的有2行:
1 3
2 2谢谢
表A 有字段B/C
B C
1 22
1 22
1 22
1 23
2 33
2 33
3 44
3 45
---------
要根据字段B查出对应C相同超过2行的记录,例如 B为1时,C相同的有3行,B为2,C相同的有2行:
1 3
2 2谢谢
--> 测试数据: @s
declare @s table (B int,C int)
insert into @s
select 1,22 union all
select 1,22 union all
select 1,22 union all
select 1,23 union all
select 2,33 union all
select 2,33 union all
select 3,44 union all
select 3,45select b,数量=count(1) from @s group by b,c having count(1)>=2
求SQL语句
表A 有字段B/C
B C
1 22
1 22
1 22
1 23
1 23
2 33
2 33
3 44
3 45
---------
要根据字段B查出对应C相同超过2行的记录,例如 B为1时,C相同的有3行,B为2,C相同的有2行:
1 22 3
1 23 2
2 33 2 要有这样的结果, 谢谢
--> 测试数据: @s
declare @s table (B int,C int)
insert into @s
select 1,22 union all
select 1,22 union all
select 1,22 union all
select 1,23 union all
select 1,23 union all
select 2,33 union all
select 2,33 union all
select 3,44 union all
select 3,45select b,c,数量=count(1) from @s group by b,c having count(1)>=2
create table aaa (aa int, bb int )insert into aaa
select 1 , 22 union all
select 1 , 22 union all
select 1 , 22 union all
select 1, 23 union all
select 2 , 33 union all
select 2 , 33 union all
select 3 , 44 union all
select 3 , 45
select
select aa,次数 from ( select aa,bb,count(*)as 次数 from aaa group by aa,bb having count(*)>1