Mysql的union

我同事写了一条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.00 sec)

mysql> insert into tablename values (2,2,2,2,2,2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tablename values (3,3,3,3,3,3,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tablename values (4,4,4,4,4,4,4);
Query OK, 1 row affected (0.00 sec)

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     ) as t   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)

 

posted @ 2013-11-06 15:43  李秋  阅读(451)  评论(0)    收藏  举报