大家好,我有一个表A:ID SCHOOL AMOUNT FIELD AGE
1 0001 20 BOYAGE5 5
2 0001 30 BOYAGE6 6
3 0001 40 BOYAGE7 7
4 0002 50 BOYAGE8 8
5 0002 70 GIRLAGE5 5
6 0003 80 GIRLAGE6 6
7 0003 90 GIRLAGE7 7
8 0003 60 GIRLAGE8 8
求一查询语句 通过“AMOUNT”,“FIELD”, “AGE” 任意组合 可以过滤出SCHOOL。例如:想要查询一下 是否 有这样的SCHOOL存在, 这个SCHOOL包含有AGE从5到7岁的BOY共20人以上,同时所含的8岁的GIRL少于30人。
这样的语句怎么写??
1 0001 20 BOYAGE5 5
2 0001 30 BOYAGE6 6
3 0001 40 BOYAGE7 7
4 0002 50 BOYAGE8 8
5 0002 70 GIRLAGE5 5
6 0003 80 GIRLAGE6 6
7 0003 90 GIRLAGE7 7
8 0003 60 GIRLAGE8 8
求一查询语句 通过“AMOUNT”,“FIELD”, “AGE” 任意组合 可以过滤出SCHOOL。例如:想要查询一下 是否 有这样的SCHOOL存在, 这个SCHOOL包含有AGE从5到7岁的BOY共20人以上,同时所含的8岁的GIRL少于30人。
这样的语句怎么写??
create table tb (
id int,
school char(4),
amount int,
field varchar(10),
age int
)insert into tb
select 1,'0001',20,'BOYAGE5', 5 union all
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select *
from tb
/*
id school amount field age
----------- ------ ----------- ---------- -----------
1 0001 20 BOYAGE5 5
2 0001 30 BOYAGE6 6
3 0001 40 BOYAGE7 7
4 0002 50 BOYAGE8 8
5 0002 70 GIRLAGE5 5
6 0003 80 GIRLAGE6 6
7 0003 90 GIRLAGE7 7
8 0003 60 GIRLAGE8 8 (所影响的行数为 8 行)
*/
select *
from tb
where (age between 5 and 7) and amount >=20 and left(field,3)='boy'
and school in (
select school
from tb
where age=8 and amount<30 and left(field,4)='gril'
)
/*
id school amount field age
----------- ------ ----------- ---------- ----------- (所影响的行数为 0 行)
*/
--------------------------------
确认是这个条件吗?我的代码是按这个条件搜的,没有记录。
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select distinct school from tb
where (select isnull(sum(amount),0) from tb t where school=tb.school and left(field, 1)='b' and age between 5 and 7 )>=20
and (select isnull(sum(amount),0) from tb t where school=tb.school and left(field, 1)='g' and age>=8 )<30drop table tb/*
school
------
0001(1 row(s) affected)
*/
create table tb(id int, school char(4), amount int, field varchar(10), age int)insert into tb select 1,'0001',20,'BOYAGE5', 5 union all
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select m.school from
(select school from tb where age between 5 and 7 and field like 'boy%' group by school having(sum(amount)) >= 20) m,
(select school from tb where age = 8 and field like 'girl%' group by school having(sum(amount)) < 30) n
where m.school = n.school/*
(0 行受影响)
*/drop table tb
id int,
school char(4),
amount int,
field varchar(10),
age int
)insert into tb
select 1,'0001',20,'BOYAGE5', 5 union all
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select *
from tb
/*
id school amount field age
----------- ------ ----------- ---------- -----------
1 0001 20 BOYAGE5 5
2 0001 30 BOYAGE6 6
3 0001 40 BOYAGE7 7
4 0002 50 BOYAGE8 8
5 0002 70 GIRLAGE5 5
6 0003 80 GIRLAGE6 6
7 0003 90 GIRLAGE7 7
8 0003 60 GIRLAGE8 8 (所影响的行数为 8 行)
*/
select distinct school
from tb
where (age between 5 and 7) and amount >=20 and left(field,3)='boy'
and school not in (
select school
from tb
where age=8 and amount>=30 and left(field,4)='gril'
)
/*
school
------
0001(所影响的行数为 1 行)
*/
create table tb(id int, school char(4), amount int, field varchar(10), age int)insert into tb select 1,'0001',20,'BOYAGE5', 5 union all
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select T1.school
from (select school from tb where left(field, 1)='b' and age between 5 and 7 group by school having isnull(sum(amount),0)>=20) T1
left join (select school from tb where left(field, 1)='g' and age>=8 group by school having isnull(sum(amount),0)>=30) T2
on T1.school=T2.school
where T2.school is nulldrop table tb/*
school
------
0001(1 row(s) affected)
*/
from tb
where (age between 5 and 7) and amount >=20 and left(field,3)='boy'
and school in (
select school
from tb
where age=8 and amount<30 and left(field,4)='gril'
)
/*
id school amount field age
----------- ------ ----------- ---------- ----------- (所影响的行数为 0 行)
*/有的班没有女孩(天啊,太惨了)
drop table tb
go
create table tb(id int, school char(4), amount int, field varchar(10), age int)insert into tb select 1,'0001',20,'BOYAGE5', 5 union all
select 2,'0001',30,'BOYAGE6' , 6 union all
select 3,'0001',40,'BOYAGE7' , 7 union all
select 4,'0002',50 ,'BOYAGE8' , 8 union all
select 5,'0002',70 ,'GIRLAGE5' , 5 union all
select 6,'0003',80 , 'GIRLAGE6' , 6 union all
select 7,'0003',90 , 'GIRLAGE7' , 7 union all
select 8, '0003',60 , 'GIRLAGE8' , 8
select * from tb
select BOY.school from
(select school,sum(amount) as totalcount from (select school,amount,field,age from tb where age between 5 and 7 and substring(field,1,3)='BOY')A group by school)BOY
left join
(select school,amount,field,age from tb where age=8 and substring(field,1,4)='GIRL' and amount<30)GIRL
on BOY.school=GIRL.school and BOY.totalcount>=20