现有表asd
表中结构如下create table asd
(aa int,bb int)insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1)现在要显示aa共有行数为一个指定数字的aa列,bb列和总和数
如:要显示aa共有3行则显示
a b 共有
1 1 3
1 2 3
1 3 3
3 1 3
3 2 3
3 3 3
如:要显示aa共有2行则显示
a b 共有
4 1 2
4 2 2查询语句该如何写在sybase中写成如下语句有正确结果,但是在SQL Servr中会要你bb必须包含在group中,但将bb写入group中后查询结果则完全不正确,去掉bb列可以获得正确的执行结果但不符合需求
select aa,bb,count(aa) from asd
group by aa having count(aa)=3在
表中结构如下create table asd
(aa int,bb int)insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1)现在要显示aa共有行数为一个指定数字的aa列,bb列和总和数
如:要显示aa共有3行则显示
a b 共有
1 1 3
1 2 3
1 3 3
3 1 3
3 2 3
3 3 3
如:要显示aa共有2行则显示
a b 共有
4 1 2
4 2 2查询语句该如何写在sybase中写成如下语句有正确结果,但是在SQL Servr中会要你bb必须包含在group中,但将bb写入group中后查询结果则完全不正确,去掉bb列可以获得正确的执行结果但不符合需求
select aa,bb,count(aa) from asd
group by aa having count(aa)=3在
insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) select * from asd where aa in (select aa from asd group by aa having count(*) = 3)
/*
aa bb
----------- -----------
1 1
1 2
1 3
3 1
3 2
3 3(所影响的行数为 6 行)
*/select * from asd where aa in (select aa from asd group by aa having count(*) = 2)
/*
aa bb
----------- -----------
4 1
4 2(所影响的行数为 2 行)
*/drop table asd
insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) declare @aa as intset @aa = 3
select * from asd where aa in (select aa from asd group by aa having count(*) = @aa)
/*
aa bb
----------- -----------
1 1
1 2
1 3
3 1
3 2
3 3(所影响的行数为 6 行)
*/set @aa = 2
select * from asd where aa in (select aa from asd group by aa having count(*) = @aa)
/*
aa bb
----------- -----------
4 1
4 2(所影响的行数为 2 行)
*/drop table asd
--使用变量完成
create table asd (aa int,bb int)
insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) declare @aa as intset @aa = 3
select * , 共有 = @aa from asd where aa in (select aa from asd group by aa having count(*) = @aa)
/*
aa bb 共有
----------- ----------- -----------
1 1 3
1 2 3
1 3 3
3 1 3
3 2 3
3 3 3(所影响的行数为 6 行)
*/set @aa = 2
select * , 共有 = @aa from asd where aa in (select aa from asd group by aa having count(*) = @aa)
/*
aa bb 共有
----------- ----------- -----------
4 1 2
4 2 2(所影响的行数为 2 行)
*/drop table asd
create table asd (aa int,bb int)
insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) select * , 共有 = 3 from asd where aa in (select aa from asd group by aa having count(*) = 3)
/*
aa bb 共有
----------- ----------- -----------
1 1 3
1 2 3
1 3 3
3 1 3
3 2 3
3 3 3(所影响的行数为 6 行)
*/select * , 共有 = 2 from asd where aa in (select aa from asd group by aa having count(*) = 2)
/*
aa bb 共有
----------- ----------- -----------
4 1 2
4 2 2(所影响的行数为 2 行)
*/drop table asd
(aa int,bb int) insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) go
select a.*,b.cnt
from asd a
right join (
select aa,count(aa) as cnt from asd
group by aa
having count(aa)=3 ) b
on a.aa = b.aadrop table asd/8aa bb cnt
----------- ----------- -----------
1 1 3
1 2 3
1 3 3
3 1 3
3 2 3
3 3 3(所影响的行数为 6 行)
*/
(aa int,bb int) insert asd values(1,1)
insert asd values(1,2)
insert asd values(1,3)
insert asd values(2,1)
insert asd values(3,1)
insert asd values(3,2)
insert asd values(3,3)
insert asd values(4,1)
insert asd values(4,2)
insert asd values(5,1) go
select a.*,b.cnt
from asd a
right join (
select aa,count(aa) as cnt from asd
group by aa
having count(aa)=2 ) b
on a.aa = b.aadrop table asd/*aa bb cnt
----------- ----------- -----------
4 1 2
4 2 2(所影响的行数为 2 行)*/