select uname,sum(money) as money from xxx where mydate between '2011-1-1' and '2011-12-31';
select uname,sum(money) as money from xxx where mydate between '2011-1-1' and '2011-12-31' group by uname;
--没懂楼主的意思 select uname,sum(money) as money from xxx where mydate between '2011-1-1' and '2011-12-31' group by uname;
大概的意思是select uname, 一月的汇总数据,二月的汇总数据....,合计 from XX where ....
select uname,t1.money,t2.money,...,t.money as money from xxx, (select sum(money) as money where mydate between '2011-1-1' and '2011-1-31' group by uname) t1, (select sum(money) as money where mydate between '2011-2-1' and '2011-2-28' group by uname) t2, ... (select sum(money) as money where mydate between '2011-2-1' and '2011-12-31' group by uname) t;
select uname,sum(1月份),sum(2月份),sum(3月份)....,sum(合计)from xxx where mydate between '2011-1-1' and '2011-12-31' group by unamesum(里面放的是你每个月所对应的列名)
select uname,t1.money as 1,t2.money as 2,...,t.money as all from xxx, (select sum(money) as money where mydate between '2011-1-1' and '2011-1-31' group by uname) t1, (select sum(money) as money where mydate between '2011-2-1' and '2011-2-28' group by uname) t2, ... (select sum(money) as money where mydate between '2011-1-1' and '2011-12-31' group by uname) t;
select uname,onemoney extract(year from to_date('2011-1-1','yyyy-mm-dd')), towmoney extract(year from to_date('2011-2-1','yyyy-mm-dd')), . . . sum(money) "合计" from .... 提供一个大概的意思,具体自己去写
假定mydate是形如yyyy-MM-dd的字符类型: select uname,substring(mydate, 1, 7), sum(money) from xxx where mydate between '2011-01-01' and '2011-12-31' group by uname, substring(mydate, 1, 7) union select uname, 'total', sum(money) from xxx where mydate between '2011-01-01' and '2011-12-31' group by uname
select infos.uname,t1.money,t2.money,t.money 总计 from infos, (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1, (select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-28' group by uname) t2, (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) twhere infos.uname=t1.uname and infos.uname=t2.uname and infos.uname=t.uname 我是这样想的,但是人员老是重复,导致查询出的结果不是我想要的
select mydate,case when a.mydate<12 then (select sum(money) money from test where mydate between 1 and a.mydate) else (select sum(money) from test) end from test a group by mydate 对应表结构 uname 姓名 money 金额 mydate 月份你要年的话,可以再加个年的字段,你对照这个调试下你的sql语句
select distinct infos.uname,t1.money,t2.money,t.money from infos, (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1, (select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-28' group by uname) t2, (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) t where infos.uname=t1.uname and infos.uname=t2.uname and infos.uname=t.uname加distinct 可以防止重复,但是只能查询每个月都有数据的人员,只有几个月有数据的人员查不出来
上面这个结果表示下一月汇总前面各月的数据,如果只需要每个月汇总显示的话可以参考下面写法select mydate,case when 1 then (select sum(money) money from test where mydate=a.mydate) end from test a group by mydate
hava a tryselect uname, sum(case when month(mydate) = 1 then money else 0) as Jan, sum(case when month(mydate) = 2 then money else 0) as Feb, sum(case when month(mydate) = 3 then money else 0) as Mar, sum(case when month(mydate) = 4 then money else 0) as Apr, sum(case when month(mydate) = 5 then money else 0) as May, sum(case when month(mydate) = 6 then money else 0) as Jun, sum(case when month(mydate) = 7 then money else 0) as Jul, sum(case when month(mydate) = 8 then money else 0) as Aug, sum(case when month(mydate) = 9 then money else 0) as Sep, sum(case when month(mydate) = 10 then money else 0) as Oct, sum(case when month(mydate) = 11 then money else 0) as Nov, sum(case when month(mydate) = 12 then money else 0) as Dec, sum(month) as Total from your_table where year(mydate) = 2011 group by uname
hava a try select uname, sum(case when month(mydate) = 1 then money else 0) as Jan, sum(case when month(mydate) = 2 then money else 0) as Feb, sum(case when month(mydate) = 3 then money else 0) as Mar, sum(case when month(mydate) = 4 then money else 0) as Apr, sum(case when month(mydate) = 5 then money else 0) as May, sum(case when month(mydate) = 6 then money else 0) as Jun, sum(case when month(mydate) = 7 then money else 0) as Jul, sum(case when month(mydate) = 8 then money else 0) as Aug, sum(case when month(mydate) = 9 then money else 0) as Sep, sum(case when month(mydate) = 10 then money else 0) as Oct, sum(case when month(mydate) = 11 then money else 0) as Nov, sum(case when month(mydate) = 12 then money else 0) as Dec, sum(money) as Total --上面这里写错了 from your_table where year(mydate) = 2011 group by uname
各位我这样写实现了,但是感觉太繁琐,有没有更简单的方法实现 select distinct infos.uname,t1.money,t2.money,t3.money,t4.money,t5.money,t6.money,t7.money,t8.money,t9.money,t10.money,t11.money,t12.money,t.money from infos left join (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1 on infos.uname=t1.uname left join (select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-30' group by uname) t2 on infos.uname=t2.uname left join (select uname,sum(money) as money from infos where mydate between '2011-3-1' and '2011-3-31' group by uname) t3 on infos.uname=t3.uname left join (select uname,sum(money) as money from infos where mydate between '2011-4-1' and '2011-4-30' group by uname) t4 on infos.uname=t4.uname left join (select uname,sum(money) as money from infos where mydate between '2011-5-1' and '2011-5-31' group by uname) t5 on infos.uname=t5.uname left join (select uname,sum(money) as money from infos where mydate between '2011-6-1' and '2011-6-30' group by uname) t6 on infos.uname=t6.uname left join (select uname,sum(money) as money from infos where mydate between '2011-7-1' and '2011-7-31' group by uname) t7 on infos.uname=t7.uname left join (select uname,sum(money) as money from infos where mydate between '2011-8-1' and '2011-8-31' group by uname) t8 on infos.uname=t8.uname left join (select uname,sum(money) as money from infos where mydate between '2011-9-1' and '2011-9-30' group by uname) t9 on infos.uname=t9.uname left join (select uname,sum(money) as money from infos where mydate between '2011-10-1' and '2011-10-31' group by uname) t10 on infos.uname=t10.uname left join (select uname,sum(money) as money from infos where mydate between '2011-11-1' and '2011-11-30' group by uname) t11 on infos.uname=t11.uname left join (select uname,sum(money) as money from infos where mydate between '2011-12-1' and '2011-12-31' group by uname) t12 on infos.uname=t12.uname left join (select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) t on infos.uname=t.uname
USE [test] GO /****** 对象: Table [dbo].[test] 脚本日期: 11/30/2011 10:42:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[test]( [uname] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [umoney] [int] NULL, [udate] [datetime] NULL ) ON [PRIMARY]GO SET ANSI_PADDING OFF 建表的语句我给你了 下面是sqlselect A.uname, isNull(sum(A.一月),0) as '一月', isNull(sum(A.二月),0) as '二月', isNull(sum(A.三月),0) as '三月', isNull(sum(A.四月),0) as '四月', isNull(sum(A.五月),0) as '五月', isNull(sum(A.六月),0) as '六月', isNull(sum(A.七月),0) as '七月', isNull(sum(A.八月),0) as '八月', isNull(sum(A.九月),0) as '九月', isNull(sum(A.十月),0) as '十月', isNull(sum(A.十一月),0) as '十一月', isNull(sum(A.十二月),0) as '十二月' from ( select uname, '一月'=case when udate between '2001-01-01' and '2001-01-31' then umoney end, '二月'=case when udate between '2001-02-01' and '2001-02-28' then umoney end, '三月'=case when udate between '2001-03-01' and '2001-03-31' then umoney end, '四月'=case when udate between '2001-04-01' and '2001-04-30' then umoney end, '五月'=case when udate between '2001-05-01' and '2001-05-31' then umoney end, '六月'=case when udate between '2001-06-01' and '2001-06-30' then umoney end, '七月'=case when udate between '2001-07-01' and '2001-07-31' then umoney end, '八月'=case when udate between '2001-08-01' and '2001-08-31' then umoney end, '九月'=case when udate between '2001-09-01' and '2001-09-30' then umoney end, '十月'=case when udate between '2001-10-01' and '2001-10-31' then umoney end, '十一月'=case when udate between '2001-11-01' and '2001-11-30' then umoney end, '十二月'=case when udate between '2001-12-01' and '2001-12-31' then umoney end from test ) A group by A.uname
21楼的兄弟,写的比较好,我稍稍改动下,确实比较好用,再次也谢谢各位同仁的帮助,下面贴出sql,希望对大家有些帮助,谢谢! select uname, sum(case month(mydate) when 1 then money else 0 end) as Jan, sum(case month(mydate) when 2 then money else 0 end) as Feb, sum(case month(mydate) when 3 then money else 0 end) as Mar, sum(case month(mydate) when 4 then money else 0 end) as Apr, sum(case month(mydate) when 5 then money else 0 end) as May, sum(case month(mydate) when 6 then money else 0 end) as Jun, sum(case month(mydate) when 7 then money else 0 end) as Jul, sum(case month(mydate) when 8 then money else 0 end) as Aug, sum(case month(mydate) when 9 then money else 0 end) as Sep, sum(case month(mydate) when 10 then money else 0 end) as Oct, sum(case month(mydate) when 11 then money else 0 end) as Nov, sum(case month(mydate) when 12 then money else 0 end) as Nov, sum(case year(mydate) when 2011 then money else 0 end) as 合计 from infos where year(mydate) = 2011 group by uname
--没懂楼主的意思
select uname,sum(money) as money
from xxx
where mydate between '2011-1-1' and '2011-12-31'
group by uname;
select uname,t1.money,t2.money,...,t.money as money
from xxx,
(select sum(money) as money where mydate between '2011-1-1' and '2011-1-31' group by uname) t1,
(select sum(money) as money where mydate between '2011-2-1' and '2011-2-28' group by uname) t2,
...
(select sum(money) as money where mydate between '2011-2-1' and '2011-12-31' group by uname) t;
select uname,t1.money as 1,t2.money as 2,...,t.money as all
from xxx,
(select sum(money) as money where mydate between '2011-1-1' and '2011-1-31' group by uname) t1,
(select sum(money) as money where mydate between '2011-2-1' and '2011-2-28' group by uname) t2,
...
(select sum(money) as money where mydate between '2011-1-1' and '2011-12-31' group by uname) t;
towmoney extract(year from to_date('2011-2-1','yyyy-mm-dd')),
.
.
.
sum(money) "合计"
from
....
提供一个大概的意思,具体自己去写
select uname,substring(mydate, 1, 7), sum(money) from xxx
where mydate between '2011-01-01' and '2011-12-31'
group by uname, substring(mydate, 1, 7)
union
select uname, 'total', sum(money) from xxx
where mydate between '2011-01-01' and '2011-12-31'
group by uname
from infos,
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1,
(select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-28' group by uname) t2,
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) twhere infos.uname=t1.uname and infos.uname=t2.uname and infos.uname=t.uname
我是这样想的,但是人员老是重复,导致查询出的结果不是我想要的
(select sum(money) money from test where mydate between 1 and a.mydate)
else
(select sum(money) from test)
end
from test a group by mydate
对应表结构
uname 姓名
money 金额
mydate 月份你要年的话,可以再加个年的字段,你对照这个调试下你的sql语句
from infos,
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1,
(select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-28' group by uname) t2,
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) t
where infos.uname=t1.uname and infos.uname=t2.uname and infos.uname=t.uname加distinct 可以防止重复,但是只能查询每个月都有数据的人员,只有几个月有数据的人员查不出来
上面这个结果表示下一月汇总前面各月的数据,如果只需要每个月汇总显示的话可以参考下面写法select mydate,case when 1 then
(select sum(money) money from test where mydate=a.mydate)
end
from test a group by mydate
sum(case when month(mydate) = 1 then money else 0) as Jan,
sum(case when month(mydate) = 2 then money else 0) as Feb,
sum(case when month(mydate) = 3 then money else 0) as Mar,
sum(case when month(mydate) = 4 then money else 0) as Apr,
sum(case when month(mydate) = 5 then money else 0) as May,
sum(case when month(mydate) = 6 then money else 0) as Jun,
sum(case when month(mydate) = 7 then money else 0) as Jul,
sum(case when month(mydate) = 8 then money else 0) as Aug,
sum(case when month(mydate) = 9 then money else 0) as Sep,
sum(case when month(mydate) = 10 then money else 0) as Oct,
sum(case when month(mydate) = 11 then money else 0) as Nov,
sum(case when month(mydate) = 12 then money else 0) as Dec,
sum(month) as Total
from your_table
where year(mydate) = 2011
group by uname
select uname,
sum(case when month(mydate) = 1 then money else 0) as Jan,
sum(case when month(mydate) = 2 then money else 0) as Feb,
sum(case when month(mydate) = 3 then money else 0) as Mar,
sum(case when month(mydate) = 4 then money else 0) as Apr,
sum(case when month(mydate) = 5 then money else 0) as May,
sum(case when month(mydate) = 6 then money else 0) as Jun,
sum(case when month(mydate) = 7 then money else 0) as Jul,
sum(case when month(mydate) = 8 then money else 0) as Aug,
sum(case when month(mydate) = 9 then money else 0) as Sep,
sum(case when month(mydate) = 10 then money else 0) as Oct,
sum(case when month(mydate) = 11 then money else 0) as Nov,
sum(case when month(mydate) = 12 then money else 0) as Dec,
sum(money) as Total --上面这里写错了
from your_table
where year(mydate) = 2011
group by uname
select distinct infos.uname,t1.money,t2.money,t3.money,t4.money,t5.money,t6.money,t7.money,t8.money,t9.money,t10.money,t11.money,t12.money,t.money
from infos
left join
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-1-31' group by uname) t1 on infos.uname=t1.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-2-1' and '2011-2-30' group by uname) t2 on infos.uname=t2.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-3-1' and '2011-3-31' group by uname) t3 on infos.uname=t3.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-4-1' and '2011-4-30' group by uname) t4 on infos.uname=t4.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-5-1' and '2011-5-31' group by uname) t5 on infos.uname=t5.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-6-1' and '2011-6-30' group by uname) t6 on infos.uname=t6.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-7-1' and '2011-7-31' group by uname) t7 on infos.uname=t7.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-8-1' and '2011-8-31' group by uname) t8 on infos.uname=t8.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-9-1' and '2011-9-30' group by uname) t9 on infos.uname=t9.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-10-1' and '2011-10-31' group by uname) t10 on infos.uname=t10.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-11-1' and '2011-11-30' group by uname) t11 on infos.uname=t11.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-12-1' and '2011-12-31' group by uname) t12 on infos.uname=t12.uname
left join
(select uname,sum(money) as money from infos where mydate between '2011-1-1' and '2011-12-31' group by uname) t on infos.uname=t.uname
GO
/****** 对象: Table [dbo].[test] 脚本日期: 11/30/2011 10:42:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[uname] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[umoney] [int] NULL,
[udate] [datetime] NULL
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
建表的语句我给你了
下面是sqlselect A.uname,
isNull(sum(A.一月),0) as '一月',
isNull(sum(A.二月),0) as '二月',
isNull(sum(A.三月),0) as '三月',
isNull(sum(A.四月),0) as '四月',
isNull(sum(A.五月),0) as '五月',
isNull(sum(A.六月),0) as '六月',
isNull(sum(A.七月),0) as '七月',
isNull(sum(A.八月),0) as '八月',
isNull(sum(A.九月),0) as '九月',
isNull(sum(A.十月),0) as '十月',
isNull(sum(A.十一月),0) as '十一月',
isNull(sum(A.十二月),0) as '十二月'
from
(
select uname,
'一月'=case when udate between '2001-01-01' and '2001-01-31' then umoney end,
'二月'=case when udate between '2001-02-01' and '2001-02-28' then umoney end,
'三月'=case when udate between '2001-03-01' and '2001-03-31' then umoney end,
'四月'=case when udate between '2001-04-01' and '2001-04-30' then umoney end,
'五月'=case when udate between '2001-05-01' and '2001-05-31' then umoney end,
'六月'=case when udate between '2001-06-01' and '2001-06-30' then umoney end,
'七月'=case when udate between '2001-07-01' and '2001-07-31' then umoney end,
'八月'=case when udate between '2001-08-01' and '2001-08-31' then umoney end,
'九月'=case when udate between '2001-09-01' and '2001-09-30' then umoney end,
'十月'=case when udate between '2001-10-01' and '2001-10-31' then umoney end,
'十一月'=case when udate between '2001-11-01' and '2001-11-30' then umoney end,
'十二月'=case when udate between '2001-12-01' and '2001-12-31' then umoney end
from test
)
A
group by A.uname
select uname,
sum(case month(mydate) when 1 then money else 0 end) as Jan,
sum(case month(mydate) when 2 then money else 0 end) as Feb,
sum(case month(mydate) when 3 then money else 0 end) as Mar,
sum(case month(mydate) when 4 then money else 0 end) as Apr,
sum(case month(mydate) when 5 then money else 0 end) as May,
sum(case month(mydate) when 6 then money else 0 end) as Jun,
sum(case month(mydate) when 7 then money else 0 end) as Jul,
sum(case month(mydate) when 8 then money else 0 end) as Aug,
sum(case month(mydate) when 9 then money else 0 end) as Sep,
sum(case month(mydate) when 10 then money else 0 end) as Oct,
sum(case month(mydate) when 11 then money else 0 end) as Nov,
sum(case month(mydate) when 12 then money else 0 end) as Nov,
sum(case year(mydate) when 2011 then money else 0 end) as 合计
from infos
where year(mydate) = 2011
group by uname