mysql 1~12月份sql查询语句解决思路
日期：2014-05-17 浏览次数：20813 次
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？
------解决方案--------------------
统计报表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,
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
