100分求个SQL语句

[yzf86211861]

我现在有个表 字段如下

standards amount variation statuss Reason
1. 55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;
2. 55 279 4 物量积压 部件人员不足;
3. 55 279 4 物量积压 跨间场地积压;图纸问题较多;
4.
5.
不确定多少行,
我想得到如下查询前面几个字段一样最后一列合并成一列，上面那个表也是我通过一段SQL 查出来的不是现成的表，查询语句 如下

         select Z.标准 as standards,
          Z.实际数量 as amount,
          Z.差异 as variation,
          Z.statuss as statuss,
          Y.Reason as Reason
   from 
   (
	   select 55 As 标准,
			SUM(B.SessionNum) As 实际数量,
		   (SUM(B.SessionNum)-55)/55 As 差异,
		   case 
				when (SUM(B.SessionNum)-55)/55>=-1 AND (SUM(B.SessionNum)-55)/55<0.05 then '物量不足'
				when (SUM(B.SessionNum)-55)/55>=0.05 AND (SUM(B.SessionNum)-55)/55<=0.15 then '正常'
				when (SUM(B.SessionNum)-55)/55>0.15 AND (SUM(B.SessionNum)-55)/55<10 then '物量积压'
		   end As statuss 
		from 
		(
			select ShipID,SessionID from ShipProgress_Pretreatment where  
			 (EndDate_Delivery is null or EndDate_Delivery='' or convert(datetime,EndDate_Delivery)<='2010-11-24' )  
			  AND
			 (EndDate_C is null or EndDate_C='' or convert(datetime,EndDate_C)>'2010-11-24' )
		 ) A
		 inner join T_ShipSession B on A.ShipID=B.ShipID AND B.SessionID=A.SessionID
	) Z 
	Left join T_BufferReason Y on Z.statuss=Y.BufferStatus AND Y.BufferType='1'
    

最终效果如下
standards amount variation statuss Reason
55 279 4 物量积压 加工人员设备不足;T排制作进度较慢;部件人员不足;跨间场地积压;图纸问题较多;

[浪子-无悔]

楼主你得描述不是很清晰，看来比较发杂哈。
请教邹建。。。

[whb147]

呵呵，写一个函数即可，就是要小心那一列不能超过8000字，如果超过，就被截断了

[whb147]

如果10分钟后还没有答案，我就来写，嘿嘿

[yzf86211861]

http://topic.csdn.net/u/20101124/16/675a93b7-241c-4d78-89ec-64af1ab0a790.html
这边还有50分写完 谁帮忙写个，一起结贴.

[hch126163]

drop table tb
CREATE TABLE tb
(standards int, amount int, variation int,statuss int,Reason varchar(50),)
insert into tb values(1,55,279,4,'物量积压 加工人员设备不足;T排制作进度较慢;')
insert into tb values(2,55,279,4,' 部件人员不足;')
insert into tb values(3,55,279,4,' 跨间场地积压;图纸问题较多;')

select * from tb


drop function dbo.fn_strSum

CREATE FUNCTION dbo.fn_strSum(@amount int,@variation int,@statuss int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @values varchar(8000)
    SET @values = ''
    SELECT @values = @values + ',' + Reason FROM tb WHERE amount=@amount
and variation=@variation and  statuss=@statuss
    RETURN STUFF(@values, 1, 1, '')
END
GO

-- 调用函数
SELECT amount,variation,statuss, Reason = dbo.fn_strSum(amount,variation,statuss) FROM tb GROUP BY amount,variation,statuss

[hch126163]

--drop table tb
CREATE TABLE tb
(standards int, amount int, variation int,statuss int,Reason varchar(50),)
insert into tb values(1,55,279,4,'物量积压 加工人员设备不足;T排制作进度较慢;')
insert into tb values(2,55,279,4,' 部件人员不足;')
insert into tb values(3,55,279,4,' 跨间场地积压;图纸问题较多;')

select * from tb


--drop function dbo.fn_strSum

CREATE FUNCTION dbo.fn_strSum(@amount int,@variation int,@statuss int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @values varchar(8000)
SET @values = ''
SELECT @values = @values + ',' + Reason FROM tb WHERE amount=@amount
and variation=@variation and statuss=@statuss
RETURN STUFF(@values, 1, 1, '')
END
GO

-- 调用函数
SELECT amount,variation,statuss, Reason = dbo.fn_strSum(amount,variation,statuss) FROM tb GROUP BY amount,variation,statuss

[zjw2004112]

用FOR XML Path 可以搞定你的需求

[浪子-无悔]

呵呵 过来学习了。。。

[whb147]

CREATE TABLE tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),Reason varchar(50))

