CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(10) default NULL,
`num` varchar(5) default NULL,
`year` varchar(5) default NULL,
`money` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;INSERT INTO `test` VALUES ('1', '张三', 'A0001', '2012', '1000');
INSERT INTO `test` VALUES ('2', '李四', 'A0002', '2012', '200');
INSERT INTO `test` VALUES ('3', '张三', 'A0001', '2013', '300');
INSERT INTO `test` VALUES ('4', '王五', 'A0003', '2013', '600');
INSERT INTO `test` VALUES ('5', '张三', 'A0001', '2014', '1500');
INSERT INTO `test` VALUES ('6', '王五', 'A0003', '2014', '2000');
INSERT INTO `test` VALUES ('7', '李四', 'A0002', '2014', '700');
我要查询year=2012和2013时的数据,让查出的显示在一行,这样的sql要怎么写?name num year1 money1 year2 money2
张三 A0001 2012 1000 2013 300
李四 A0002 2012 200
王五 A0003 2013 600
SELECT * FROM `test1` ;
SELECT NAME,num,
SUM(IF(YEAR=2012,YEAR,0)),
SUM(IF(YEAR=2012,money,0)),
SUM(IF(YEAR=2013,YEAR,0)),
SUM(IF(YEAR=2013,money,0))
FROM test1
WHERE YEAR IN(2012,2013)
GROUP BY NAME,num
-> '2013',sum(case when year='2013' then money end)
-> from test
-> group by name,num
-> ;
+--------+-------+------+-------------------------------------------+------+-------------------------------------------+
| name | num | 2012 | sum(case when year='2012' then money end) | 2013 | sum(case when year='2013' then money end) |
+--------+-------+------+-------------------------------------------+------+-------------------------------------------+
| 张三 | A0001 | 2012 | 1000 | 2013 | 300 |
| 王五 | A0003 | 2012 | NULL | 2013 | 600 |
| 李四 | A0002 | 2012 | 200 | 2013 | NULL |
+--------+-------+------+-------------------------------------------+------+-------------------------------------------+
3 rows in set (0.00 sec)
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...