各大师,请教下,有下面两张表
表1:
CEL COVTYEP NUM
AAA outdoor 53
BBB outdoor 516
CCC outdoor 740
DDD indoor 50
EEE outdoor 44
FFF indoor 746
表2:
CELNUM FLAG STRNUM
1 outdoor 44,47,50,53,56
2 indoor 44, 47, 50, 53
3 outdoor 512, 514, 516, 518
4 outdoor 738, 740, 742, 744, 746
5 indoor 736, 740, 742, 744
如上表两张表:如何实现SQL语句查询出CEL以及CEL字段对应的CELNUM值。如查询AAA的CELLNUM,则从NUM里53对应到STRNUM字段,若包含53且FLAG=COVTYPE,则显示出该AAA值和CELNUM的1值。
------解决方案--------------------
if object_id('[表1]') is not null drop table [表1]
go
create table [表1] (CEL nvarchar(6),COVTYEP nvarchar(14),NUM int)
insert into [表1]
select 'AAA','outdoor',53 union all
select 'BBB','outdoor',516 union all
select 'CCC','outdoor',740 union all
select 'DDD','indoor',50 union all
select 'EEE','outdoor',44 union all
select 'FFF','indoor',746
if object_id('[表2]') is not null drop table [表2]
go
create table [表2] (CELNUM int,FLAG nvarchar(14),STRNUM VARCHAR(30))
insert into [表2]
select 1,'outdoor','44,47,50,53,56' union all
select 2,'indoor','44,47,50,53' union all
select 3,'outdoor','512,514,516,518' union all
select 4,'outdoor','738,740,742,744,746' union all
select 5,'indoor','736,740,742,744'
select * from [表1]
select * from [表2]
SELECT a.cel,b.celnum
FROM [表2] b
INNER JOIN [表1] a ON a.covtyep = b.flag AND CHARINDEX(CONVERT(VARCHAR,a.num),b.strnum) = 1
/*
cel celnum
EEE 1*/
------解决方案--------------------
--更正一下
SELECT a.cel,b.celnum
FROM [表2] b
INNER JOIN [表1] a ON a.covtyep = b.flag AND CHARINDEX(CONVERT(VARCHAR,a.num),b.strnum) >0
/*
cel celnum
AAA 1
BBB 3
CCC 4
DDD 2
EEE 1
EEE 4*/
------解决方案--------------------
select a.CEL,b.CELNUM from t1 as a