sql汇总问题 能不能用Tab把你的数据对齐一下,哪是哪啊? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 请问各位高手,有如下表 code | name | month | inquantity | inprice | outquqntity |outprice 001 |a | 0 | 10 | 12 | | 002 |b | 0 | 10 | 20 | | 003 |c | 0 | 5 | 10 | |001 |a | 1 | 20 | 40 | |001 |a | 1 | | | 3 | 3.6 002 |b | 1 | 2 | 4 | | 003 |c | 2 | | | 2 | 4 004 |d | 2 | 14 | 28 | |004 |d | 3 | | | 3 | 6 其中month为月份,0月份即 是期初 要求生成如下表 code |qcqua(数量)|qcpr(金额)|nquantity(入数)|npr(金额)|outqu(出数)|price(金额| kcqu(存数)| kcpr(金额) 001 | 10 | 12 | 20 | 40 | 3 | 3.6 | 27 | 56.4 002 | 10 | 20 | 2 | 4 | | | 12 | 24003 | 5 | 10 | | | 2 | 4 | 3 | 6 004 | | | 14 | 28 | 3 | 6 | 11 | 22不好意思,第一次提问,打上去时是对齐的,发上去却变了这是原某财务软件导出的格式,现本人重新做WEB版了,不知如何做。 declare @t table(code varchar(6),name varchar(6),month int,inquantity int,inprice money,outquqntity int,outprice money)insert into @t select '001','a',0,10 ,12 ,null,nullinsert into @t select '002','b',0,10 ,20 ,null,nullinsert into @t select '003','c',0,5 ,10 ,null,nullinsert into @t select '001','a',1,20 ,40 ,null,nullinsert into @t select '001','a',1,null,null, 3 ,3.6 insert into @t select '002','b',1,2 ,4 ,null,nullinsert into @t select '003','c',2,null,null,2 ,4insert into @t select '004','d',2,14 ,28 ,null,nullinsert into @t select '004','d',3,null,null,3 ,6 select code, sum(case month when 0 then inquantity else 0 end), sum(case month when 0 then inprice else 0 end), sum(case month when 0 then 0 else inquantity end), sum(case month when 0 then 0 else inprice end), sum(case month when 0 then 0 else outquqntity end), sum(case month when 0 then 0 else outprice end), sum(isnull(inquantity,0)-isnull(outquqntity,0)), sum(isnull(inprice,0)-isnull(outprice,0))from @tgroup by code declare @t table(code varchar(6),name varchar(6),month int,inquantity int,inprice money,outquqntity int,outprice money)insert into @t select '001','a',0,10 ,12 ,null,nullinsert into @t select '002','b',0,10 ,20 ,null,nullinsert into @t select '003','c',0,5 ,10 ,null,nullinsert into @t select '001','a',1,20 ,40 ,null,nullinsert into @t select '001','a',1,null,null, 3 ,3.6 insert into @t select '002','b',1,2 ,4 ,null,nullinsert into @t select '003','c',2,null,null,2 ,4insert into @t select '004','d',2,14 ,28 ,null,nullinsert into @t select '004','d',3,null,null,3 ,6 select code, sum(case month when 0 then inquantity else 0 end) as qcqua, sum(case month when 0 then inprice else 0 end) as qcpr, sum(case month when 0 then 0 else inquantity end) as nquantity, sum(case month when 0 then 0 else inprice end) as npr, sum(case month when 0 then 0 else outquqntity end) as outqu, sum(case month when 0 then 0 else outprice end) as price, sum(isnull(inquantity,0)-isnull(outquqntity,0)) as kcqu, sum(isnull(inprice,0)-isnull(outprice,0)) as kcprfrom @tgroup by code/*code qcqua qcpr nquantity npr outqu price kcqu kcpr ------ ----------- --------------------- ----------- --------------------- ----------- --------------------- ----------- --------------------- 001 10 12.0000 20 40.0000 3 3.6000 27 48.4000002 10 20.0000 2 4.0000 0 .0000 12 24.0000003 5 10.0000 0 .0000 2 4.0000 3 6.0000004 0 .0000 14 28.0000 3 6.0000 11 22.0000*/ 20分在线等一查询统计语句 insert + condition 求一sql语句 哪位兄弟机器上装有SQLSERVER2005或2008,帮我一个忙。是一个数据仓库的问题。具体请进 数型表的编码及计算问题 bcp out 嵌套进 for 时候的 碰到斜杠 sql2005主键问题 attach两个数据库文件总是出错,急!急!急!在线等待? 动态查询的思考 为什么我的SQL SERVER数据库不能进行备份操作? 请教更新数据问题 求助select SUM后面的时间条件写法
请问各位高手,有如下表
code | name | month | inquantity | inprice | outquqntity |outprice
001 |a | 0 | 10 | 12 | |
002 |b | 0 | 10 | 20 | |
003 |c | 0 | 5 | 10 | |
001 |a | 1 | 20 | 40 | |
001 |a | 1 | | | 3 | 3.6
002 |b | 1 | 2 | 4 | |
003 |c | 2 | | | 2 | 4
004 |d | 2 | 14 | 28 | |
004 |d | 3 | | | 3 | 6
其中month为月份,0月份即 是期初
要求生成如下表
code |qcqua(数量)|qcpr(金额)|nquantity(入数)|npr(金额)|outqu(出数)|price(金额| kcqu(存数)| kcpr(金额)
001 | 10 | 12 | 20 | 40 | 3 | 3.6 | 27 | 56.4
002 | 10 | 20 | 2 | 4 | | | 12 | 24
003 | 5 | 10 | | | 2 | 4 | 3 | 6
004 | | | 14 | 28 | 3 | 6 | 11 | 22
不好意思,第一次提问,打上去时是对齐的,发上去却变了
这是原某财务软件导出的格式,现本人重新做WEB版了,不知如何做。
declare @t table(code varchar(6),name varchar(6),month int,inquantity int,inprice money,outquqntity int,outprice money)
insert into @t select '001','a',0,10 ,12 ,null,null
insert into @t select '002','b',0,10 ,20 ,null,null
insert into @t select '003','c',0,5 ,10 ,null,null
insert into @t select '001','a',1,20 ,40 ,null,null
insert into @t select '001','a',1,null,null, 3 ,3.6
insert into @t select '002','b',1,2 ,4 ,null,null
insert into @t select '003','c',2,null,null,2 ,4
insert into @t select '004','d',2,14 ,28 ,null,null
insert into @t select '004','d',3,null,null,3 ,6 select
code,
sum(case month when 0 then inquantity else 0 end),
sum(case month when 0 then inprice else 0 end),
sum(case month when 0 then 0 else inquantity end),
sum(case month when 0 then 0 else inprice end),
sum(case month when 0 then 0 else outquqntity end),
sum(case month when 0 then 0 else outprice end),
sum(isnull(inquantity,0)-isnull(outquqntity,0)),
sum(isnull(inprice,0)-isnull(outprice,0))
from
@t
group by
code
declare @t table(code varchar(6),name varchar(6),month int,inquantity int,inprice money,outquqntity int,outprice money)
insert into @t select '001','a',0,10 ,12 ,null,null
insert into @t select '002','b',0,10 ,20 ,null,null
insert into @t select '003','c',0,5 ,10 ,null,null
insert into @t select '001','a',1,20 ,40 ,null,null
insert into @t select '001','a',1,null,null, 3 ,3.6
insert into @t select '002','b',1,2 ,4 ,null,null
insert into @t select '003','c',2,null,null,2 ,4
insert into @t select '004','d',2,14 ,28 ,null,null
insert into @t select '004','d',3,null,null,3 ,6 select
code,
sum(case month when 0 then inquantity else 0 end) as qcqua,
sum(case month when 0 then inprice else 0 end) as qcpr,
sum(case month when 0 then 0 else inquantity end) as nquantity,
sum(case month when 0 then 0 else inprice end) as npr,
sum(case month when 0 then 0 else outquqntity end) as outqu,
sum(case month when 0 then 0 else outprice end) as price,
sum(isnull(inquantity,0)-isnull(outquqntity,0)) as kcqu,
sum(isnull(inprice,0)-isnull(outprice,0)) as kcpr
from
@t
group by
code/*
code qcqua qcpr nquantity npr outqu price kcqu kcpr
------ ----------- --------------------- ----------- --------------------- ----------- --------------------- ----------- ---------------------
001 10 12.0000 20 40.0000 3 3.6000 27 48.4000
002 10 20.0000 2 4.0000 0 .0000 12 24.0000
003 5 10.0000 0 .0000 2 4.0000 3 6.0000
004 0 .0000 14 28.0000 3 6.0000 11 22.0000
*/