首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

sql ,一定要弄清楚这个有关问题。每次这里卡死。

2013-11-13 
sql 求助,一定要弄清楚这个问题。。每次这里卡死。。里面有测试表和数据,我不得不提问,大神们知道下,怎么解决

sql 求助,一定要弄清楚这个问题。。每次这里卡死。。
里面有测试表和数据,我不得不提问,大神们知道下,怎么解决这个老要我聚合rn的方法,不是一,二次碰到了。。


CREATE TABLE #a(ID INT,name VARCHAR(200),pass VARCHAR(200),remark VARCHAR(200))  
INSERT INTO #a
SELECT 1,'1','1','AB'
UNION ALL
SELECT 2,'1','3','BCD'
 
CREATE TABLE #b(ID INT,fieldvalue VARCHAR(200),fieldremark VARCHAR(200))  
INSERT INTO #b
SELECT 1,'A','备注1'
UNION ALL
SELECT 2,'B','备注2'
UNION ALL
SELECT 3,'C','备注3'
UNION ALL
SELECT 4,'D','备注4'
select * from #a a



;with ceb as
(
select a.ID,a.name,a.pass,substring(a.remark,number+1,1) as fieldvalue from #a a 
left join
(
select number from master..spt_values t   where t.type='p' ) b
on  (number+1)<=len(a.remark) 
)
,ceb2 as
(
select ROW_NUMBER() over(order by getdate()) rn,a.ID,a.name,a.pass,b.fieldremark from ceb a,#b b where a.fieldvalue=b.fieldvalue
)

select ID, name, pass, 
stuff((select ','+b.fieldremark from ceb2 b where a.rn=b.rn   for xml path('')),1,1,'') as fieldremark
from ceb2 a  group by ID,name,pass

sql
[解决办法]
不聚合是可以啊

;with ceb as
(
select a.ID,a.name,a.pass,substring(a.remark,number+1,1) as fieldvalue from #a a 
left join
(
select number from master..spt_values t   where t.type='p' ) b
on  (number+1)<=len(a.remark) 
),ceb2 as
(
select a.ID,a.name,a.pass,b.fieldremark from ceb a,#b b where a.fieldvalue=b.fieldvalue
)
select a.id,a.NAME,a.pass,
stuff((select ','+fieldremark from ceb2 b 
       where b.id=a.id and b.name=a.NAME AND a.pass=b.pass 
       for xml path('')),1,1,'') 'fieldremark'
from ceb2 a
group by  a.id,a.NAME,a.pass
/*
id          NAME                                                                                                                                                                                                     pass                                                                                                                                                                                                     fieldremark
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


1           1                                                                                                                                                                                                        1                                                                                                                                                                                                        备注1,备注2
2           1                                                                                                                                                                                                        3                                                                                                                                                                                                        备注2,备注3,备注4

*/

热点排行