求sql语句一条
表A
rowidA nameA
1 苹果
2 香蕉
3 肉丸
4 贡丸
表B
rowidB nameB rowidA
1 水果 1,2
2 食品 3,4
要求查询结果
rowidB nameB nameA
1 水果 苹果
1 水果 香蕉
2 食品 肉丸
2 食品 贡丸
sql
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-14 20:46:05
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([rowidA] int,[nameA] varchar(4))
insert [A]
select 1,'苹果' union all
select 2,'香蕉' union all
select 3,'肉丸' union all
select 4,'贡丸'
--------------开始查询--------------------------
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([rowidB] int,[nameB] varchar(4),[rowidA] varchar(3))
insert [B]
select 1,'水果','1,2' union all
select 2,'食品','3,4'
--------------开始查询--------------------------
SELECT b.[rowidB] ,
b.nameB ,
a.nameA
FROM [A] a
INNER JOIN ( SELECT [rowidB] ,
[nameB] ,
SUBSTRING([rowidA], number,
CHARINDEX(',', [rowidA] + ',', number)
- number) AS [rowidA]
FROM (SELECT * FROM [B] WHERE rowidB=1) a ,
master..spt_values
WHERE number >= 1
AND number <= LEN([rowidA])
AND type = 'p'
AND SUBSTRING(',' + [rowidA], number, 1) = ','
) b ON a.rowidA = b.rowidA
----------------结果----------------------------
/*
rowidB nameB nameA
----------- ----- -----
1 水果 苹果
1 水果 香蕉
(2 行受影响)
*/
