SQL语句问题，期待高手指点！

Question

本帖最后由 hansint 于 2009-12-24 18:57:19 编辑

Answer 1

比较常见的做法。select Job_date,
sum(if(Product=0,Quantity,null)) as Product0,
sum(if(Product=1,Quantity,null)) as Product1
from job_order
group by Job_datemysql> select * from job_order;
+------------+----------+---------+
| Job_date   | Quantity | Product |
+------------+----------+---------+
| 2009-12-01 |      500 |       1 |
| 2009-12-01 |      100 |       0 |
| 2009-12-01 |      100 |       1 |
| 2009-11-01 |      600 |       0 |
| 2009-10-01 |      300 |       1 |
+------------+----------+---------+
5 rows in set (0.00 sec)mysql> select Job_date,
    ->  sum(if(Product=0,Quantity,null)) as Product0,
    ->  sum(if(Product=1,Quantity,null)) as Product1
    -> from job_order
    -> group by Job_date;
+------------+----------+----------+
| Job_date   | Product0 | Product1 |
+------------+----------+----------+
| 2009-10-01 |     NULL |      300 |
| 2009-11-01 |      600 |     NULL |
| 2009-12-01 |      100 |      600 |
+------------+----------+----------+
3 rows in set (0.00 sec)mysql>

Answer 2

交叉表，用SUM(IF()) OR SUM(CASE WHEN)
select Job_date,sum(if(Product=0,Quantity,null)) as Product0,sum(if(Product=1,Quantity,null)) as Product1 from job_order group by Job_date;