解决方案 »
- 求SQL語句
- sqlserver2008导出到excels bcp出错
- 几个字段必须读出,另几个字段根据情况读出,怎么实现?我快崩溃了
- 在数据库'Bb12345_db'中拒绝了SETUSER权限。
- 把当前表SELECT出来的数据,变动一个字段,再插入当前表,怎么写??
- 奇怪,零條記錄仍占用5G空間,而且是在使用狀態,無法用收縮去解決.
- 100分送DB2于SMS问题。~~~~~~~~~~
- 请问在98+个人版的sql2000下的数据库,以后能转移到W2K ADV +sql专业版上吗?
- 关于sqlserver2000客户端与服务端连接你们都会的
- SQL查询根据多字段重复项查询结果只以ID最新(大)保留一个条记录
- SQL数据库,创建表的问题,非常着急
- 没学过数据库基础,求解一个字段数据类型的基础问题,谢谢
when yf=10 then '十月收入'
else '十一月收入'
end as yfsr,
SUM(xyf) as xyf,
SUM(cwf) as cwf,
SUM(bgyf) as bgyf
from (
select
*,
case when cast(right([date],2) as int)<=20 then cast(right([date],2) as int) else cast(right([date],2) as int)+1 end as yf
from tablename
) as a where yf in (9,10,11)
when yf=10 then '十月收入'
else '十一月收入'
end as yfsr,
SUM(xyf) as xyf,
SUM(cwf) as cwf,
SUM(bgyf) as bgyf
from (
select
*,
case when cast(right([date],2) as int)<=20 then cast(right([date],2) as int) else cast(right([date],2) as int)+1 end as yf
from tablename
) as a where yf in (9,10,11) group by yf
with cte as
(select case when DATE>=20140821 and DATE<20140921 then '九月收入'
when DATE>=20140921 and DATE<20141021 then '十月收入'
when DATE>=20141021 and DATE<20141121 then '十月收入'
end as date
,xyf,cwf,bgyf from tablename )
select date,SUM(xyf) as xyf,SUM(cwf)as cwf, SUM(bgyf)as bgyf
from cte
group by date /code]
from (
select *,
case when cast(right([date],2) as int)<=20 then cast(substring([date],3,4)
when cast(right([date],2) as int)>20 and cast(substring([date],5,2) as int)!=12 then cast(substring([date],3,4) as int)+1
else cast(cast(substring([date],3,2)+1 as varchar(2))+'01' as int) end as ny
from tablename ) as a group by ny
with cte as
(select case when right(DATE,2)>=21 then DATEADD(MONTH,1,date)
else date end as date
,xyf,cwf,bgyf from tablename )
select date,SUM(xyf) as xyf,SUM(cwf)as cwf, SUM(bgyf)as bgyf
from cte
group by LEFT(date,4)试试这个。 判断DATE列后2位。如果是大于20,把月份加1,(用dateadd加1 就不会出现13月份的问题了。)
然后在对LEFT(date,4)进行分组。