Mysql连接的问题

天南海北 发布于 2012/03/02 10:00
阅读 132
收藏 0

表结构如下:
CREATE TABLE `test` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `type` INT(1) NOT NULL,
  `money` INT(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

测试数据如下:

type=1表示收入 type=0表示支出

现在我想处理这张表,将结果表示为
name  shouru  zhichu
小明   10         30
东子   0           20

当然也有小明某天shouru为0的情况,我考虑到用join,但这是内连接吧
如果是LEFT JOIN或者RIGHT JOIN,总会损失一部分数据
想到用全连接,试过好像不支持,思路有限,特上来求助,谢谢!
SELECT
  a.name,
  a.money AS `shouru`,
  b.money AS `zhichu`
FROM ((SELECT
         `name`,
         `money`
       FROM `test`
       WHERE `type` = 1) a
   INNER JOIN (SELECT
           `name`,
           `money`
         FROM `test`
         WHERE `type` = 0) b
     ON a.name = b.name)

加载中
0
天南海北
天南海北
SELECT
  a.name,
  a.money AS `shouru`,
  b.money AS `zhichu`
FROM ((SELECT
         `name`,
         `money`
       FROM `test`
       WHERE `type` = 1) a
   FULL JOIN (SELECT
           `name`,
           `money`
         FROM `test`
         WHERE `type` = 0) b

     ON a.name = b.name)

 

这样不行,难道是版本不对?报错信息如下:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN (SELECT
           `name`,
           `money`
         FROM `test`
   ' at line 10
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

0
黄龍
黄龍

select a.name,

sum(case when a.type = 1 then a.money else 0 end) as shouru,

sum(case when a.type = 0 then a.money else 0 end) as shouru

from test

group by a.name

 

0
天南海北
天南海北

好东西!!!

case when果然博大精深!!!

0
天南海北
天南海北

引用来自“HL_Simon”的答案

select a.name,

sum(case when a.type = 1 then a.money else 0 end) as shouru,

sum(case when a.type = 0 then a.money else 0 end) as shouru

from test

group by a.name

 

谢谢你!我都忘记有这个东西了!
返回顶部
顶部