遇见一个不好解决的问题

Question

有一个表a字段    id    dm      cityId 
------------------------------
数据     1    新疆     1，2
数据     2    西藏     2，3，4
表b是字段       id      cityName
------------------------------
数据      1        乌鲁木齐
数据      2        成都
数据      3        包头
数据      4        东胜而a和b关联后要求显示为：字段      id       dm        cityName       cityId
--------------------------------------------------
数据       1      新疆       乌鲁木齐         1
数据       1      新疆       成都             2
数据       2      西藏       成都             2
数据       2      西藏       包头             3
数据       2      西藏       东胜             4

Answer 1

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 2

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 3

if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int, [dm] nvarchar(30),[cityId] varchar(30))
go
insert into [a]
select 1, N'新疆', '1,2' union all
select 2, N'西藏', '2,3,4'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int, [cityName] nvarchar(30))
go
insert into [b]
select 1, N'乌鲁木齐' union all
select 2, N'成都' union all
select 3, N'包头' union all
select 4, N'东胜'
goselect * from [a]select [a].[id],[a].[dm],[b].[cityName],[b].[id] cityId from [b],[a]
where charindex(convert(varchar(5),[b].[id])+',',convert(varchar(5),[a].[cityId])+',')>0/*(2 row(s) affected)(4 row(s) affected)
id          dm                             cityId
----------- ------------------------------ ------------------------------
1           新疆                             1,2
2           西藏                             2,3,4(2 row(s) affected)id          dm                             cityName                       cityId
----------- ------------------------------ ------------------------------ -----------
1           新疆                             乌鲁木齐                           1
1           新疆                             成都                             2
2           西藏                             成都                             2
2           西藏                             包头                             3
2           西藏                             东胜                             4(5 row(s) affected)
*/

Answer 4

select * from [b],[a] where charindex(','+ltrim([b].[id])+',',','+[a].[cityId]+',')>0
我修改了一下你的，因为当2，3，4后面加个1，变成11，21，31，41，就不对了