27,579
社区成员
发帖
与我相关
我的任务
分享
declare @date datetime
set @date = '2011-1-1' --查询的日期
select companyName,sum(total)gdtotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(yy,-1,@date),112))qu_yeartotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(mm,-1,@date),112))shang_monthtotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(4),createdate,112) = convert(varchar(4),@date,112))quan_yeartotal
--上边的全年这里把年数搞错了。
from bs_company t
where convert(varchar(6),createdate,112) = convert(varchar(6),@date,112)
group by companyName
declare @date datetime
set @date = '2011-1-1' --查询的日期
select companyName,sum(total)gdtotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(yy,-1,@date),112))qu_yeartotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(mm,-1,@date),112))shang_monthtotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(4),createdate,112) = convert(varchar(4),datediff(yy,-1,@date),112))quan_yeartotal
from bs_company t
where convert(varchar(6),createdate,112) = convert(varchar(6),@date,112)
group by companyName
/**
*这些可以放在一个查询里的,就是凑数据了,估计你要做报表还是什么的吧!
*你可以写成类似下边的格式:
*/
declare @date datetime
set @date = '2011-1-1' --查询的日期
select companyName,sum(total)gdtotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(yy,-1,@date),112))qu_yeartotal,
(select sum(total) from bs_company
where companyid = t.companyid and convert(varchar(6),createdate,112) = convert(varchar(6),datediff(mm,-1,@date),112))shang_monthtotal
from bs_company t
where convert(varchar(6),createdate,112) = convert(varchar(6),@date,112)
group by companyName
select 公司名,
本月总量=(select sum(量) from tb where date=本月 and 公司名=a.公司名),
去年同期=(select sum(量) from tb where date=去年本月 and 公司名=a.公司名),
上月总量=(select sum(量) from tb where date=add(mm,-1,本月) and 公司名=a.公司名),
全年累计=(select sum(量) from tb where date=全年 and 公司名=a.公司名)
from tb a group by 公司名
----子查询里条件,楼主自己看看,供参考。