with report(id,title,username) as( select 1,'2011年度述职报告','1,2,3,4' union all select 2,'2012年度述职报告','3,4,5'), uploadreport( id,report,userid) as( select 1,1,'2' union all select 2,2,'3,4' ) select title,number username from report,uploadreport,master..spt_values tb where uploadreport.id=report.id and tb.type='p' and charindex(rtrim(number),username)>0 and charindex(rtrim(number),userid)<=0
再改改 with report(id,title,username) as( select 1,'2011年度述职报告','1,2,3,4' union all select 2,'2012年度述职报告','3,4,5'), uploadreport( id,report,userid) as( select 1,1,'2' union all select 2,2,'3,4' ) select title,number username from report,uploadreport,master..spt_values tb where uploadreport.id=report.id and tb.type='p' and charindex(','+rtrim(number)+',',','+username+',')>0 and charindex(','+rtrim(number)+',',','+userid+',')<=0
--> 测试数据:[report] if object_id('[report]') is not null drop table [report] GO create table [report]([id] int,[title] varchar(16),[username] varchar(7)) insert [report] select 1,'2011年度述职报告','1,2,3,4' union all select 2,'2012年度述职报告','3,4,5' --> 测试数据:[uploadreport] if object_id('[uploadreport]') is not null drop table [uploadreport] GO create table [uploadreport]([id] int,[report] int,[userid] int) insert [uploadreport] select 1,1,2 union all select 2,2,3 union all select 2,2,4 select distinct [title],t.number from [report] a join master..spt_values t on charindex(','+rtrim(t.number)+',',','+[username]+',')>0 where not exists(select 1 from [uploadreport] where [report]=a.id and userid=t.number) order by title/* title number ---------------- ----------- 2011年度述职报告 1 2011年度述职报告 3 2011年度述职报告 4 2012年度述职报告 5(4 行受影响) */ drop table [report] drop table [uploadreport]
as(
select 1,'2011年度述职报告','1,2,3,4' union all
select 2,'2012年度述职报告','3,4,5'),
uploadreport( id,report,userid)
as(
select 1,1,'2' union all
select 2,2,'3,4'
)
select title,number username from report,uploadreport,master..spt_values tb where uploadreport.id=report.id and tb.type='p'
and charindex(rtrim(number),username)>0 and charindex(rtrim(number),userid)<=0
with report(id,title,username)
as(
select 1,'2011年度述职报告','1,2,3,4' union all
select 2,'2012年度述职报告','3,4,5'),
uploadreport( id,report,userid)
as(
select 1,1,'2' union all
select 2,2,'3,4'
)
select title,number username from report,uploadreport,master..spt_values tb where uploadreport.id=report.id and tb.type='p'
and charindex(','+rtrim(number)+',',','+username+',')>0 and charindex(','+rtrim(number)+',',','+userid+',')<=0
if object_id('[report]') is not null drop table [report]
GO
create table [report]([id] int,[title] varchar(16),[username] varchar(7))
insert [report]
select 1,'2011年度述职报告','1,2,3,4' union all
select 2,'2012年度述职报告','3,4,5'
--> 测试数据:[uploadreport]
if object_id('[uploadreport]') is not null drop table [uploadreport]
GO
create table [uploadreport]([id] int,[report] int,[userid] int)
insert [uploadreport]
select 1,1,2 union all
select 2,2,3 union all
select 2,2,4
select distinct [title],t.number
from [report] a join master..spt_values t on charindex(','+rtrim(t.number)+',',','+[username]+',')>0
where not exists(select 1 from [uploadreport] where [report]=a.id and userid=t.number)
order by title/*
title number
---------------- -----------
2011年度述职报告 1
2011年度述职报告 3
2011年度述职报告 4
2012年度述职报告 5(4 行受影响)
*/
drop table [report]
drop table [uploadreport]