有一个表table
sign updatetime jiner
收入 2009-1-10 100
付出 2009-1-20 100
收入 2009-1-30 200
付出 2009-2-5 100
收入 2009-4-5 100....有这样的结果(按月份统计)月份 收入汇总 付出汇总
2009.1 300 100
2009.2 0 100
2009.3 0 0
2009.4 100 0...
sign updatetime jiner
收入 2009-1-10 100
付出 2009-1-20 100
收入 2009-1-30 200
付出 2009-2-5 100
收入 2009-4-5 100....有这样的结果(按月份统计)月份 收入汇总 付出汇总
2009.1 300 100
2009.2 0 100
2009.3 0 0
2009.4 100 0...
sum(case when sign='收入' then jiner else 0 end),
sum(case when sign='付出' then jiner else 0 end)
from tb
group by convert(varchar(7),updatetime,120)
SUM(CASE WHEN sign ='收入' THNE jiner ESLE 0 END )AS 收入汇总,
SUM(CASE WHEN sign ='支出' THNE jiner ESLE 0 END )AS 支出汇总
FROM TB GROUP BY CONVERT(VARCHAR(7),updatetime ,120)
/***********************************************--> 测试数据:[TB]
--> 测试时间:2009-08-13 15:25:24
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[TB]') is not null drop table [TB]
create table [TB]([sign] varchar(4),[updatetime] datetime,[jiner] int)
insert [TB]
select '收入','2009-1-10',100 union all
select '付出','2009-1-20',100 union all
select '收入','2009-1-30',200 union all
select '付出','2009-2-5',100 union all
select '收入','2009-4-5',100
select 月份=convert(varchar(7),[updatetime],120),
收入汇总=sum(case when [sign]='收入' then [jiner] end),
付出汇总=sum(case when [sign]='付出' then [jiner] end)
from TB
group by convert(varchar(7),[updatetime],120)/*
月份 收入汇总 付出汇总
------- ----------- -----------
2009-01 300 100
2009-02 NULL 100
2009-04 100 NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/drop table TB
月份=DATEname(YEAR,updatetime)+'.'+DATEname(MONTH,updatetime),
收入汇总=SUM(case when SIGN='收入' then jiner else 0 end),
付出汇总=SUM(case when SIGN='付出' then jiner else 0 end)
from [table]
group by DATEname(YEAR,updatetime)+'.'+DATEname(MONTH,updatetime)
drop table [TB]
go
create table [TB]([sign] varchar(4),[updatetime] datetime,[jiner] int)
insert [TB]
select '收入','2009-1-10',100 union all
select '付出','2009-1-20',100 union all
select '收入','2009-1-30',200 union all
select '付出','2009-2-5',100 union all
select '收入','2009-4-5',100select
月份=DATEname(YEAR,updatetime)+'.'+DATEname(MONTH,updatetime),
收入汇总=SUM(case when SIGN='收入' then jiner else 0 end),
付出汇总=SUM(case when SIGN='付出' then jiner else 0 end)
from tb
group by DATEname(YEAR,updatetime)+'.'+DATEname(MONTH,updatetime)
/*(5 行受影响)
月份 收入汇总 付出汇总
------------------------------------------------------------- ----------- -----------
2009.01 300 100
2009.02 0 100
2009.04 100 0(3 行受影响)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-13 15:28:24
----------------------------------------------------------------
--> 测试数据:[table]
if object_id('[table]') is not null drop table [table]
go
create table [table]([sign] varchar(4),[updatetime] datetime,[jiner] int)
insert [table]
select '收入','2009-1-10',100 union all
select '付出','2009-1-20',100 union all
select '收入','2009-1-30',200 union all
select '付出','2009-2-5',100 union all
select '收入','2009-4-5',100
--------------开始查询--------------------------
select
convert(varchar(7),updatetime,120) as 月份,
sum(case when sign='收入' then jiner else 0 end) as 收入汇总 ,
sum(case when sign='付出' then jiner else 0 end) as 支出汇总
from
[table]
group by convert(varchar(7),updatetime,120)
----------------结果----------------------------
/*月份 收入汇总 支出汇总
------- ----------- -----------
2009-01 300 100
2009-02 0 100
2009-04 100 0(所影响的行数为 3 行)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-13 15:28:24
----------------------------------------------------------------
--> 测试数据:[table]
if object_id('[table]') is not null drop table [table]
go
create table [table]([sign] varchar(4),[updatetime] datetime,[jiner] int)
insert [table]
select '收入','2009-1-10',100 union all
select '付出','2009-1-20',100 union all
select '收入','2009-1-30',200 union all
select '付出','2009-2-5',100 union all
select '收入','2009-4-5',100
--------------开始查询--------------------------
select
replace(convert(varchar(7),updatetime,120),'-','.') as 月份,
sum(case when sign='收入' then jiner else 0 end) as 收入汇总 ,
sum(case when sign='付出' then jiner else 0 end) as 支出汇总
from
[table]
group by replace(convert(varchar(7),updatetime,120),'-','.')
----------------结果----------------------------
/*月份 收入汇总 支出汇总
----------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
2009.01 300 100
2009.02 0 100
2009.04 100 0
(所影响的行数为 3 行)
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-13 15:28:24
----------------------------------------------------------------
--> 测试数据:[table]
if object_id('[table]') is not null drop table [table]
go
create table [table]([sign] varchar(4),[updatetime] datetime,[jiner] int)
insert [table]
select '收入','2009-1-10',100 union all
select '付出','2009-1-20',100 union all
select '收入','2009-1-30',200 union all
select '付出','2009-2-5',100 union all
select '收入','2009-4-5',100
--------------开始查询--------------------------
select
replace(convert(varchar(7),updatetime,120),'-','.') as 月份,
sum(case when sign='收入' then jiner else 0 end) as 收入汇总 ,
sum(case when sign='付出' then jiner else 0 end) as 支出汇总
from
[table]
group by replace(convert(varchar(7),updatetime,120),'-','.')
----------------结果----------------------------
/*月份 收入汇总 付出汇总
------------------------------------------------------------- ----------- -----------
2009.01 300 100
2009.02 0 100
2009.04 100 0(3 行受影响)*/
convert(varchar(7),updatetime ,120) 月份,
sum(case when sign='收入' then jiner else 0 end) 收入汇总 ,
sum(case when sign='付出' then jiner else 0 end) 支出汇总
from table
group by convert(varchar(7),updatetime ,120)