查询包含字符的SQL语句？
各大师，请教下，有下面两张表
表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

/*
celcelnum
EEE1*/

[其他解释]

--更正一下
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

/*
celcelnum
AAA1
BBB3
CCC4
DDD2
EEE1
EEE4*/

[其他解释]

select a.CEL,b.CELNUM from t1 as a 
 
Left join t2 as b on Charindex(ltrim(a.NUM),b.STRNUM)>0 and a.COVTYEP=b.FLAG)

[其他解释]

引用:

各大师，请教下，有下面两张表
表1：
CEL COVTYEP NUM
AAA outdoor 53
BBB outdoor 516
CCC outdoor 740
DDD indoor 50
EEE outdoor 44
FFF ……

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'

引用:

SQL code?



12345678910111213141516171819202122232425262728293031

if object_id('[表1]') is not null drop table [表1] go create table [表1] (CEL nvarchar(6),COVTYEP nvarchar(14),NUM int) insert in……

高手，你写的应该NUM与STRNUM是模糊匹配吧。像EEE就出现两个值，所以要完全匹配哦。谢谢！
[其他解释]

引用:

SQL code?1234567891011121314--更正一下SELECT a.cel,b.celnumFROM [表2] bINNER JOIN [表1] a ON a.covtyep = b.flag AND CHARINDEX(CONVERT(VARCHAR,a.num),b.strnum) >0 /*cel celnumAAA 1BBB 3CCC 4DDD ……

正解