摘要：
我同事写了一条Sql语句，当时没看明白，下面晒出来：SELECTSUM(new_cart)asnew_cart,SUM(new_collect)asnew_collect,SUM(total_cart)astotal_cart,SUM(total_collect)astotal_collect,SUM(ipv)ipv,SUM(iuv)iuvFROM(SELECTSUM(new_cart)asnew
我同事写了一条Sql语句，当时没看明白，下面晒出来：
SELECT SUM(new_cart) as new_cart,SUM(new_collect) as new_collect, SUM(total_cart) as total_cart, SUM(total_collect) as total_collect, SUM(ipv) ipv, SUM(iuv) iuv FROM
( SELECT SUM(new_cart) as new_cart,SUM(new_collect) as new_collect,0 as total_cart,0 as total_collect, SUM(ipv) ipv, SUM(iuv) iuv FROM tablename WHERE id <= 5 union all SELECT 0 as new_cart,0 as new_collect,total_cart, total_collect, 0 AS ipv, 0 AS iuv FROM tablename WHERE id = 5
) as t
ORDER BY new_cart desc;
数据表结构：
CREATE TABLE `tablename` ( `id` int(11) NOT NULL AUTO_INCREMENT, `new_cart` bigint(20) DEFAULT NULL, `new_collect` bigint(20) DEFAULT NULL, `total_cart` bigint(20) DEFAULT NULL , `total_collect` bigint(20) DEFAULT NULL , `ipv` bigint(20) DEFAULT NULL , `iuv` bigint(20) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
那么其实是使用了MYSQL的UNION。UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
录入语句：
mysql> insert into tablename values (1,1,1,1,1,1,1); Query OK, 1 row affected (0.00sec) mysql> insert into tablename values (2,2,2,2,2,2,2); Query OK, 1 row affected (0.00sec) mysql> insert into tablename values (3,3,3,3,3,3,3); Query OK, 1 row affected (0.00sec) mysql> insert into tablename values (4,4,4,4,4,4,4); Query OK, 1 row affected (0.00sec) mysql> insert into tablename values (5,5,5,5,5,5,5); Query OK, 1 row affected (0.00 sec)
执行第一条语句：
SELECT SUM(new_cart) as new_cart,SUM(new_collect) as new_collect,0 as total_cart,0 as total_collect, SUM(ipv) ipv, SUM(iuv) iuv FROM tablename WHERE id <= 5 union all SELECT 0 as new_cart,0 as new_collect,total_cart, total_collect, 0 AS ipv, 0 AS iuv FROM tablename WHERE id = 5;
输出：
+----------+-------------+------------+---------------+------+------+ | new_cart | new_collect | total_cart | total_collect | ipv | iuv | +----------+-------------+------------+---------------+------+------+ | 15 | 15 | 0 | 0 | 15 | 15 | | 0 | 0 | 5 | 5 | 0 | 0 | +----------+-------------+------------+---------------+------+------+ 2 rows in set (0.00 sec)
可见，union是把两条记录合并输出出来，那么再将这些内容相加输出该怎么办哪？聪明的你一定想到了第一条数据语句了吧~
mysql> SELECT SUM(new_cart) as new_cart,SUM(new_collect) as new_collect, SUM(total_cart) as total_cart, SUM(total_collect) as total_collect, SUM(ipv) ipv, SUM(iuv) iuv FROM ( SELECT SUM(new_cart) as new_cart,SUM(new_collect) as new_collect,0 as total_cart,0 as total_collect, SUM(ipv) ipv, SUM(iuv) iuv FROM tablename WHERE id <= 5 union all SELECT 0 as new_cart,0 as new_collect,total_cart, total_collect, 0 AS ipv, 0 AS iuv FROM tablename WHERE id = 5 ) ast ORDER BY new_cart desc; +----------+-------------+------------+---------------+------+------+ | new_cart | new_collect | total_cart | total_collect | ipv | iuv | +----------+-------------+------------+---------------+------+------+ | 15 | 15 | 5 | 5 | 15 | 15 | +----------+-------------+------------+---------------+------+------+ 1 row in set (0.00 sec)