mysql 1~12月份sql查询语句解决思路

mysql 1~12月份sql查询语句
统计报表2011年大食堂收入一览表
序号姓名 1 2 3 4 5 6 7 8 9 10 11 12 合计
1 王庆 300 300 300 300 300 300 300 300 300 300 300 300 100000
2 林帅 300 300 300 300 300 300 300 300 300 300 300 300 100000
3 李超 300 300 300 300 300 300 300 300 300 300 300 300 100000
4 刘磊 300 300 300 300 300 300 300 300 300 300 300 300 100000

以上其实是一个表格，统计1~12月每个人的充值记录
一、功能描述：选择年份，生成1~12月份的累积充值金额，例如以上的是2011年的4个人每个月份的充值金额总和，当然数据不正确，我随便写的，数据库是mysql
二、数据表结构：
1、姓名：uname
2、金额：money
3、充值日期：mydate

怎么写这个查询sql？

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

SQL code


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;

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

hava a try

SQL code

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

------解决方案--------------------
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,

mysql 1~12月份sql查询语句解决思路

相关推荐