用sql把多个列以,分隔的转为多行,求各解决方案,感谢。 本帖最后由 chinahuyong 于 2014-07-23 09:39:03 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 看你的表中,主键应该是VAR0,VAR1,VAR4,VAR8,创建一个表值function,用来分割字符串,然后和原表按照主键关联join;由于VAR2和VAR7可能同时存在多值情况,因此要多做一次full join ------------------------------------------------------------------ Author :DBA_HuangZJ(發糞塗牆)-- Date :2014-07-23 10:18:08-- Version:-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)--------------------------------------------------------------------> 测试数据:[hunag]if object_id('[hunag]') is not null drop table [hunag]go create table [hunag]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(17),[VAR4] datetime,[VAR7] varchar(25),[VAR8] varchar(6))insert [hunag]select '贵人鸟',603555,'11.8,12.2,12.6','2014-01-15','7100000,7100000,7100000','宋子明' union allselect '贵人鸟',603555,'10.38,10.88,10.58','2014-01-15','21000000,7000000,14000000','严琳'--------------开始查询--------------------------SELECT DISTINCT A.[VAR0],A.[VAR1],A.[VAR2],A.[VAR4],B.[VAR7],A.[VAR8]FROM (select [VAR0], a.[VAR1], SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4], --SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number)[VAR7], [VAR8]from [hunag] a,master..spt_values where number >=1 and number<=len([VAR2]) and type='p' and substring(','+[VAR2],number,1)=',')AINNER JOIN (select [VAR0], a.[VAR1], SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7], [VAR4], [VAR8]FROM [hunag] a,master..spt_values where number >=1 and number<=len([VAR7]) and type='p' and substring(','+[VAR7],number,1)=',')B ON A.VAR0=B.VAR0 AND A.VAR1=B.VAR1 AND A.VAR4=B.VAR4 AND A.VAR8=B.VAR8----------------结果----------------------------/* VAR0 VAR1 VAR2 VAR4 VAR7 VAR8------ ----------- ----------------- ----------------------- ------------------------- ------贵人鸟 603555 10.38 2014-01-15 00:00:00.000 14000000 严琳贵人鸟 603555 10.38 2014-01-15 00:00:00.000 21000000 严琳贵人鸟 603555 10.38 2014-01-15 00:00:00.000 7000000 严琳贵人鸟 603555 10.58 2014-01-15 00:00:00.000 14000000 严琳贵人鸟 603555 10.58 2014-01-15 00:00:00.000 21000000 严琳贵人鸟 603555 10.58 2014-01-15 00:00:00.000 7000000 严琳贵人鸟 603555 10.88 2014-01-15 00:00:00.000 14000000 严琳贵人鸟 603555 10.88 2014-01-15 00:00:00.000 21000000 严琳贵人鸟 603555 10.88 2014-01-15 00:00:00.000 7000000 严琳贵人鸟 603555 11.8 2014-01-15 00:00:00.000 7100000 宋子明贵人鸟 603555 12.2 2014-01-15 00:00:00.000 7100000 宋子明贵人鸟 603555 12.6 2014-01-15 00:00:00.000 7100000 宋子明*/ ------------------------------------------------------------------ Author :DBA_HuangZJ(發糞塗牆)-- Date :2014-07-23 11:19:32-- Version:-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)--------------------------------------------------------------------> 测试数据:[huang]if object_id('[huang]') is not null drop table [huang]go create table [huang]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))insert [huang]select '贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union allselect '贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union allselect '贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union allselect '贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'--------------开始查询--------------------------SELECT DISTINCT a.[VAR0],a.[VAR1],a.[VAR2],b.[VAR3],a.VAR4,a.[VAR5],[VAR6],c.[VAR7]FROM (select [VAR0], [VAR1], SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4], [VAR5],[VAR6]from huang a,master..spt_values where number >=1 and number<=len([VAR2]) and type='p' and substring(','+[VAR2],number,1)=',')a INNER JOIN (select [VAR0], [VAR1], SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3]from huang a,master..spt_values where number >=1 and number<=len([VAR3]) and type='p' and substring(','+[VAR3],number,1)=',')b ON a.var0=b.var0 AND a.var1=b.var1INNER JOIN ( select [VAR0], [VAR1], SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7]from huang a,master..spt_values where number >=1 and number<=len([VAR7]) and type='p' and substring(','+[VAR7],number,1)=',') c ON a.var0=c.var0 AND a.var1=c.var1----------------结果----------------------------/* VAR0 VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 24000000贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 23000000贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 24000000*/ 也就是说你这个不需要组合?VAR2,3,7这三个一定只有3行是吧? 不一定某列是三个数据,有的是两个VAR2,3,7这三个一定只有3行在上面的实例中是3行,有的也有是两个值的情况,那么就对应的是两行,不过VAR2,3,7这三个字段对应的行数都是一样多的。 本帖最后由 DBA_Huangzj 于 2014-07-23 14:41:59 编辑 专家,有个ID字段是主键,或GUID,这个就是主键。 ------------------------------------------------------------------ Author :DBA_HuangZJ(發糞塗牆)-- Date :2014-07-23 11:19:32-- Version:-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)--------------------------------------------------------------------> 测试数据:[huang]if object_id('[huang]') is not null drop table [huang]go create table [huang](ID INT ,[VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))insert [huang]select 1,'贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union allselect 2,'贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union allselect 3,'贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union allselect 4,'贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'--------------开始查询--------------------------SELECT DISTINCT a.ID,a.var0,a.var1,a.var2,b.var3,a.var4,a.var5,a.var6,c.VAR7FROM (select id, [VAR0], [VAR1], SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4], [VAR5],[VAR6],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2from huang a,master..spt_values where number >=1 and number<=len([VAR2]) and type='p' and substring(','+[VAR2],number,1)=',')a INNER JOIN (SELECT id, [VAR0], [VAR1], SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2from huang a,master..spt_values WHERE number >=1 and number<=len([VAR3]) and type='p' and substring(','+[VAR3],number,1)=',')b ON a.id=b.id AND a.id2=b.id2 INNER JOIN ( select id, [VAR0], [VAR1], SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2 FROM huang a,master..spt_values where number >=1 and number<=len([VAR7]) and type='p' and substring(','+[VAR7],number,1)=',')c ON a.id=c.id AND a.id2=c.id2 ----------------结果----------------------------/* ID var0 var1 var2 var3 var4 var5 var6 VAR7----------- ------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------1 贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 240000001 贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 230000001 贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 230000002 贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 240000002 贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 230000002 贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 230000003 贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 240000003 贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 230000003 贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 230000004 贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 240000004 贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 230000004 贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 23000000*/ SQL触发器case when 判断问题!! 不要嫌我啰嗦和犹豫不决,我只是想最后问一次,求各位高人帮我做个决定,关于标签的数据库设计 查询两张表,得到一个合计,怎么查? 各单位计算机数量的统计 求一个where方法,不含用指定关键字 两个数据表 连接查询问题 400分请CSDN的朋友帮忙用ASP开发一个基于WEB的“请假单录入”系统。 请问想执行如下的insert语句,如何实现 为何Sql Server 2005 性能比2000低很多? 请问有谁做过用VB编程控制SQL SERVER的同步(复制)? sql 2005分区文件多次备份与还原问题 大神求教:SQL Server 2012 总是阻塞,并且回滚特别慢,以及索引优化的问题
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-23 10:18:08
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[hunag]
if object_id('[hunag]') is not null drop table [hunag]
go
create table [hunag]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(17),[VAR4] datetime,[VAR7] varchar(25),[VAR8] varchar(6))
insert [hunag]
select '贵人鸟',603555,'11.8,12.2,12.6','2014-01-15','7100000,7100000,7100000','宋子明' union all
select '贵人鸟',603555,'10.38,10.88,10.58','2014-01-15','21000000,7000000,14000000','严琳'
--------------开始查询--------------------------
SELECT DISTINCT A.[VAR0],A.[VAR1],A.[VAR2],A.[VAR4],B.[VAR7],A.[VAR8]
FROM
(select
[VAR0],
a.[VAR1],
SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4],
--SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number)[VAR7],
[VAR8]
from
[hunag] a,master..spt_values
where
number >=1 and number<=len([VAR2])
and type='p'
and substring(','+[VAR2],number,1)=',')A
INNER JOIN (select
[VAR0],
a.[VAR1],
SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7], [VAR4],
[VAR8]
FROM
[hunag] a,master..spt_values
where
number >=1 and number<=len([VAR7])
and type='p'
and substring(','+[VAR7],number,1)=',')B ON A.VAR0=B.VAR0 AND A.VAR1=B.VAR1 AND A.VAR4=B.VAR4 AND A.VAR8=B.VAR8----------------结果----------------------------
/*
VAR0 VAR1 VAR2 VAR4 VAR7 VAR8
------ ----------- ----------------- ----------------------- ------------------------- ------
贵人鸟 603555 10.38 2014-01-15 00:00:00.000 14000000 严琳
贵人鸟 603555 10.38 2014-01-15 00:00:00.000 21000000 严琳
贵人鸟 603555 10.38 2014-01-15 00:00:00.000 7000000 严琳
贵人鸟 603555 10.58 2014-01-15 00:00:00.000 14000000 严琳
贵人鸟 603555 10.58 2014-01-15 00:00:00.000 21000000 严琳
贵人鸟 603555 10.58 2014-01-15 00:00:00.000 7000000 严琳
贵人鸟 603555 10.88 2014-01-15 00:00:00.000 14000000 严琳
贵人鸟 603555 10.88 2014-01-15 00:00:00.000 21000000 严琳
贵人鸟 603555 10.88 2014-01-15 00:00:00.000 7000000 严琳
贵人鸟 603555 11.8 2014-01-15 00:00:00.000 7100000 宋子明
贵人鸟 603555 12.2 2014-01-15 00:00:00.000 7100000 宋子明
贵人鸟 603555 12.6 2014-01-15 00:00:00.000 7100000 宋子明
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-23 11:19:32
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select '贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select '贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select '贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------SELECT DISTINCT a.[VAR0],a.[VAR1],a.[VAR2],b.[VAR3],a.VAR4,a.[VAR5],[VAR6],c.[VAR7]
FROM (select
[VAR0],
[VAR1],
SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4], [VAR5],[VAR6]
from
huang a,master..spt_values
where
number >=1 and number<=len([VAR2])
and type='p'
and substring(','+[VAR2],number,1)=',')a INNER JOIN (
select
[VAR0],
[VAR1],
SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3]
from
huang a,master..spt_values
where
number >=1 and number<=len([VAR3])
and type='p'
and substring(','+[VAR3],number,1)=',')b ON a.var0=b.var0 AND a.var1=b.var1
INNER JOIN (
select
[VAR0],
[VAR1],
SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7]
from
huang a,master..spt_values
where
number >=1 and number<=len([VAR7])
and type='p'
and substring(','+[VAR7],number,1)=',') c ON a.var0=c.var0 AND a.var1=c.var1----------------结果----------------------------
/*
VAR0 VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7
------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
*/
VAR2,3,7这三个一定只有3行在上面的实例中是3行,有的也有是两个值的情况,那么就对应的是两行,不过VAR2,3,7这三个字段对应的行数都是一样多的。
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-23 11:19:32
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang](ID INT ,[VAR0] varchar(6),[VAR1] int,[VAR2] varchar(15),[VAR3] varchar(14),[VAR4] date,[VAR5] date,[VAR6] date,[VAR7] varchar(26))
insert [huang]
select 1,'贵人鸟',603555,'10.9,10.78,10.6','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,23000000,24000000' union all
select 2,'贵人鸟',603555,'10.7,11.,10.88','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 3,'贵人鸟',603555,'10.8,11.1,10.98','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','24000000,23000000,23000000' union all
select 4,'贵人鸟',603555,'10.68,10.5,10.8','10.6,10.6,10.6','2014-01-15','2014-01-14','2014-01-15','23000000,24000000,23000000'
--------------开始查询--------------------------
SELECT DISTINCT a.ID,a.var0,a.var1,a.var2,b.var3,a.var4,a.var5,a.var6,c.VAR7
FROM (
select id,
[VAR0],
[VAR1],
SUBSTRING([VAR2],number,CHARINDEX(',',[VAR2]+',',number)-number) as [VAR2], [VAR4], [VAR5],[VAR6],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2
from
huang a,master..spt_values
where
number >=1 and number<=len([VAR2])
and type='p'
and substring(','+[VAR2],number,1)=',')a INNER JOIN (
SELECT id,
[VAR0],
[VAR1],
SUBSTRING([VAR3],number,CHARINDEX(',',[VAR3]+',',number)-number) as [VAR3],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2
from
huang a,master..spt_values
WHERE
number >=1 and number<=len([VAR3])
and type='p'
and substring(','+[VAR3],number,1)=',')b ON a.id=b.id AND a.id2=b.id2 INNER JOIN (
select id,
[VAR0],
[VAR1],
SUBSTRING([VAR7],number,CHARINDEX(',',[VAR7]+',',number)-number) as [VAR7],ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)id2
FROM
huang a,master..spt_values
where
number >=1 and number<=len([VAR7])
and type='p'
and substring(','+[VAR7],number,1)=',')c ON a.id=c.id AND a.id2=c.id2 ----------------结果----------------------------
/*
ID var0 var1 var2 var3 var4 var5 var6 VAR7
----------- ------ ----------- --------------- -------------- ---------- ---------- ---------- --------------------------
1 贵人鸟 603555 10.6 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
1 贵人鸟 603555 10.78 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
1 贵人鸟 603555 10.9 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
2 贵人鸟 603555 10.7 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
2 贵人鸟 603555 10.88 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
2 贵人鸟 603555 11. 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
3 贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
3 贵人鸟 603555 10.98 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
3 贵人鸟 603555 11.1 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
4 贵人鸟 603555 10.5 10.6 2014-01-15 2014-01-14 2014-01-15 24000000
4 贵人鸟 603555 10.68 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
4 贵人鸟 603555 10.8 10.6 2014-01-15 2014-01-14 2014-01-15 23000000
*/