java sql查询得-1,mysql 1~12月份sql查询语句解决思路

SQL codeselect 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

------解决方案--------------------

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

建表的语句我给你了

下面是sql

select 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,