select distinct student_id,st from feedback where st='1' and student_id not in (select student_id from feedback where st>'1')
create table feedback (student_id int ,st int ) insert into feedback select 101,0 insert into feedback select 101,1 insert into feedback select 102,0 insert into feedback select 103,0 insert into feedback select 102,1 insert into feedback select 103,1 insert into feedback select 103,2 insert into feedback select 104,0select * from feedback a where st = 1 and exists ( select 1 from feedback where a.student_id = student_id and st >= a.st group by student_id having count(1)=1 ) student_id st ----------- ----------- 101 1 102 1(所影响的行数为 2 行)
create table tb(student_id int,st int) insert into tb values(101, 0 ) insert into tb values(101, 1 ) insert into tb values(102, 0 ) insert into tb values(103, 0 ) insert into tb values(102, 1 ) insert into tb values(103, 1 ) insert into tb values(103, 2 ) insert into tb values(104, 0 ) goselect a.* from tb a where st = 1 and student_id not in ( select student_id from ( select distinct student_id from tb where st = 0 union all select distinct student_id from tb where st > 1 ) t group by student_id having count(*) > 1 ) order by student_iddrop table tb/* student_id st ----------- ----------- 101 1 102 1(所影响的行数为 2 行) */
create table feedback ( student_id varchar(6), st int )insert into feedback select '101',0 union select '101',1 union select '102',0 union select '103',0 union select '102',1 union select '103',1 union select '103',1 union select '103',2 union select '103',0select student_id,st from feedback where st=1 and student_id not in(select student_id from feedback where st>1)
select * from feedback where student_id not in ( select distinct student_id from feedback where st=0 or st> 1 )
--这还不短啊!! select student_id,st from feedback where st=1 and student_id not in(select student_id from feedback where st>1)
create table tb(student_id int,st int) insert into tb values(101, 0 ) insert into tb values(101, 1 ) insert into tb values(102, 0 ) insert into tb values(103, 0 ) insert into tb values(102, 1 ) insert into tb values(103, 1 ) insert into tb values(103, 2 ) insert into tb values(104, 0 ) goselect student_id,max(st) st from tb group by student_id having count(distinct st)<3 and max(st)=1 go drop table tb
select student_id,max(st) st from tb group by student_id having max(st)=1
select * from feedback a where st=1 and not exists(select count(*) from feedback where student_id=a.student_id having count(*)>1) and not exists(select * from feedback where student_id=a.student_id and (st=0 or st>1))
--student_id st -- 101 0 -- 101 1 -- 102 0 -- 103 0 -- 102 1 -- 103 1 -- 103 2 -- 104 0 ------------------------ --查询出st=1的student_id ,并且student_id 不能重复,如果某个符合条件的student_id有同时出现st=0,或者st> 1的情况,也过滤掉。按照我的意思:结果应该是 --101 1 --102 1 if object_id('t_query') is not null drop table t_query go create table t_query (student_id int ,st int ) insert into t_query select 101,0 insert into t_query select 101,1 insert into t_query select 102,0 insert into t_query select 103,0 insert into t_query select 102,1 insert into t_query select 103,1 insert into t_query select 103,2 insert into t_query select 104,0 select b.* from (select student_id,max(st)as 最大st,min(st) as 最小st from t_query group by student_id)b where b.最大st=1
select student_id,st from feedback where st=1 and student_id not in(select student_id from feedback where st>1)
insert into feedback select 101,0
insert into feedback select 101,1
insert into feedback select 102,0
insert into feedback select 103,0
insert into feedback select 102,1
insert into feedback select 103,1
insert into feedback select 103,2
insert into feedback select 104,0select * from feedback a
where st = 1 and exists (
select 1
from feedback
where a.student_id = student_id and st >= a.st
group by student_id
having count(1)=1 )
student_id st
----------- -----------
101 1
102 1(所影响的行数为 2 行)
insert into tb values(101, 0 )
insert into tb values(101, 1 )
insert into tb values(102, 0 )
insert into tb values(103, 0 )
insert into tb values(102, 1 )
insert into tb values(103, 1 )
insert into tb values(103, 2 )
insert into tb values(104, 0 )
goselect a.* from tb a where st = 1 and student_id not in
(
select student_id from
(
select distinct student_id from tb where st = 0
union all
select distinct student_id from tb where st > 1
) t
group by student_id having count(*) > 1
)
order by student_iddrop table tb/*
student_id st
----------- -----------
101 1
102 1(所影响的行数为 2 行)
*/
create table feedback
(
student_id varchar(6),
st int
)insert into feedback
select '101',0
union
select '101',1
union
select '102',0
union
select '103',0
union
select '102',1
union
select '103',1
union
select '103',1
union
select '103',2
union
select '103',0select student_id,st
from feedback
where st=1
and student_id not in(select student_id
from feedback
where st>1)
where student_id not in
(
select distinct student_id from feedback where st=0 or st> 1
)
--这还不短啊!!
select student_id,st
from feedback
where st=1
and student_id not in(select student_id
from feedback
where st>1)
insert into tb values(101, 0 )
insert into tb values(101, 1 )
insert into tb values(102, 0 )
insert into tb values(103, 0 )
insert into tb values(102, 1 )
insert into tb values(103, 1 )
insert into tb values(103, 2 )
insert into tb values(104, 0 )
goselect student_id,max(st) st
from tb group by student_id
having count(distinct st)<3 and max(st)=1
go
drop table tb
from tb group by student_id
having max(st)=1
not exists(select count(*) from feedback where student_id=a.student_id having count(*)>1)
and not exists(select * from feedback where student_id=a.student_id and (st=0 or st>1))
如果某个符合条件的student_id有同时出现
a.st=0且st=1,
b.st> 1 且st=1
的情况,两种情况都要过滤掉
-- 101 0
-- 101 1
-- 102 0
-- 103 0
-- 102 1
-- 103 1
-- 103 2
-- 104 0
------------------------
--查询出st=1的student_id ,并且student_id 不能重复,如果某个符合条件的student_id有同时出现st=0,或者st> 1的情况,也过滤掉。按照我的意思:结果应该是
--101 1
--102 1
if object_id('t_query') is not null
drop table t_query
go
create table t_query (student_id int ,st int )
insert into t_query select 101,0
insert into t_query select 101,1
insert into t_query select 102,0
insert into t_query select 103,0
insert into t_query select 102,1
insert into t_query select 103,1
insert into t_query select 103,2
insert into t_query select 104,0
select b.* from (select student_id,max(st)as 最大st,min(st) as 最小st from t_query group by student_id)b
where b.最大st=1
from feedback
where st=1
and student_id not in(select student_id
from feedback
where st>1)
101 0
101 1
102 0
102 1
103 0
103 1
103 2
101 0
101 1
在这种情况下 上面的给的回复就有问题了 重现重复的 --student_id 的了 6楼的结果也重复了
--student_id st
101 1
102 1
101 1