select t1.partname,t1.type,price1,price2,price3
from
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end) t1
inner join
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end where left(convert(varchar,paydate,112),6)=left(convert(varchar,getdate(),112),6)) t2
on t1.partname=t2.partname and t1.type=t2.type
inner join
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end where partname<left(convert(varchar,dateadd(mm,1,getdate()),112),6)+'01') t3
on t1.partname=t3.partname and t1.type=t3.type
union
select s1.partname,s1.type,price1,price2,price3
from
(select partname,'' type,sum(price) price1 from table1 group by partname) s1
inner join
(select partname,'' type,sum(price) price1 from table1 group by partname where left(convert(varchar,paydate,112),6)=left(convert(varchar,getdate(),112),6)) s2
on s1.partname=s2.partname
inner join
(select partname,'' type,sum(price) price1 from table1 group by partname where partname<left(convert(varchar,dateadd(mm,1,getdate()),112),6)+'01') s3
on s1.partname=s3.partname
from
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end) t1
inner join
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end where left(convert(varchar,paydate,112),6)=left(convert(varchar,getdate(),112),6)) t2
on t1.partname=t2.partname and t1.type=t2.type
inner join
(select partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end type,sum(price) price1 from table1 group by partname,case type when 1 then '1' when 2 then '2/3' when 3 then '2/3 else 'others' end where partname<left(convert(varchar,dateadd(mm,1,getdate()),112),6)+'01') t3
on t1.partname=t3.partname and t1.type=t3.type
union
select s1.partname,s1.type,price1,price2,price3
from
(select partname,'' type,sum(price) price1 from table1 group by partname) s1
inner join
(select partname,'' type,sum(price) price1 from table1 group by partname where left(convert(varchar,paydate,112),6)=left(convert(varchar,getdate(),112),6)) s2
on s1.partname=s2.partname
inner join
(select partname,'' type,sum(price) price1 from table1 group by partname where partname<left(convert(varchar,dateadd(mm,1,getdate()),112),6)+'01') s3
on s1.partname=s3.partname
解决方案 »
- 请教两表间同步更新的问题
- 刚用linq不久,想问问有没有性能测试的工具,我想比较一下不同的linq语句的速度差异有多大~~
- 怎样用exec命令执行带返回值的存储参数阿?
- 如何在XP或98下安装SQL2k的服务器程序?
- 奇怪的查询合计累加问题
- 大给分,请教数据库教程的资料!!!!
- 请问SQL里用什么语句得到一个表的结构?
- 开发街道家庭人员信息管理系统时建库时遇到的问题
- 视图里不能用order by吗,那如果要让视图排序的话怎么办
- 逃离狐狸精
- 怎样将一幅不是很大的bmp图片放进数据库中
- 请问什么原因导致“System.Data.SqlClient.SqlException: 将截断字符串或二进制数据。”异常??谢谢!!
上述t3,s3中的price1应为price3;
你的题目也好难看懂!
上述t3,s3中的price1应为price3;
就是用CASE把TYPE分组如果是1分到第一组,2、3分到第二组,其他时候分到第三组
然后GROUP BY这个分组
举个例子
SELECT NEWCOL,SUM(CASE WHEN paydate IS 当前月份 THEN PRICE),。
FROM TABLE1
GROUP BY CASE TYPE WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 2
ELSE 3
END
hillhx(曾经的曾经)
各50分