tb
id province status
1 common 1
1 上海 0
2 common 1
2 广西 0
2 西川 0
3 common 0
3 上海 1
4 common 0
有如上表结构,status表示该省份是否可用,common表示通用配置.
如果common=0,上海=1,则上海可用.
如果common=1,上海=0,则上海不可用.
如果common=1,无上海配置,则上海可用.
如果common=0,无上海配置,则上海不可用.
我要筛选出所有上海能使用的记录id,上表的数据结果应该是2,3求sql语句!
id province status
1 common 1
1 上海 0
2 common 1
2 广西 0
2 西川 0
3 common 0
3 上海 1
4 common 0
有如上表结构,status表示该省份是否可用,common表示通用配置.
如果common=0,上海=1,则上海可用.
如果common=1,上海=0,则上海不可用.
如果common=1,无上海配置,则上海可用.
如果common=0,无上海配置,则上海不可用.
我要筛选出所有上海能使用的记录id,上表的数据结果应该是2,3求sql语句!
无上海配置,怎么知道是上海的?
declare @tb table (id int,province varchar(6),status int)
insert into @tb
select 1,'common',1 union all
select 1,'上海',0 union all
select 2,'common',1 union all
select 2,'广西',0 union all
select 2,'西川',0 union all
select 3,'common',0 union all
select 3,'上海',1 union all
select 4,'common',0select a.* from @tb a
left join @tb b on a.id=b.id and a.province<>b.province
where (a.province='上海' and a.status=1 and b.province='common'
and b.status=0)
/*
id province status
----------- -------- -----------
3 上海 1
*/
insert into tb select 1,'common',1
insert into tb select 1,'上海',0
insert into tb select 2,'common',1
insert into tb select 2,'广西',0
insert into tb select 2,'西川',0
insert into tb select 3,'common',0
insert into tb select 3,'上海',1
insert into tb select 4,'common',0
go
select id from tb a
where province='common' and (
(status=0 and exists(select 1 from tb where id=a.id and province='上海' and status=1))
or
(status=1 and not exists(select 1 from tb where id=a.id and province='上海' and status=0))
or
(status=1 and not exists(select 1 from tb where id=a.id and province='上海')))
go
drop table tb
/*
id
-----------
2
3(2 行受影响)
*/
select * from tb
where (
exists (select 1 from tb a where a.id=tb.id and a.province='common' and status=0)
and exists (select 1 from tb a where a.id=tb.id and a.province='上海' and status=1)
)
or
(
exists (select 1 from tb a where a.id=tb.id and a.province='common' and status=1)
and not exists (select 1 from tb a where a.id=tb.id and a.province='上海')
)