显示下表年度为季度,算出总计
表income
+----+------+---------+-----------+
| id | year | quarter | amount |
+----+------+---------+-----------+
| 1 | 2004 | 1 | 2328.00 |
| 2 | 2004 | 2 | 3822.00 |
| 3 | 2004 | 3 | 7071.00 |
| 4 | 2004 | 4 | 8931.00 |
| 5 | 2005 | 1 | 2633.00 |
| 6 | 2005 | 2 | 3910.00 |
| 7 | 2005 | 3 | 237193.00 |
| 8 | 2005 | 4 | 567444.00 |
| 9 | 2006 | 1 | 12313.00 |
+----+------+---------+-----------+
结果:
+------+----------+---------+-----------+-----------+----------+
| year | 1d | 2d | 3d | 4d | SUM |
+------+----------+---------+-----------+-----------+----------|
| 2004 | 2328.00 | 3822.00 | 7071.00 | 8931.00 | 22152.00 |
| 2005 | 2633.00 | 3910.00 | 237193.00 | 567444.00 | 811180.00|
| 2006 | 12313.00 | NULL | NULL | NULL | 12313.00 |
+------+----------+---------+-----------+-----------+----------+
SQL:
+-------------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `income` (
`id` int(11) NOT NULL auto_increment,
`year` varchar(4) default NULL,
`quarter` int(2) default NULL,
`amount` decimal(15,2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=10 ;
INSERT INTO `income` VALUES (1, '2004', 1, 2328.00);
INSERT INTO `income` VALUES (2, '2004', 2, 3822.00);
INSERT INTO `income` VALUES (3, '2004', 3, 7071.00);
INSERT INTO `income` VALUES (4, '2004', 4, 8931.00);
INSERT INTO `income` VALUES (5, '2005', 1, 2633.00);
INSERT INTO `income` VALUES (6, '2005', 2, 3910.00);
INSERT INTO `income` VALUES (7, '2005', 3, 237193.00);
INSERT INTO `income` VALUES (8, '2005', 4, 567444.00);
INSERT INTO `income` VALUES (9, '2006', 1, 12313.00);
+-------------------------------------------------------------+
问题:通过income表数据生成结果表信息
方法一:
+-------------------------------------------------------------------------+
|select a.year,1d,2d,3d,4d,sum from (select distinct year from income) a |
|left join |
|(select year,amount as 1d,id from income where quarter=1 group by year)d1|
|on a.year=d1.year |
|left join
|(select year,amount as 2d,id from income where quarter=2 group by year)d2|
|on a.year=d2.year |
|left join |
|(select year,amount as 3d,id from income where quarter=3 group by year)d3|
|on a.year=d3.year |
|left join |
|(select year,amount as 4d,id from income where quarter=4 group by year)d4|
|on a.year=d4.year |
|left join |
|(select year,sum(amount) as sum,id from income group by year)s |
|on a.year=s.year |
+-------------------------------------------------------------------------+
方法二:
+-------------------------------------------------------+
|select year, |
|sum(case when quarter=1 then amount else 0 end) 1d, |
|sum(case when quarter=2 then amount else null end) 2d, |
|sum(case when quarter=3 then amount else null end) 3d, |
|sum(case when quarter=4 then amount else null end) 4d, |
|sum(amount) sum |
|from income group by year; |
+-------------------------------------------------------+
分享到:
相关推荐
SQL跟踪后,用该工具能够快速整理出整洁的SQL,减少人工的复制、帖贴
SQL格式整理工具,而且还可以把SQL语句转换成VB DELPHI C# JAVA里面用的格式
SqlServer索引碎片整理脚本,提据库查询效率,很有用。
自制SQL文整理,非开源,非完整,写得很挫,有使用时限,强烈不建议下载
《SQL》基本语句整理大全
对Sql语句的格式进行整理,使其易读。 操作简单,只需拷贝Sql语句,执行整理程序,然后再粘贴,就成为格式清晰的Sql语句了。
SQL Server面试常用sql整理
数据库整理工具 SQLserver专业数据整理
一些sql整理实例 sql整理实例 sql整理实例
很实用的sql语句整理工具,把杂乱的语句变得规范和易读。
SQLSERVER基础实用必会SQL语句整理.docx
sql语句整理 sql增删改查 更新数据记录 删除数据记录
经典sql语句整理大全 喜欢的朋友可以下来看看 纯文字版 比较小
DBA日常维护SQL整理,涵盖操作系统、存储、数据库各个层面
自己整理的sql常用语句,比较适合0接触学习sql的人
我最近在CSDN上学的SQL笔记.如有错误请多多指教.
sql优化总结,针对一般sql语句的优化编写进行总结整理,有助于形成良好的sql语句书写习惯
网上找的并汇总整理的SQL日期函数语句,应该比较全面了希望对大家有用
sqllice,一个自动整理SQL文格式的工具,很好用的,适合各语言的开发人员。
SQL Server 2005索引碎片整理