34,594
社区成员
发帖
与我相关
我的任务
分享求一个SQL语句或存储过程
SELECT T_DprocStyleNo, T_DprocEchoplexQty, T_DprocMateColor
FROM TPROCESSINFO2
WHERE (T_MProcID = 29)
ORDER BY T_DprocStyleNo, T_DprocMateColor
T_DprocLineID T_DprocStyleNo T_DprocEchoplexQty T_DprocMateColor T_DprocMateSpec
------------- -------------------- --------------------- ----------------- ------------------
1 P04-2135F 635.6300 漂白 143
7 P04-2135F 663.8700 浅兰素色布 143
10 P04-2139F 34.6800 藏青 143
4 P04-2139F 1250.2100 漂白 143
8 P04-2146F 35.0200 藏青 143
9 P04-2146F 27.1300 粉红 143
3 P04-2146F 583.5800 漂白 143
2 P04-2146F 753.2400 漂白 143
6 P04-2150F 826.9800 粉红素色布 143
11 P04-2151F 31.6900 粉兰 143
5 P04-2151F 927.3200 漂白 143
（所影响的行数为 11 行）
以上是从表里查出来的数据。现在想要得到如下结果：即按T_DprocStyleNo统计，然后按颜色统计。想要得到一个这样的报表。
T_DprocStyleNo 漂白 浅兰素色布 藏青 粉红 粉红素色布 粉兰 T_DprocMateSpec 合计
-------------------- ----------------------------------------------------------------------------------
P04-2135F 635.6300 663.8700 143 1299.5
P04-2139F 1250.2100 34.6800 143 1284.89
P04-2146F 1336.82 35.0200 27.1300 143 1398.97
P04-2150F 826.9800 143 826.98
P04-2151F 927.3200 31.6900 143 959.01
合计 4149.98 663.87 69.7 27.13 826.98 31.69
合计先不管的话，在数据库里想要得到如下数据：
T_DprocStyleNo 漂白 浅兰素色布 藏青 粉红 粉红素色布 粉兰 T_DprocMateSpec
-------------------- ----------------------------------------------------------------------------------
P04-2135F 635.6300 663.8700 143
P04-2139F 1250.2100 34.6800 143
P04-2146F 1336.82 35.0200 27.1300 143
P04-2150F 826.9800 143
P04-2151F 927.3200 31.6900 143
可以实现吗？ 各位前辈们，谢谢！
FROM TPROCESSINFO2
WHERE (T_MProcID = 29)
ORDER BY T_DprocStyleNo, T_DprocMateColor
T_DprocLineID T_DprocStyleNo T_DprocEchoplexQty T_DprocMateColor T_DprocMateSpec
------------- -------------------- --------------------- ----------------- ------------------
1 P04-2135F 635.6300 漂白 143
7 P04-2135F 663.8700 浅兰素色布 143
10 P04-2139F 34.6800 藏青 143
4 P04-2139F 1250.2100 漂白 143
8 P04-2146F 35.0200 藏青 143
9 P04-2146F 27.1300 粉红 143
3 P04-2146F 583.5800 漂白 143
2 P04-2146F 753.2400 漂白 143
6 P04-2150F 826.9800 粉红素色布 143
11 P04-2151F 31.6900 粉兰 143
5 P04-2151F 927.3200 漂白 143
（所影响的行数为 11 行）
以上是从表里查出来的数据。现在想要得到如下结果：即按T_DprocStyleNo统计，然后按颜色统计。想要得到一个这样的报表。
T_DprocStyleNo 漂白 浅兰素色布 藏青 粉红 粉红素色布 粉兰 T_DprocMateSpec 合计
-------------------- ----------------------------------------------------------------------------------
P04-2135F 635.6300 663.8700 143 1299.5
P04-2139F 1250.2100 34.6800 143 1284.89
P04-2146F 1336.82 35.0200 27.1300 143 1398.97
P04-2150F 826.9800 143 826.98
P04-2151F 927.3200 31.6900 143 959.01
合计 4149.98 663.87 69.7 27.13 826.98 31.69
合计先不管的话，在数据库里想要得到如下数据：
T_DprocStyleNo 漂白 浅兰素色布 藏青 粉红 粉红素色布 粉兰 T_DprocMateSpec
-------------------- ----------------------------------------------------------------------------------
P04-2135F 635.6300 663.8700 143
P04-2139F 1250.2100 34.6800 143
P04-2146F 1336.82 35.0200 27.1300 143
P04-2150F 826.9800 143
P04-2151F 927.3200 31.6900 143
可以实现吗？ 各位前辈们，谢谢！
...全文
请发表友善的回复…
发表回复
utpcb 2007-12-06
- 打赏
- 举报
大乌龟的那个好
-狙击手- 2007-12-06
- 打赏
- 举报
我上面写的全是动态 的，颜色不固定不要紧的
dawugui 2007-12-06
- 打赏
- 举报
--动态SQL,颜色不固定
declare @sql varchar(8000)
set @sql = 'select t1.* , t2.T_DprocMateSpec
from
(
select isnull(T_DprocStyleNo,''合计'') T_DprocStyleNo'
select @sql = @sql + ' , sum(case T_DprocMateColor when T_DprocMateColor then T_DprocEchoplexQty else 0 end) [' + T_DprocMateColor + ']'
from (select distinct T_DprocMateColor from tb) as a
set @sql = @sql + ' from tb group by T_DprocStyleNo
with rollup
) t1 left join
(
select T_DprocStyleNo , sum(T_DprocMateSpec) T_DprocMateSpec from tb group by T_DprocStyleNo
union all
select T_DprocStyleNo = ''合计'' , sum(T_DprocMateSpec) T_DprocMateSpec from tb
) t2
on t1.T_DprocStyleNo = t2.T_DprocStyleNo'
exec(@sql)
/*
T_DprocStyleNo 藏青 粉红 粉红素色布 粉兰 漂白 浅兰素色布 T_DprocMateSpec
-------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------
P04-2135F 1299.5000 1299.5000 1299.5000 1299.5000 1299.5000 1299.5000 286
P04-2139F 1284.8900 1284.8900 1284.8900 1284.8900 1284.8900 1284.8900 286
P04-2146F 1398.9700 1398.9700 1398.9700 1398.9700 1398.9700 1398.9700 572
P04-2150F 826.9800 826.9800 826.9800 826.9800 826.9800 826.9800 143
P04-2151F 959.0100 959.0100 959.0100 959.0100 959.0100 959.0100 286
合计 5769.3500 5769.3500 5769.3500 5769.3500 5769.3500 5769.3500 1573
*/
drop table tbdawugui 2007-12-06
- 打赏
- 举报
--把两帖一起拷贝过去执行,系统提示我信息太长,只能分两段了.
create table tb(T_DprocLineID int,T_DprocStyleNo varchar(10),T_DprocEchoplexQty numeric(12,4),
T_DprocMateColor nvarchar(10),T_DprocMateSpec int)
insert tb select 1,'P04-2135F',635.6300,'漂白',143
insert tb select 7,'P04-2135F',663.8700,'浅兰素色布',143
insert tb select 10,'P04-2139F',34.6800,'藏青',143
insert tb select 4,'P04-2139F',1250.2100,'漂白',143
insert tb select 8,'P04-2146F', 35.0200,'藏青',143
insert tb select 9,'P04-2146F',27.1300,'粉红',143
insert tb select 3,'P04-2146F',583.5800,'漂白',143
insert tb select 2,'P04-2146F',753.2400,'漂白',143
insert tb select 6,'P04-2150F',826.9800,'粉红素色布',143
insert tb select 11,'P04-2151F',31.6900,'粉兰',143
insert tb select 5,'P04-2151F',927.3200,'漂白',143
go
--静态SQL,颜色固定
select t1.* , t2.T_DprocMateSpec from
(
select isnull(T_DprocStyleNo,'合计') T_DprocStyleNo,
sum(case T_DprocMateColor when '漂白' then T_DprocEchoplexQty else 0 end) 漂白,
sum(case T_DprocMateColor when '浅兰素色布' then T_DprocEchoplexQty else 0 end) 浅兰素色布,
sum(case T_DprocMateColor when '藏青' then T_DprocEchoplexQty else 0 end) 藏青,
sum(case T_DprocMateColor when '粉红' then T_DprocEchoplexQty else 0 end) 粉红,
sum(case T_DprocMateColor when '粉红素色布' then T_DprocEchoplexQty else 0 end) 粉红素色布,
sum(case T_DprocMateColor when '粉兰' then T_DprocEchoplexQty else 0 end) 粉兰
from tb
group by T_DprocStyleNo
with rollup
) t1 left join
(
select T_DprocStyleNo , sum(T_DprocMateSpec) T_DprocMateSpec from tb group by T_DprocStyleNo
union all
select T_DprocStyleNo = '合计' , sum(T_DprocMateSpec) T_DprocMateSpec from tb
) t2
on t1.T_DprocStyleNo = t2.T_DprocStyleNo
/*
T_DprocStyleNo 漂白 浅兰素色布 藏青 粉红 粉红素色布 粉兰 T_DprocMateSpec
-------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------
P04-2135F 635.6300 663.8700 .0000 .0000 .0000 .0000 286
P04-2139F 1250.2100 .0000 34.6800 .0000 .0000 .0000 286
P04-2146F 1336.8200 .0000 35.0200 27.1300 .0000 .0000 572
P04-2150F .0000 .0000 .0000 .0000 826.9800 .0000 143
P04-2151F 927.3200 .0000 .0000 .0000 .0000 31.6900 286
合计 4149.9800 663.8700 69.7000 27.1300 826.9800 31.6900 1573
（所影响的行数为 6 行）
*/
萧霖 2007-12-06
- 打赏
- 举报
没有什么想法了?
萧霖 2007-12-05
- 打赏
- 举报
谢谢大哥大姐高手们。如果T_DprocMateSpec 不是确定的，而是随着T_DprocStyleNo 不同而不同也可以使用吗？如果不可以该如何写啊？先不要考虑合计好了，合计在报表里写。
我在做报表时表头应该怎么取到呢？ 颜色是变化的，不能够写死，是不是要存到临时表再去取呢？
发现SQL功能挺强大的
我在做报表时表头应该怎么取到呢？ 颜色是变化的，不能够写死，是不是要存到临时表再去取呢？
发现SQL功能挺强大的
-狙击手- 2007-12-05
- 打赏
- 举报
不好意思 ，函数 用错了
create table tb(T_DprocLineID int,T_DprocStyleNo varchar(10),T_DprocEchoplexQty numeric(12,4),
T_DprocMateColor nvarchar(10),T_DprocMateSpec int)
insert tb select 1,'P04-2135F',635.6300,'漂白',143
insert tb select 7,'P04-2135F',663.8700,'浅兰素色布',143
insert tb select 10,'P04-2139F',34.6800,'藏青',143
insert tb select 4,'P04-2139F',1250.2100,'漂白',143
insert tb select 8,'P04-2146F', 35.0200,'藏青',143
insert tb select 9,'P04-2146F',27.1300,'粉红',143
insert tb select 3,'P04-2146F',583.5800,'漂白',143
insert tb select 2,'P04-2146F',753.2400,'漂白',143
insert tb select 6,'P04-2150F',826.9800,'粉红素色布',143
insert tb select 11,'P04-2151F',31.6900,'粉兰',143
insert tb select 5,'P04-2151F',927.3200,'漂白',143
go
declare @s nvarchar(4000)
set @s = 'select case when grouping(T_DprocStyleNo) = 1 then ''合计''else T_DprocStyleNo end as T_DprocStyleNo'
select @s = @s + ',['+t_dprocmatecolor+'] = sum(case when t_dprocmatecolor = '''+ t_dprocmatecolor+'''
then T_DprocEchoplexQty else 0 end) '
from (select distinct t_dprocmatecolor from tb)a
set @s = @s + ',max(T_DprocMateSpec) from tb group by T_DprocStyleNo with rollup'
exec (@s)
drop table tb
/*
T_DprocStyleNo 藏青 粉红 粉红素色布 粉兰 漂白 浅兰素色布
-------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -----------
P04-2135F .0000 .0000 .0000 .0000 635.6300 663.8700 143
P04-2139F 34.6800 .0000 .0000 .0000 1250.2100 .0000 143
P04-2146F 35.0200 27.1300 .0000 .0000 1336.8200 .0000 143
P04-2150F .0000 .0000 826.9800 .0000 .0000 .0000 143
P04-2151F .0000 .0000 .0000 31.6900 927.3200 .0000 143
合计 69.7000 27.1300 826.9800 31.6900 4149.9800 663.8700 143
*/ 
中国风 2007-12-05
- 打赏
- 举报
生成的语句如下:
select
[T_DprocStyleNo]=isnull(T_DprocStyleNo,'合计'),
[藏青]=sum(case when T_DprocMateColor='藏青' then T_DprocEchoplexQty else 0 end),
[粉红]=sum(case when T_DprocMateColor='粉红' then T_DprocEchoplexQty else 0 end),
[粉红素色布]=sum(case when T_DprocMateColor='粉红素色布' then T_DprocEchoplexQty else 0 end),
[粉兰]=sum(case when T_DprocMateColor='粉兰' then T_DprocEchoplexQty else 0 end),
[漂白]=sum(case when T_DprocMateColor='漂白' then T_DprocEchoplexQty else 0 end),
[浅兰素色布]=sum(case when T_DprocMateColor='浅兰素色布' then T_DprocEchoplexQty else 0 end),
T_DprocMateSpec
from
#
group by
T_DprocStyleNo,T_DprocMateSpec with rollup
having not (grouping(T_DprocMateSpec)=1 and grouping(T_DprocStyleNo)=0)
-狙击手- 2007-12-05
- 打赏
- 举报
create table tb(T_DprocLineID int,T_DprocStyleNo varchar(10),T_DprocEchoplexQty numeric(12,4),
T_DprocMateColor nvarchar(10),T_DprocMateSpec int)
insert tb select 1,'P04-2135F',635.6300,'漂白',143
insert tb select 7,'P04-2135F',663.8700,'浅兰素色布',143
insert tb select 10,'P04-2139F',34.6800,'藏青',143
insert tb select 4,'P04-2139F',1250.2100,'漂白',143
insert tb select 8,'P04-2146F', 35.0200,'藏青',143
insert tb select 9,'P04-2146F',27.1300,'粉红',143
insert tb select 3,'P04-2146F',583.5800,'漂白',143
insert tb select 2,'P04-2146F',753.2400,'漂白',143
insert tb select 6,'P04-2150F',826.9800,'粉红素色布',143
insert tb select 11,'P04-2151F',31.6900,'粉兰',143
insert tb select 5,'P04-2151F',927.3200,'漂白',143
go
declare @s nvarchar(4000)
set @s = 'select case when grouping(T_DprocStyleNo) = 1 then ''合计''else T_DprocStyleNo end as T_DprocStyleNo'
select @s = @s + ',['+t_dprocmatecolor+'] = max(case when t_dprocmatecolor = '''+ t_dprocmatecolor+'''
then T_DprocEchoplexQty else 0 end) '
from (select distinct t_dprocmatecolor from tb )a
set @s = @s + ',max(T_DprocMateSpec) from tb group by T_DprocStyleNo with rollup'
exec (@s)
drop table tb
/*
T_DprocStyleNo 藏青 粉红 粉红素色布 粉兰 漂白 浅兰素色布
-------------- -------------- -------------- -------------- -------------- -------------- -------------- -----------
P04-2135F .0000 .0000 .0000 .0000 635.6300 663.8700 143
P04-2139F 34.6800 .0000 .0000 .0000 1250.2100 .0000 143
P04-2146F 35.0200 27.1300 .0000 .0000 753.2400 .0000 143
P04-2150F .0000 .0000 826.9800 .0000 .0000 .0000 143
P04-2151F .0000 .0000 .0000 31.6900 927.3200 .0000 143
合计 35.0200 27.1300 826.9800 31.6900 1250.2100 663.8700 143
*/ 中国风 2007-12-05
- 打赏
- 举报
use test
go
create table #(T_DprocLineID int,
T_DprocStyleNo nvarchar(10),
T_DprocEchoplexQty numeric(18,4),
T_DprocMateColor nvarchar(10),
T_DprocMateSpec int)
insert # select 1, 'P04-2135F', 635.6300, '漂白', 143
insert # select 7, 'P04-2135F', 663.8700, '浅兰素色布', 143
insert # select 10, 'P04-2139F', 34.6800, '藏青', 143
insert # select 4, 'P04-2139F', 1250.2100, '漂白', 143
insert # select 8, 'P04-2146F', 35.0200, '藏青', 143
insert # select 9, 'P04-2146F', 27.1300, '粉红', 143
insert # select 3, 'P04-2146F', 583.5800, '漂白', 143
insert # select 2, 'P04-2146F', 753.2400, '漂白', 143
insert # select 6, 'P04-2150F', 826.9800, '粉红素色布', 143
insert # select 11, 'P04-2151F', 31.6900, '粉兰', 143
insert # select 5, 'P04-2151F', 927.3200, '漂白', 143
go
declare @s nvarchar(4000)
select @s='select [T_DprocStyleNo]=isnull(T_DprocStyleNo,''合计'')'
select @s=@s+','+quotename(T_DprocMateColor)+'=sum(case when T_DprocMateColor=' +quotename(T_DprocMateColor,'''')+' then T_DprocEchoplexQty else 0 end)'
from
#
group by T_DprocMateColor
exec( @s+',T_DprocMateSpec from # group by T_DprocStyleNo,T_DprocMateSpec with rollup
having not (grouping(T_DprocMateSpec)=1 and grouping(T_DprocStyleNo)=0)')
T_DprocStyleNo 藏青 粉红 粉红素色布 粉兰 漂白 浅兰素色布 T_DprocMateSpec
-------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------
P04-2135F .0000 .0000 .0000 .0000 635.6300 663.8700 143
P04-2139F 34.6800 .0000 .0000 .0000 1250.2100 .0000 143
P04-2146F 35.0200 27.1300 .0000 .0000 1336.8200 .0000 143
P04-2150F .0000 .0000 826.9800 .0000 .0000 .0000 143
P04-2151F .0000 .0000 .0000 31.6900 927.3200 .0000 143
合计 69.7000 27.1300 826.9800 31.6900 4149.9800 663.8700 NULL
-狙击手- 2007-12-05
- 打赏
- 举报
create table tb(T_DprocLineID int,T_DprocStyleNo varchar(10),T_DprocEchoplexQty numeric(12,4),
T_DprocMateColor nvarchar(10),T_DprocMateSpec int)
insert tb select 1,'P04-2135F',635.6300,'漂白',143
insert tb select 7,'P04-2135F',663.8700,'浅兰素色布',143
insert tb select 10,'P04-2139F',34.6800,'藏青',143
insert tb select 4,'P04-2139F',1250.2100,'漂白',143
insert tb select 8,'P04-2146F', 35.0200,'藏青',143
insert tb select 9,'P04-2146F',27.1300,'粉红',143
insert tb select 3,'P04-2146F',583.5800,'漂白',143
insert tb select 2,'P04-2146F',753.2400,'漂白',143
insert tb select 6,'P04-2150F',826.9800,'粉红素色布',143
insert tb select 11,'P04-2151F',31.6900,'粉兰',143
insert tb select 5,'P04-2151F',927.3200,'漂白',143
go
declare @s nvarchar(4000)
set @s = 'select T_DprocStyleNo'
select @s = @s + ',['+t_dprocmatecolor+'] = max(case when t_dprocmatecolor = '''+ t_dprocmatecolor+'''
then T_DprocEchoplexQty else 0 end) '
from (select distinct t_dprocmatecolor from tb )a
set @s = @s + ',T_DprocMateSpec from tb group by T_DprocStyleNo,T_DprocMateSpec'
exec (@s)
drop table tb
/*
T_DprocStyleNo 藏青 粉红 粉红素色布 粉兰 漂白 浅兰素色布 T_DprocMateSpec
-------------- -------------- -------------- -------------- -------------- -------------- -------------- ---------------
P04-2135F .0000 .0000 .0000 .0000 635.6300 663.8700 143
P04-2139F 34.6800 .0000 .0000 .0000 1250.2100 .0000 143
P04-2146F 35.0200 27.1300 .0000 .0000 753.2400 .0000 143
P04-2150F .0000 .0000 826.9800 .0000 .0000 .0000 143
P04-2151F .0000 .0000 .0000 31.6900 927.3200 .0000 143
*/ -狙击手- 2007-12-05
- 打赏
- 举报
行列转换加合计
表test结构：
店面 产品 金额
---- ----- ----
A店 产品1 100
A店 产品2 100
A店 产品3 100
B店 产品1 200
B店 产品3 200
……
---------------------------
要求的结果是：
店面名称 产品1 产品2 产品3 …… 总计
A 100 100 100 …… 300
B 200 0 200 …… 400
这里产品数量不是固定的，店面也不是固定的
create table test(店面 varchar(10),产品 varchar(10),金额 int)
insert into test select 'A店','产品1',100
insert into test select 'A店','产品2',100
insert into test select 'A店','产品3',100
insert into test select 'B店','产品1',200
insert into test select 'B店','产品3',200
go
declare @sql varchar(8000)
set @sql = 'select 店面'
select @sql = @sql + ',' + 产品 + '=sum(case 产品 when ''' + 产品 + ''' then 金额 else 0 end)'
from test group by 产品 order by 产品
set @sql = @sql + ',总计 = sum(金额) from test group by 店面'
exec(@sql)
go
drop table test
店面 产品1 产品2 产品3 总计
---- ----- ----- ----- -----------
A店 100 100 100 300
B店 200 0 200 400-狙击手- 2007-12-05
- 打赏
- 举报
基本的行列转换呀
