Select 台帐编号,煤炭品种,存储方式,sum(库存数量) as 库存数量
from 表名
group by 台帐编号,煤炭品种,存储方式
from 表名
group by 台帐编号,煤炭品种,存储方式
解决方案 »
- 关于 sql server 2008 报表设计器,针式打印机的应用
- SQL能自動提示:存放數據文件和日志文件的硬盤空間已滿嗎?
- 一个实用,但棘手的SQL语句
- 求各部门前20会员的销售额
- 求一句比较有挑战性的Sql语句或者存储过程...希望各位大哥帮下忙,在线等.谢谢
- 请教:急!!!!!!!
- 操作系统是windows xp home版,可以安装sql server 2000做数据库服务器用吗?
- 请问sql server 7.0能在windows 2000 profession下运行么?
- sql2000 如何存储空间数据(mapinfor)!!!!!!!!!急
- 求助, sql2014代理服务无法启动,求大佬帮帮忙
- 关于储存过程奇怪的问题
- 急!!! 不同数据库间 不同表结构的表之间的数据传输问题,请各位指点
from 表
group by 台帐编号,煤炭品种,存储方式
insert into #t1 values('C020120040101','01','L010102','1',1000)
insert into #t1 values('C020120040101','01','L010103','1',1000)
insert into #t1 values('C020120040101','01','L010201','1',1000)
insert into #t1 values('C020120040101','01','L010203','1',1000)
insert into #t1 values('C020120040101','01','L020201','1',1000)
insert into #t1 values('C020120040101','01','L020201','2',1000)
insert into #t1 values('C020120040101','02','L010101','1',900)
insert into #t1 values('C020120040101','02','L010102','1',900)
insert into #t1 values('C020120040101','02','L010103','1',900)
insert into #t1 values('C020120040101','02','L010201','1',900)
insert into #t1 values('C020120040101','02','L010203','1',900)
insert into #t1 values('C020120040101','02','L020201','1',900)
insert into #t1 values('C020120040101','02','L020201','2',900)
insert into #t1 values('C020120040101','03','L010101','1',800)
insert into #t1 values('C020120040101','03','L010102','1',800)
insert into #t1 values('C020120040101','03','L010103','1',800)
insert into #t1 values('C020120040101','03','L010201','1',800)
insert into #t1 values('C020120040101','03','L010203','1',800)
insert into #t1 values('C020120040101','03','L020201','1',800)
insert into #t1 values('C020120040101','03','L020201','2',800)
declare @str nvarchar(4000)
select @str=''
select @str=@str+',[煤炭品种为('+煤炭品种+'+存储方式为)(1)]=isnull((select sum(1) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''1''),0)'
+',[煤炭品种为('+煤炭品种+'+存储方式为)(2)]=isnull((select sum(1) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''2''),0)'
from (select distinct 煤炭品种 from #t1) a
select @str='select 台帐编号'+@str+' from #t1 group by 台帐编号 '
exec( @str)
drop table #t1--测试结果
--C020120040101 3 0 3 0 3 0 3 0 3 0 3 3
是求库存数量,修改一下。
select @str=''
select @str=@str+',[煤炭品种为('+煤炭品种+'+存储方式为)(1)]=isnull((select sum(库存数量) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''1''),0)'
+',[煤炭品种为('+煤炭品种+'+存储方式为)(2)]=isnull((select sum(1) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''2''),0)'
from (select distinct 煤炭品种 from #t1) a
select @str='select 台帐编号'+@str+' from #t1 group by 台帐编号 '
exec( @str)
insert into #t1 values('C020120040101','01','L010102','1',1000)
insert into #t1 values('C020120040101','01','L010103','1',1000)
insert into #t1 values('C020120040101','01','L010201','1',1000)
insert into #t1 values('C020120040101','01','L010203','1',1000)
insert into #t1 values('C020120040101','01','L020201','1',1000)
insert into #t1 values('C020120040101','01','L020201','2',1000)
insert into #t1 values('C020120040101','02','L010101','1',900)
insert into #t1 values('C020120040101','02','L010102','1',900)
insert into #t1 values('C020120040101','02','L010103','1',900)
insert into #t1 values('C020120040101','02','L010201','1',900)
insert into #t1 values('C020120040101','02','L010203','1',900)
insert into #t1 values('C020120040101','02','L020201','1',900)
insert into #t1 values('C020120040101','02','L020201','2',900)
insert into #t1 values('C020120040101','03','L010101','1',800)
insert into #t1 values('C020120040101','03','L010102','1',800)
insert into #t1 values('C020120040101','03','L010103','1',800)
insert into #t1 values('C020120040101','03','L010201','1',800)
insert into #t1 values('C020120040101','03','L010203','1',800)
insert into #t1 values('C020120040101','03','L020201','1',800)
insert into #t1 values('C020120040101','03','L020201','2',800)
insert into #t1 values('C020120040102','03','L020201','1',800)
insert into #t1 values('C020120040102','03','L020202','2',800)
insert into #t1 values('C020120040102','03','L020203','1',800)
insert into #t1 values('C020120040102','03','L020201','2',800)
insert into #t1 values('C020120040102','03','L020202','1',800)
insert into #t1 values('C020120040102','03','L020203','2',800)
insert into #t1 values('C020120040102','03','L020201','1',800)declare @str nvarchar(4000)
select @str=''
select @str=@str+',[煤炭品种为('+煤炭品种+'+存储方式为)(1)]=isnull((select sum(库存数量) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''1'' and 台帐编号=b.台帐编号),0)'
+',[煤炭品种为('+煤炭品种+'+存储方式为)(2)]=isnull((select sum(库存数量) from #t1 where 煤炭品种='''+煤炭品种+''' and 存储方式=''2'' and 台帐编号=b.台帐编号),0)'
from (select distinct 煤炭品种 from #t1) a
select @str='select 台帐编号'+@str+' from #t1 b group by 台帐编号 'exec( @str)drop table #t1
接分!