求帮写这条简单的查询SQL语句

Question

有如下的数据CREATE TABLE #tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),fee money,Reason varchar(500)) 
go
insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','279',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
GO想要的数据图如下
就是相同的 standards,amount,variaction,statuss的话只取一条，然后在Sum(fee),多行的Reason累计在一起
注意事项：
这个表是临时表来的，没有主键没有自增ID，如果大家想写 函数 注意函数里面不可以放临时表，还有一点请大家不要用for xml path('')，这个我写过超级慢。辛苦一下各位兄弟再帮我用别的方法写一下吧

Answer 1

CREATE TABLE #tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),fee money,Reason varchar(500)) 
go
insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','279',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
GO
select a.*,b.hobby as Reason from (
  select standards,amount,variation,statuss,SUM(fee)as fee
  from #tb with(nolock) 
  group by standards,amount,variation,statuss
)a left join (
  SELECT B.standards,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
  SELECT standards,(
    SELECT Reason+',' FROM #tb 
    WHERE standards=A.standards 
    FOR XML PATH('')) AS StuList
  FROM #tb A 
  GROUP BY standards
  )B 
)b
on a.standards=b.standardsdrop table #tbstandards                                          amount                                             variation                                          statuss                                            fee                   Reason
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55                                                 279                                                4                                                  物量积压                                               1200.00               加工人员设备不足;T排制作进度较慢;,部件人员不足;,跨间场地积压;图纸问题较多;
56                                                 300                                                4                                                  物量积压                                               1900.00               AAAA;,BBBB;,CCCC;(2 行受影响)

Answer 2

其实用for xml path('')，是最好的解决办法，慢可能是别的原因，而不 是方法，比较是不是因为没有建索引？还有执行过程你有没有使用事务 

Answer 3

你的这个不行，我的意思是说 standards,amount,variaction,statuss相同的时候才合并，你看看下面这条数据就不对了

Answer 4

如果数据量较小，可以考虑xml path(''),如果数据量大，可以考虑函数来合并

Answer 5

CREATE TABLE #tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),fee money,Reason varchar(500)) 
go
insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','2791',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
GO
select a.*,b.hobby as Reason from (
  select standards,amount,variation,statuss,SUM(fee)as fee
  from #tb with(nolock) 
  group by standards,amount,variation,statuss
)a left join (
  SELECT B.standards,amount,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
  SELECT standards,amount,(
    SELECT Reason+'' FROM #tb 
    WHERE standards=A.standards and amount=A.amount
    FOR XML PATH('')) AS StuList
  FROM #tb A 
  GROUP BY standards,amount
  )B 
)b
on a.standards=b.standards and a.amount=b.amountdrop table #tbstandards                                          amount                                             variation                                          statuss                                            fee                   Reason
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55                                                 279                                                4                                                  物量积压                                               800.00                加工人员设备不足;T排制作进度较慢;跨间场地积压;图纸问题较多
55                                                 2791                                               4                                                  物量积压                                               400.00                部件人员不足
56                                                 300                                                4                                                  物量积压                                               1900.00               AAAA;BBBB;CCCC(3 行受影响

Answer 6

确切的来说应该是把你的代码修改成这样SELECT *FROM #tb
go
-------一下是修改的SQL---
select a.*,b.hobby as Reason from (
  select standards,amount,variation,statuss,SUM(fee)as fee
  from #tb with(nolock) 
  group by standards,amount,variation,statuss
)a left join (
  SELECT B.standards,B.amount,B.variation,B.statuss,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
  SELECT standards,amount,variation,statuss,(
    SELECT Reason+',' FROM #tb 
    WHERE standards=A.standards and amount=A.amount and variation=A.variation and statuss=A.statuss
    FOR XML PATH('')) AS StuList
  FROM #tb A 
  GROUP BY standards,amount,variation,statuss
  )B 
)b
on a.standards=b.standards and a.amount=b.amount and a.variation=b.variation and a.statuss=b.statuss

Answer 7

if object_id('Tempdb..#tb') is not null drop table #tb
CREATE TABLE #tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),fee money,Reason varchar(500)) 
go
insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','279',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
GO
 
select standards,amount,variation,statuss,sum(fee) as fee,
stuff(( select ' '+Reason   from #tb z 
where   t.standards=z.standards and t.amount=z.amount 
and t.variation=z.variation and t.statuss=z.statuss
for xml path('')), 1, 1, '')  Reason  
from #tb t
group by t.standards,t.amount,t.variation,t.statuss
--------------------standards                                          amount                                             variation                                          statuss                                            fee                   Reason
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55                                                 279                                                4                                                  物量积压                                               1200.00               加工人员设备不足;T排制作进度较慢; 部件人员不足; 跨间场地积压;图纸问题较多;
56                                                 300                                                4                                                  物量积压                                               1900.00               AAAA; BBBB; CCCC;(2 行受影响)

Answer 8

这要是在oracle中sql应该怎么样写呢？
这sql拿过不不行啊

Answer 9

