SQL小技巧

数据库结构如下：

要求：一句SQL实现，分别统计type1，2，3，4 一共多少money （group by uid）

原先做法：分开查询 然后用php合并结果，如

select sum(money) form test where type = 1 group by uid  
select sum(money) form test where type = 2 group by uid  
select sum(money) form test where type = 3 group by uid  
select sum(money) form test where type = 4 group by uid

现在要求一句SQL。

SELECT  
uid,  
SUM( IF(`type`=1, money, 0 ) ) AS `BuyByCash`,  
SUM( IF (`type`=3, money, 0 ) ) AS `BuyByCard`,  
SUM( IF( `type` IN (1,3), money, 0 ) ) AS `BuyByCashAndCard`,  
SUM( IF(`type`=2, money, 0 ) ) AS `SellByCash`,  
SUM( IF(`type`=4, money, 0 ) ) AS `SellByCard`,  
SUM( IF( `type` IN (2,4), money, 0 ) ) AS `BuyByCashAndCard`,  
SUM(money) AS `Amount`,  
COUNT(*) AS `DataNums`  
FROM `test`   
group by `uid`

解决了