select DISTINCT * from (select bm,
(select sl from TR where TR.bm=T.bm and T.bs='R' and month(rq)=1) as r1,
(select sl from TC where TC.bm=T.bm and T.bs='C' and month(rq)=1) as C1,
(select sl from TR where TR.bm=T.bm and T.bs='R' and month(rq)=2) as r2,
(select sl from TC where TC.bm=T.bm and T.bs='C' and month(rq)=2) as C2
from
(select bm,'R' as bs,sl,rq from TR
union
select bm,'C' as bs ,sl,rq from TC) T) TZ
(select sl from TR where TR.bm=T.bm and T.bs='R' and month(rq)=1) as r1,
(select sl from TC where TC.bm=T.bm and T.bs='C' and month(rq)=1) as C1,
(select sl from TR where TR.bm=T.bm and T.bs='R' and month(rq)=2) as r2,
(select sl from TC where TC.bm=T.bm and T.bs='C' and month(rq)=2) as C2
from
(select bm,'R' as bs,sl,rq from TR
union
select bm,'C' as bs ,sl,rq from TC) T) TZ
解决方案 »
- SqlServer触发器怎么用的
- 企业要求开始时间,结束时间在显示和导入的时候年月日分时每个字段一列,是设置两个字段就够了还是分工设置?
- 同时装sql2000与2005后连接怎么写
- 如果int做主键不够用,用bigint应该也可以吧?有没有人用bigint做主键的?应该比guid效率高很多吧?
- 这样的语句怎么才效率最佳?
- 怎么样在SQLSERVER 中用字符串构造一个日期类型????
- 简单的sql合计请热心的高手帮忙,谢谢
- 数据库里有“abc”,我查询的时候输入“abcd”怎样找到有“abc”的记录?
- 如何实现贵人榜的删除和续费
- 弱弱地问:SQL server 2000和SQL server 7有什么区别?
- pState必须关闭么?
- 一台服务器的一个数据库中的表通过存储过程如何写入另一台服务器的一个数据库中的表
sum(case datepart(month,rq) when 2 then sl else 0 end) as r2
from a
group by bm
cross join
select sum(case datepart(month,rq) when 1 then sl else 0 end) as c1,
sum(case datepart(month,rq) when 2 then sl else 0 end) as c2
from b
group by bm
select bm from a group by bm
2:计算入出库
select sum(sl) from a where convert(char,rq,102)>='2005.01.01' and convert(char,rq,102)<=2005.01.31'' and bm=''
select sum(sl) from b where convert(char,rq,102)>='2005.01.01' and convert(char,rq,102)<=2005.01.31'' and bm=''
呵呵。。初学SQL,就我目前的水平只能这样写了。后面得继续将语法复制,粘贴,将日期从1月到12月分类。希望是抛砖引玉。希望高手修改,修改!
select a.id,a.bm,
sum(case when right(rq)=1 then a.sl else 0 end) as r1,
sum(case when right(rq)=1 then b.sl else 0 end) as c1
sum(case when right(rq)=2 then a.sl else 0 end) as r2
sum(case when right(rq)=2 then b.sl else 0 end) as c2
from (select id,bm,sum(sl) as sl ,convert (varchar(6),rq,112)as rq from a group by id,bm,convert (varchar(6),rq,112)) a
all join (select id,bm,sum(sl)as sl ,convert (varchar(6),rq,112) as rq from a group by id,bm,convert (varchar(6),rq,112)) b
on a.id=b.id and a.bm=b.bm and a.rq=b.rq
group by a.id,b.bm
sum(case when right((rq),1)=1 then c.sl else 0 end) as r1,
sum(case when right((rq),1)=1 then d.sl else 0 end) as c1,
sum(case when right((rq),1)=2 then c.sl else 0 end) as r2,
sum(case when right((rq),1)=2 then d.sl else 0 end) as c2
from (select id,bm,sum(sl) as sl ,convert (varchar(6),rq,112) as rq from a group by id,bm,convert (varchar(6),rq,112)) c
join (select id,bm,sum(sl)as sl ,convert (varchar(6),rq,112) as rq from a group by id,bm,convert (varchar(6),rq,112)) d
on c.id=d.id and c.bm=d.bm and c.rq=d.rq
group by c.id,d.bm
(select sum(sl) from TR where TR.bm=T.bm and T.bs='R' and month(rq)=1) as r1,
(select sum(sl) from TC where TC.bm=T.bm and T.bs='C' and month(rq)=1) as C1,
(select sum(sl) from TR where TR.bm=T.bm and T.bs='R' and month(rq)=2) as r2,
(select sum(sl) from TC where TC.bm=T.bm and T.bs='C' and month(rq)=2) as C2
from
(select bm,'R' as bs,sl,rq from TR
union
select bm,'C' as bs ,sl,rq from TC) T) TZ