注册
登录

您现在的位置是：首页 > 学无止境

SQL小技巧

木木彡82 2011-12-16 09:34:53 共364人围观
其它
数据库结构如下：

要求：一句SQL实现，分别统计type1，2，3，4 一共多少money （group by uid）
原先做法：分开查询 然后用php合并结果，如
select sum(money) form test where type = 1 group by uid
s...
数据库结构如下：

要求：一句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`
解决了


上一篇：php自带函数判断是否email地址

下一篇：开源分词程序

相关文章

文章评论

  • 登录后评论

点击排行