字段分解难题

[yonghanyang]

表A
有字段
ID 主演
id (int) actor nvarchar(255)
1 郑元畅 林依晨 赵咏华 章柏翰
2 路飞 卓洛 奈美 乌索普 山治 乔巴
3 乌索普 林依晨
4 下野纮
求生成下列格式的mssql语句
id 演员
1 郑元畅
2 林依晨
3 赵咏华
4 章柏翰
5 路飞
6 卓洛
7 奈美
8 乌索普
9 山治
10 乔巴
11 下野纮

[yonghanyang]

太感谢大家了，结贴拉,大家都有分!~

[feixianxxx]

[Quote=引用 30 楼 feixianxxx 的回复:]
引用 27 楼 yonghanyang 的回复:
几万条数据啊,怎么找

SQL codeIFOBJECT_ID('[tb]')ISNOTNULLDROPTABLE[tb]GoCREATETABLE tb([id]INT,[actor]NVARCHAR(250))GoINSERTINTO tbSELECT1,'郑元畅 林依晨 赵咏华 章柏翰'UNIONALLSELECT2,'路飞 卓洛 奈 美 乌索普 山治 乔巴'UNIONALLSELECT3,'乌索普 林依晨'UNIONALLSELECT4,'下野纮'GOIFOBJECT_ID('c')ISNOTNULLDROPTABLE cSELECTTOP1000 id=IDENTITY(int,1,1)INTO cFROM syscolumns a, syscolumns bselect id=IDENTITY(int,1,1),*into ##from(SELECTdistinctSUBSTRING(Aa.actor, B.id,CHARINDEX(',', Aa.actor+',', B.id)- B.id)as valueFROM (select id,actor=REPLACE(actor,'',',')from tb ) Aa, c BWHERESUBSTRING(','+ Aa.actor, B.id,1)=',' )rgoselect id=IDENTITY(int,1,1),*into #####from (selectcasewhenlen(value)=1then value+''+(select valuefrom ##where id=#.id+1)else valueendas valuefrom ## #) ####goselect valueinto ktfrom #####goselect id=IDENTITY(int,1,1),*into ktcfrom ktwhereLEN(value)<=3goselect*from ktc/*
1 林依晨
2 路飞
3 美 奈
4 乔巴
5 山治
6 乌索普
7 下野纮
8 章柏翰
9 赵咏华
10 郑元畅
11 卓洛*/
[/Quote]
修改的简单了点

IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
Go
CREATE TABLE tb([id] INT,[actor] NVARCHAR(250))
Go
INSERT INTO tb
   SELECT 1,'郑元畅 林依晨 赵咏华 章柏翰' UNION ALL
   SELECT 2,'路飞 卓洛 奈 美 乌索普 山治 乔巴' UNION ALL
   SELECT 3,'乌索普 林依晨' UNION ALL
   SELECT 4,'下野纮' 
GO

IF OBJECT_ID('c') IS NOT NULL 
    DROP TABLE c
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO c FROM syscolumns a, syscolumns b 
select id = IDENTITY(int, 1, 1),* into ##
from(
SELECT   distinct SUBSTRING(Aa.actor, B.id, CHARINDEX(',', Aa.actor + ',', B.id) - B.id) as value
FROM (select id,actor=REPLACE(actor,' ',',') from  tb ) Aa, c B 
WHERE SUBSTRING(',' + Aa.actor, B.id, 1) = ',' )r
go

