22,207
社区成员
发帖
与我相关
我的任务
分享
WITH StateCTE(UserId,State,Time) AS
(
SELECT SureUserId,'Yes',Update_Time FROM RS_SureRule WHERE SureRuleId = '311'
)
SELECT * FROM StateCTE
UNION
SELECT UserId,'No',NULL FROM RS_User WHERE UserId NOT IN(SELECT UserId FROM StateCTE)
ORDER BY UserId
select * from RS_Rule where id not in(select SureRuleId from RS_SureRule where SureUserId='9005')
/*
Id Title Text Creater Updater
------------ ---------- -------------------------------------------------- ------------ ------------
311 a abc 9005 9009
313 c fag 9005 9005
(所影响的行数为 2 行)
*/
use PracticeDB
go
if exists (select 1 from sysobjects where name in ('RS_SureRule'))
drop table RS_SureRule
go
create table RS_SureRule
(
SureUserId numeric(10),
SureRuleId numeric(10),
Update_Time datetime,
Update_Host varchar(50)
)
go
insert into RS_SureRule
select 9007 ,311 ,'2010/5/13 16:45','DV-LIN' union all
select 9007 ,313 ,'2010/5/13 18:03','DV-LIN' union all
select 9005 ,314 ,'2010/5/13 18:08','DV-LIN' union all
select 9005 ,308 ,'2010/5/13 14:25','DV-LIN' union all
select 9005 ,312 ,'2010/5/13 14:31','DV-LIN' union all
select 9006 ,311 ,'2010/5/13 14:32','DV-LIN' union all
select 9009 ,312 ,'2010/5/13 14:33','DV-LIN'
go
create table RS_Rule
(
Id numeric(10),
Title varchar(10),
Text varchar(50),
Creater numeric(10),
Updater numeric(10)
)
go
insert into RS_Rule
select 311, 'a' ,'abc' ,9005 ,9009 union all
select 312, 'b' ,'bdf' ,9006 ,9007 union all
select 313, 'c' ,'fag' ,9005 ,9005 union all
select 314, 'd' ,'gaf' ,9006 ,9008
go
create table RS_User
(
UserId numeric(10),
UserName varchar(50)
)
go
insert into RS_User
select 9005, 'Fay' union all
select 9006, 'Betty' union all
select 9007, 'Ninocle' union all
select 9008, 'Lucy' union all
select 9009, 'Lily'
select top 3* from RS_Rule
select top 3* from RS_SureRule
select top 3* from RS_User
select *
from RS_Rule r
where r.Id not in(select r.Id
from RS_Rule r join RS_SureRule s on r.Id=s.SureRuleId
left join RS_User u on s.SureUserId=u.UserId
where u.UserName='Fay')
select *
from RS_Rule
where id not in (select SureRuleId
from RS_SureRule)