怎么判断一个表的字段里的内容有没有在另外一个表的字段内容中出现过
本帖最后由 simon_wdming 于 2013-10-23 20:19:01 编辑 例如有一个表A:
TemplateIdasxcodeDateReportIdCodeItemIDValue
PAAD2013/6/301BS00000300111100
PAAD1999/12/302BS00000400222200
PAAD2000/6/303BS00000400333300
NABN2010/12/304BS00002645444400
IADX2012/6/305CF00001300555500
另外一张表B:
ItemIDTemplateId
111NBIPV
222NBIP
333BI
444N
555PVN
怎么判断A表中的TemplateId有没有在B表中的TemplateId出现过?
如果有出现过则用Y标记,没有出现过用N标记.
比如上面A表中的第一行中的ItemID=111 和TemplateId = P,
在B表中的ItemID=111 的TemplateId = NBIPV 中有出现过.
能写出一个SQL语句得出下面结果表吗?
MarkTemplateIdasxcodeDateReportIdCodeItemIDValue
YPAAD2013/6/301BS00000300111100
YPAAD1999/12/302BS00000400222200
NPAAD2000/6/303BS00000400333300
YNABN2010/12/304BS00002645444400
NIADX2012/6/305CF00001300555500
判断某个字段的内容在另外字段内容中出现过
[解决办法]
是这样不：
drop table a
drop table b
go
create table A
(
TemplateId varchar(10),
asxcode varchar(10),
[Date] datetime,
ReportId int,
Code varchar(20),
ItemIDint,
Value int
)
insert into a
select 'P','AAD','2013/6/30', 1,'BS00000300',111,100 union all
select 'P','AAD','1999/12/30',2,'BS00000400',222,200 union all
select 'P','AAD','2000/6/30',3,'BS00000400',333,300 union all
select 'N','ABN','2010/12/30',4,'BS00002645',444,400 union all
select 'I','ADX','2012/6/30',5,'CF00001300',555,500
create table b(ItemID int,TemplateId varchar(20))
insert into b
select 111,'NBIPV' union all
select 222,'NBIP' union all
select 333,'BI' union all
select 444,'N' union all
select 555,'PVN'
select case when exists(select 1 from b
where charindex(a.TemplateId,b.TemplateId)>0
and b.ItemID = a.ItemID)
then 'Y'
else 'N'
end as mark,
a.*
from a
/*
mark TemplateId asxcode Date ReportId Code ItemID Value
---- ---------- ---------- ----------------------- ----------- -------------------- ----------- -----------
Y P AAD 2013-06-30 00:00:00.000 1 BS00000300 111 100
Y P AAD 1999-12-30 00:00:00.000 2 BS00000400 222 200
N P AAD 2000-06-30 00:00:00.000 3 BS00000400 333 300
Y N ABN 2010-12-30 00:00:00.000 4 BS00002645 444 400
N I ADX 2012-06-30 00:00:00.000 5 CF00001300 555 500
(5 行受影响)
*/
create table 表A
(TemplateId varchar(5),asxcode varchar(10),[Date] varchar(16),
ReportId int,Code varchar(16),ItemID int,Value int)
insert into 表A
select 'P','AAD','2013/6/30',1,'BS00000300',111,100 union all
select 'P','AAD','1999/12/30',2,'BS00000400',222,200 union all
select 'P','AAD','2000/6/30',3,'BS00000400',333,300 union all
select 'N','ABN','2010/12/30',4,'BS00002645',444,400 union all
select 'I','ADX','2012/6/30',5,'CF00001300',555,500
create table 表B
(ItemIDint,TemplateId varchar(10))
insert into 表B
select 111,'NBIPV' union all
select 222,'NBIP' union all
select 333,'BI' union all
select 444,'N' union all
select 555,'PVN'
select case when charindex(a.TemplateId,b.TemplateId,1)>0 then 'Y' else 'N' end 'Mark',
a.TemplateId,a.asxcode,a.[Date],a.ReportId,a.Code,a.ItemID,a.Value
from 表A a
left join 表B b on a.ItemID=b.ItemID
/*
Mark TemplateId asxcode Date ReportId Code ItemID Value
---- ---------- ---------- ---------------- ----------- ---------------- ----------- -----------
Y P AAD 2013/6/30 1 BS00000300 111 100
Y P AAD 1999/12/30 2 BS00000400 222 200
N P AAD 2000/6/30 3 BS00000400 333 300
Y N ABN 2010/12/30 4 BS00002645 444 400
N I ADX 2012/6/30 5 CF00001300 555 500
(5 row(s) affected)
*/