select id = IDENTITY(int, 1, 1),* into ##### from (
select case when len(value)=1 then value+' '+(select value from ## where id=#.id+1) else value end  as value 
from ## #) #### where LEN(value)<=3
go
select * from #####


/*
1	林依晨
2	路飞
3	美 奈
4	乔巴
5	山治
6	乌索普
7	下野纮
8	章柏翰
9	赵咏华
10	郑元畅
11	卓洛
*/

[feixianxxx]

[Quote=引用 31 楼 yonghanyang 的回复:]
太感谢大家了，结贴拉,大家都有分!~
[/Quote]
喂。。先试试我的优化版 可以实现你的功能了

[feixianxxx]

LZ人海在不

[yonghanyang]

我在,我刚才在测试

[yonghanyang]

我在,我刚才在测试

[yonghanyang]

feixianxxx 能否帮忙精简下语句

[feixianxxx]

[Quote=引用 37 楼 yonghanyang 的回复:]
feixianxxx 能否帮忙精简下语句
[/Quote]
我试试 能行不 能实现不？这语句

[yonghanyang]

feixianxxx 能否帮忙精简下语句

[feixianxxx]

[Quote=引用 37 楼 yonghanyang 的回复:]
feixianxxx 能否帮忙精简下语句
[/Quote]

IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
Go
CREATE TABLE tb([id] INT,[actor] NVARCHAR(250))
Go
INSERT INTO tb
   SELECT 1,'郑元畅 林依晨 赵咏华 章柏翰' UNION ALL
   SELECT 2,'路飞 卓洛 奈 美 乌索普 山治 乔巴' UNION ALL
   SELECT 3,'乌索普 林依晨' UNION ALL
   SELECT 4,'下野纮' 
GO
--这部实现的是字段的分离和去重复 效果如下
/*
1	林依晨
2	路飞
3	美
4	奈
5	乔巴
6	山治
7	乌索普
8	下野纮
9	章柏翰
10	赵咏华
11	郑元畅
12	卓洛
*/
IF OBJECT_ID('c') IS NOT NULL 
    DROP TABLE c
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO c FROM syscolumns a, syscolumns b 
select id = IDENTITY(int, 1, 1),* into ##
from(
SELECT   distinct SUBSTRING(Aa.actor, B.id, CHARINDEX(',', Aa.actor + ',', B.id) - B.id) as value
FROM (select id,actor=REPLACE(actor,' ',',') from  tb ) Aa, c B 
WHERE SUBSTRING(',' + Aa.actor, B.id, 1) = ',' )r
go
--这部分实现的是单字的字段 组合成  双字 中间代空格的名字
select id = IDENTITY(int, 1, 1),* into ##### from (
select case when len(value)=1 then value+' '+(select value from ## where id=#.id+1) else value end  as value 
from ## #) #### where LEN(value)<=3
go
--显示效果
select * from #####


/*
1	林依晨
2	路飞
3	美 奈
4	乔巴
5	山治
6	乌索普
7	下野纮
8	章柏翰
9	赵咏华
10	郑元畅
11	卓洛
*/

[yonghanyang]

能否用一条语句呢

[feixianxxx]

[Quote=引用 39 楼 yonghanyang 的回复:]
feixianxxx 能否帮忙精简下语句
[/Quote]

IF OBJECT_ID('c') IS NOT NULL 
    DROP TABLE c
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO c FROM syscolumns a, syscolumns b 
select id = IDENTITY(int, 1, 1),* into ##
from(
SELECT   distinct SUBSTRING(Aa.actor, B.id, CHARINDEX(',', Aa.actor + ',', B.id) - B.id) as value
FROM (select id,actor=REPLACE(actor,' ',',') from  tb ) Aa, c B 
WHERE SUBSTRING(',' + Aa.actor, B.id, 1) = ',' )r
go
--这部分实现的是单字的字段 组合成  双字 中间代空格的名字
select value from (
select case when len(value)=1 then value+' '+(select value from ## where id=#.id+1) else value end  as value 
from ## # )t where LEN(value)<=3

这个简化了。。
没有ID列的 只有明星姓名

[feixianxxx]

[Quote=引用 41 楼 yonghanyang 的回复:]
能否用一条语句呢
[/Quote]
嘻嘻 估计难啦
我可是花了好久才帮你想出这么个笨办法的。。

[feixianxxx]

[Quote=引用 41 楼 yonghanyang 的回复:]
能否用一条语句呢
[/Quote]
LZ又消失了。。

[yonghanyang]

呵呵，我是难为你了，我先不结贴,困了，明天再来看看,不过我一定会给分的,帮忙的人都有分,不多，但是很感谢啊

[feixianxxx]

[Quote=引用 45 楼 yonghanyang 的回复:]
呵呵，我是难为你了，我先不结贴,困了，明天再来看看,不过我一定会给分的,帮忙的人都有分,不多，但是很感谢啊
[/Quote]
你还没告诉我 我的方法行不 测试通过了么？

[yonghanyang]

能执行，不过效率不高啊，我该下了，家里人不让我上了

[超维电脑科技]

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL code--> 测试数据: #Tifobject_id('tempdb.dbo.#T')isnotnulldroptable #Tcreatetable #T (idint,namevarchar(8))insertinto #Tselect1,'jame,job'unionallselect2,'paul,mc'unionallselect3,'carl';--> 1. CTE 递归找分¡­
[/Quote]
学习下蹭分下

[feixianxxx]

[Quote=引用 47 楼 yonghanyang 的回复:]
能执行，不过效率不高啊，我该下了，家里人不让我上了
[/Quote]
好的。。晚安。。。
做出来了
我好开心啊。。

[htl258_Tony]

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-07 23:39:33
---------------------------------
--> 生成测试数据表-tb

if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[actor] nvarchar(255))
Insert tb
select 1,'郑元畅 林依晨 赵咏华 章柏翰' union all
select 2,'路 飞 卓 洛 奈 美 乌索普 山 治 乔 巴' union all
select 3,'乌索普 林依晨' union all
select 4,'下野纮'
Go
--Select * from tb

-->SQL查询如下:
;with t as
(
	select 
		rn=row_number() over(order by id),
		演员= substring(replace(a.[actor],' ',','), b.number, charindex(',', replace(a.[actor],' ',',') + ',', b.number) - b.number)
	from tb a,master..spt_values b 
	where b.type='p' 
		and substring(',' + replace(a.[actor],' ',','),b.number,1) = ','
) ,t1 as
(
    select rn2=(row_number() over(order by rn)-1)/2,* from t where len(演员)=1
), t2 as
(
	select distinct 演员=ltrim((select ' '+演员 from t1 where rn2=a.rn2 for xml path(''))) from t1 a
	union 
	select 演员 from t where len(演员)<>1
)
select id=row_number() over(order by 演员),* from t2
/*
id                   演员
-------------------- --------------------------------
1                    林依晨
2                    路 飞
3                    奈 美
4                    乔 巴
5                    山 治
6                    乌索普
7                    下野纮
8                    章柏翰
9                    赵咏华
10                   郑元畅
11                   卓 洛

(11 行受影响)

*/