从查询结果中查询

orders表： 

    我想要从从表中查出每天电动车和手机各自的销售总额。这个需求还是蛮简单的，只需要根据createtime和product group by就行了。下面是我写的SQL语句：

SELECT DATE_FORMAT( createtime, "%Y-%m-%d" ) cr, product, SUM(price) total FROM orders GROUP BY DATE_FORMAT(createtime, "%Y-%m-%d"), product

查询结果如下： 

    这个结果确实满足了我的需求，但是存在一个问题，就是当天数多了的时候，会看得我头晕眼花的。如果查询结果是下面这个样子，我想看起来会舒服得多： 

    对于如何直接从表中查询出这样的结果，我是一点想法都没有，但是，我却知道如何从上一个SQL语句的查询结果中查询从而得到想要的结果，下面是我写的SQL语句：

SELECT origintable.cr, 

SUM(CASE WHEN origintable.product = "电动车" THEN origintable.total ELSE 0 END) "电动车", 

SUM(CASE WHEN origintable.product = "手机" THEN origintable.total ELSE 0 END) "手机" 

FROM (SELECT DATE_FORMAT( createtime, "%Y-%m-%d" ) AS cr, product, SUM(price) AS total 

FROM orders GROUP BY DATE_FORMAT(createtime, "%Y-%m-%d"), product) AS origintable 

GROUP BY origintable.cr

如果大家想亲自试一试，可以用下面的SQL语句创建orders表和向表中插入数据：

create table orders(

id int PRIMARY KEY auto_increment,

product VARCHAR(64) null,

price DOUBLE null,

createtime DATETIME

)

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("电动车", 50, NOW());

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("手机", 60, NOW());

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("电动车", 50, NOW());

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("手机", 60, NOW());

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("电动车", 50, "2016-04-14 22:16:11");

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("手机", 60, "2016-04-14 22:16:11");

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("电动车", 50, "2016-04-14 22:16:11");

INSERT INTO orders (orders.product, orders.price, orders.createtime) VALUES ("手机", 60, "2016-04-14 22:16:11");                 

来源网址：