确切的来说应该是把你的代码修改成这样SELECT *FROM #tb
go
-------一下是修改的SQL---
select a.*,b.hobby as Reason from (
  select standards,amount,variation,statuss,SUM(fee)as fee
  from #tb with(nolock) 
  group by standards,amount,variation,statuss
)a left join (
  SELECT B.standards,B.amount,B.variation,B.statuss,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
  SELECT standards,amount,variation,statuss,(
    SELECT Reason+',' FROM #tb 
    WHERE standards=A.standards and amount=A.amount and variation=A.variation and statuss=A.statuss
    FOR XML PATH('')) AS StuList
  FROM #tb A 
  GROUP BY standards,amount,variation,statuss
  )B 
)b
on a.standards=b.standards and a.amount=b.amount and a.variation=b.variation and a.statuss=b.statuss

用用楼下的这个，这个是最精辟的一个，我那个只是为了一下子写出来

Answer 10

这要是在oracle中sql应该怎么样写呢？
这sql拿过不不行啊http://zhidao.baidu.com/link?url=ooYBpZ7Oc_7Suzn93IqWSpc1OVAPtfDg6P43Uz5cBEtbwtQ21MjqvRFwoehQ6uBkuBNhCbi_uV1xpoQqp6hIza
没玩过啊

Answer 11

这要是在oracle中sql应该怎么样写呢？
这sql拿过不不行啊http://zhidao.baidu.com/link?url=ooYBpZ7Oc_7Suzn93IqWSpc1OVAPtfDg6P43Uz5cBEtbwtQ21MjqvRFwoehQ6uBkuBNhCbi_uV1xpoQqp6hIza
没玩过啊
哦

Answer 12

你要把你的语句写到里面的嘛，你这个第一个go就结束了，空存储过程啊，什么都没有。
create proc proGroup
as
begin
  select 债务人姓名,.....into #tb from (....)
  select .....
  on a.债务人姓名=......
end
go--执行
exec proGroup

Answer 13

还有你最好是这么写，自己改改条件，结果是一样的select standards,amount,variation,statuss,sum(fee) as fee,
stuff(( select ' '+Reason   from #tb z 
where   t.standards=z.standards and t.amount=z.amount 
    and t.variation=z.variation and t.statuss=z.statuss
    for xml path('')), 1, 1, '')  Reason  
from #tb t
group by t.standards,t.amount,t.variation,t.statuss

Answer 14

存储过程的写法,CREATE TABLE #tb
(standards varchar(10), amount varchar(10), variation varchar(10),
 statuss varchar(10),fee money,Reason varchar(500)) insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','279',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
create proc sp_gce
as
begin
 select a.standards,a.amount,a.variation,a.statuss,
        cast((select ''+b.Reason from #tb b 
              where b.standards=a.standards and b.amount=a.amount
              and b.variation=a.variation and b.statuss=a.statuss
              for xml path('')) as varchar(max)) 'Reason'
  from #tb a
  group by a.standards,a.amount,a.variation,a.statuss
end
exec sp_gce/*
standards  amount     variation  statuss    Reason
---------- ---------- ---------- ---------- ----------------------------------------------------------------------
55         279        4          物量积压       加工人员设备不足;T排制作进度较慢;部件人员不足;跨间场地积压;图纸问题较多;
56         300        4          物量积压       AAAA;BBBB;CCCC;(2 row(s) affected)
*/

Answer 15

CREATE TABLE #tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),fee money,Reason varchar(500)) 
go
insert into #tb values('55','279',4,'物量积压',200,'加工人员设备不足;T排制作进度较慢;') 
insert into #tb values('55','279',4,'物量积压',400,'部件人员不足;') 
insert into #tb values('55','279',4,'物量积压',600,'跨间场地积压;图纸问题较多;') 
insert into #tb values('56','300',4,'物量积压',700,'AAAA;') 
insert into #tb values('56','300',4,'物量积压',800,'BBBB;') 
insert into #tb values('56','300',4,'物量积压',400,'CCCC;') 
select a.standards,
a.amount,a.variation,a.statuss,sum(a.fee) as fee,(
select  STUFF((Reason),1,1,'') from #tb b where a.standards=b.standards   for xml path('')  
) as Reason  from #tb a 
group by standards,amount,variation,statuss

Answer 16

简化了下select a.standards,a.amount,
a.amount,a.variation,a.statuss,sum(a.fee) as fee,(
select  STUFF((Reason),1,1,'') from #tb b where a.standards=b.standards and b.amount=a.amount  for xml path('')  
) as Reason  from #tb a 
group by standards,variation,statuss,amount55 279 279 4 物量积压 800.00 工人员设备不足;T排制作进度较慢;间场地积压;图纸问题较多;
55 2791 2791 4 物量积压 400.00 件人员不足;
56 300 300 4 物量积压 1900.00 AAA;BBB;CCC;

Answer 17

不好意思错了一点，这个正确select a.standards,a.amount,
a.amount,a.variation,a.statuss,sum(a.fee) as fee,(
select LTRIM(Reason) from #tb b where a.standards=b.standards and b.amount=a.amount  for xml path('')  
) as Reason  from #tb a 
group by standards,variation,statuss,amount

Answer 18

 
 select standards,amount,variation,statuss,SUM(fee)as fee, wm_concat(Reason)  from 表名   group by standards,amount,variation,statuss
这个是oracle中的 ，用的是逗号隔开的 。