3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
老手请进，帮忙解决，分数不够可以再送（在线）
比如有一张表A,有以下几个字段
定单号：A_ord
定单日期：A_date
定单量：A_mnt
然后用一条SQL语句，按某一年十二个月统计出各月的定单量，格式如下：
月 份： 200301 200302 200303 ......
定单量： ... ... ... ......
定单号：A_ord
定单日期：A_date
定单量：A_mnt
然后用一条SQL语句，按某一年十二个月统计出各月的定单量，格式如下：
月 份： 200301 200302 200303 ......
定单量： ... ... ... ......
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
pengdali 2003-01-15
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case convert(char(6),A_date,112) when '''+[temp]+'''
then a_mnt else 0 end) as '''+[temp]+''','
from (select distinct convert(char(6),A_date,112) [temp] from 表A where year(A_date)=@你的年如2002) as a
select @sql = left(@sql,len(@sql)-1) + ' from 表A'
exec(@sql)
go
set @sql = 'select '
select @sql = @sql + 'sum(case convert(char(6),A_date,112) when '''+[temp]+'''
then a_mnt else 0 end) as '''+[temp]+''','
from (select distinct convert(char(6),A_date,112) [temp] from 表A where year(A_date)=@你的年如2002) as a
select @sql = left(@sql,len(@sql)-1) + ' from 表A'
exec(@sql)
go
pengdali 2003-01-15
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case convert(char(6),A_date,112) when '''+[temp]+'''
then a_mnt else 0 end) as '''+[temp]+''','
from (select distinct convert(char(6),A_date,112) [temp] from 表A where year(A_date)=@你的年如2002) as a
select @sql = left(@sql,len(@sql)-1) + ' from 表A'
print @sql
exec(@sql)
go
set @sql = 'select '
select @sql = @sql + 'sum(case convert(char(6),A_date,112) when '''+[temp]+'''
then a_mnt else 0 end) as '''+[temp]+''','
from (select distinct convert(char(6),A_date,112) [temp] from 表A where year(A_date)=@你的年如2002) as a
select @sql = left(@sql,len(@sql)-1) + ' from 表A'
print @sql
exec(@sql)
go
DainelLee 2003-01-15
不好意思，还是有些问题
select sum(case when a_date=1 then a_mnt else 0 end) as 1月
sum(case when a_date=2 then a_mnt else 0 end) as 2月
.
.
end) as 12月
from table
select sum(case when a_date=1 then a_mnt else 0 end) as 1月
sum(case when a_date=2 then a_mnt else 0 end) as 2月
.
.
end) as 12月
from table
Lyer 2003-01-15
谢谢DainelLee(Iinexperience)
DainelLee 2003-01-15
select sum(case when a_date=1 then a_mnt else 0 end) as 1月
when a_date=2 then a_mnt else 0 end) as 2月
.
.
end) as 12月
from table
when a_date=2 then a_mnt else 0 end) as 2月
.
.
end) as 12月
from table
DainelLee 2003-01-15
你变变就可以用了
DainelLee 2003-01-15
select sum(case when a_date=1 then a_mnt else 0
when a_date=2 then a_mnt else 0
.
.
end)
from table
when a_date=2 then a_mnt else 0
.
.
end)
from table