insert into tb values('55','279','4','物量积压','加工人员设备不足;T排制作进度较慢;')
insert into tb values('55','279','4','物量积压','部件人员不足;')
insert into tb values('55','279','4','物量积压','跨间场地积压;图纸问题较多;')
insert into tb values('56','300','4','物量积压','AAAA;')
insert into tb values('56','300','4','物量积压','BBBB;')
insert into tb values('56','300','4','物量积压','CCCC;')


go

Create   function  [dbo].[F_getReason] (@standards varchar(100),@amount varchar(100),@variation varchar(100),@statuss varchar(100))
returns varchar(8000)
as 

begin
declare @re varchar(300)
set @re =''

select @re = @re +','+Cast(Reason as varchar) 
from (
select standards,amount ,variation ,statuss,Reason from tb ---此处用你查询出来的那个数据表
)a
where a.standards=@standards and a.amount=@amount and a.variation=@variation and a.statuss=@statuss

return (stuff(@re,1,1,''))

end 

go

select distinct standards,amount,variation,statuss,Reason=dbo.F_getReason(standards,amount,variation,statuss) from tb 

drop table tb
drop function  [dbo].[F_getReason]

[whb147]

55 279 4 物量积压 加工人员设备不足;T排制作进度较,部件人员不足;,跨间场地积压;图纸问题较多;
56 300 4 物量积压 AAAA;,BBBB;,CCCC;

[whb147]

楼楼楼上的比我快呀，
早知道看到后我就写了，不该等那10分钟
上面是执行结果
不知道是不是你想要的

[chen_ya_ping]

create table #t
(
ID int not null primary key identity(1,1),
name varchar(100) not null,
products varchar(100) not null
)
insert into #t
values
('ping','car'),
('ping','bike'),
('ling','water')

SELECT  B.name,LEFT(UserList,LEN(UserList)-1) as products FROM (
SELECT name,
    (SELECT products+',' FROM #t WHERE name=A.name ORDER BY name FOR XML PATH('')) AS UserList
FROM #t A 
GROUP BY name
) B

drop table #t

一个小例子，其实网上关于多行合并为一列的还是有很多的资料的。
the result：
name products
ling water
ping car,bike

[deoing]

也列下我写的，好像思路基本都一致：
--DROP TABLE Test
CREATE TABLE Test(
standards INT,
amount INT,
variation INT,
statuss nvarchar(50),
Reason nvarchar(50)
)


CREATE FUNCTION dbo.gkfu_GetReason(
@standards INT,
@amount INT,
@variation INT,
@statuss nvarchar(50)
)
RETURNS NVARCHAR(1000)
BEGIN
DECLARE @reason nvarchar(1000)
SET @Reason = ''
select @reason = @reason + Reason from test where [standards]=@standards AND amount=@amount AND variation=@variation AND statuss=@statuss

RETURN @reason
END
GO


INSERT INTO Test([standards], amount, variation, statuss, Reason)
SELECT 55, 279, 4, '物量积压', '加工人员设备不足;T排制作进度较慢;'
UNION ALL
SELECT 55, 279, 4, '物量积压', '部件人员不足;'
UNION ALL
SELECT 55, 279, 4, '物量积压', '跨间场地积压;图纸问题较多; '


SELECT [standards], amount, variation, statuss, dbo.gkfu_GetReason([standards], amount, variation, statuss) AS Reason
FROM test
group by [standards], amount, variation, statuss

[fendouaj]

你这搞的太复杂了， 要是linq to entities 就更简单了

[jinhaize]

mark一下，留待以后鉴别

[一片冰心在玉壶]

[Quote=引用 19 楼 ch_weni 的回复:]
mark up
[/Quote]
+1

[yzf86211861]

[Quote=引用 24 楼 chen_ya_ping 的回复:]
SQL code

create table #t
(
ID int not null primary key identity(1,1),
name varchar(100) not null,
products varchar(100) not null
)
insert into #t
values
('ping','car'),
('ping','bike'),
(……
[/Quote]
厉害 试了 这个可以。不知道有没别的写法用一句SQL 语句写成

[打一壶酱油]

说实话，.net 版的 sql 水平跟 sql server 版的相差太大，这种程度居然看不懂

[s364021318]

get over

[zhangbin101004]

菜鸟一只路过学习一下。。。。