CREATE procedure he_order
@paths varchar(20),
@Tname varchar(16)
--@columns int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when sname='''+sname+''' then rtrim(amount) else '''' end) as ['+id+']'
from he_productorder_view where pname=@paths
group by sname,id
exec('select pid,breed'+@sql+' into TEMP_TABLE from he_productorder_view where pname='''+@paths+'''
group by pid,breed order by pid asc') --按线路名所订货的品种分组,并按pid排序SELECT A.AMOUNT, B.* FROM (
SELECT Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> - 100)
GROUP BY Breed,PId ORDER BY PId
) A LEFT JOIN TEMP_TABLE B ON A.BREED = B.BREEDDROP TABLE TEMP_TABLEGO
@paths varchar(20),
@Tname varchar(16)
--@columns int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when sname='''+sname+''' then rtrim(amount) else '''' end) as ['+id+']'
from he_productorder_view where pname=@paths
group by sname,id
exec('select pid,breed'+@sql+' into TEMP_TABLE from he_productorder_view where pname='''+@paths+'''
group by pid,breed order by pid asc') --按线路名所订货的品种分组,并按pid排序SELECT A.AMOUNT, B.* FROM (
SELECT Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> - 100)
GROUP BY Breed,PId ORDER BY PId
) A LEFT JOIN TEMP_TABLE B ON A.BREED = B.BREEDDROP TABLE TEMP_TABLEGO
@paths varchar(20),
@Tname varchar(16)
--@columns int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when sname='''+sname+''' then rtrim(amount) else '''' end) as ['+id+']'
from he_productorder_view where pname=@paths
group by sname,id
exec('select pid,breed'+@sql+' into #TABLE from he_productorder_view where pname='''+@paths+'''
group by pid,breed order by pid')
SELECT A.AMOUNT, B.* FROM (
SELECT Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> - 100)
GROUP BY Breed,PId ORDER BY PId
) A LEFT JOIN TEMP_TABLE B ON A.BREED = B.BREED
drop #TABLE
@Tname varchar(16),
@paths varchar(20)
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when sname='''+sname+''' then rtrim(amount) else '''' end) as ['+id+']'
from he_productorder_view where pname=@paths
group by sname,id
exec('select pid,breed'+@sql+' Into Tempbreed from he_productorder_view where pname='''+@paths+'''
group by pid,breed order by pid asc') --按线路名所订货的品种分组,并按pid排序 Select A.*, B.* From
(
SELECT Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> - 100)
GROUP BY Breed,PId) A
Left Join
Tempbreed B
On A.Breed = B.Breed
Order By A.Breed Drop Table Tempbreed
GO
002 30
004 20
006 15
008 10
011 72
013 53
014 28
018 16
023 21
没有把第一个存储过程的结果整合进去,我要的结果是breed amount(这里是合计) 1 2 3 4 5 6 7 .. 25001 50 空 空 3 2 空 3 5 .. 1
002 30 2 1 空 3 1 空 4 .. 空
004 20 3 空 1 2 3 2 空.. 2
006 15 空 空 空 空 空 空 空.. 空(he_order;1结果没有这个品种)
008 10 2 4 空 空 5 空 4 .. 3
010 23 空 2 3 8 6 2 空.. 空
011 72 空 空 空 空 空 空 空.. 空(he_order;1结果没有这个品种)
013 53 7 空 2 3 5 空 2 .. 空
014 28 空 2 7 5 4 3 5 .. 9
018 16 空 空 空 空 空 空 空.. 空(he_order;1结果没有这个品种)
023 21 空 空 空 空 空 空 空.. 空(he_order;1结果没有这个品种)
. .
. .
. .
再帮我想下,好像是前面的次序弄反了,不过我不懂只是猜的,再帮下我吧!谢谢
(
SELECT Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> - 100)
GROUP BY Breed,PId) A
Left Join
Tempbreed B
On A.Breed = B.Breed
Order By A.Breed沒有整合進去,不可能啊。我這裡有關聯語句,不可能還只有兩列的。
@paths varchar(20),
@Tname varchar(16)
--@columns int
as
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when sname='''+sname+''' then rtrim(amount) else '''' end) as ['+id+']'
from he_productorder_view where pname=@paths
group by sname,id
exec('select pid,breed as tmpbreed '+@sql+' into TEMP_TABLE from he_productorder_view where pname='''+@paths+'''
group by pid,breed order by pid asc') --按线路名所订货的品种分组,并按pid排序SELECT A.*,B.* FROM (
SELECT top 200 Breed,sum(amount) as amount FROM He_ProductOrder_view
WHERE (TName = @Tname) and (Amount <> 0)
GROUP BY Breed,PId ORDER BY PId
) A LEFT JOIN TEMP_TABLE B ON A.BREED = B.tmpBREEDDROP TABLE TEMP_TABLE加分咯,以后还请各位多支持,呵呵