MySQL学习--union

最新推荐文章于 2024-12-05 08:44:25 发布

南岸青栀*

最新推荐文章于 2024-12-05 08:44:25 发布

阅读量2.6k

点赞数 6

CC 4.0 BY-SA版权

分类专栏： SQL学习

本文链接：https://blog.csdn.net/qq_43710889/article/details/115704679

SQL学习专栏收录该内容

10 篇文章

订阅专栏

本文介绍了如何使用UNION查询合并两个表的数据并显示在网页表格中，展示了UNION操作的不同用法，包括列类型不一致时的处理、结果集排序以及去重与不去重的选择。通过实例演示了如何通过一条SQL语句实现留言板数据的整合展示，提高了查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

union查询

背景：

查询两张表中的数据，然后将留言板表中的数据输出到网页表格中。：

<?php

$conn = mysqli_connect('localhost','root','azb123');

$sql = 'use shop;';

mysqli_query($conn,$sql);

$sql = 'set name utf8;';
mysqli_query($conn,$sql);

//取出feedback表中的数据
$sql = 'select user_name,msg_content,msg_time from feedback where msg_status=1; ';

$rs = mysqli_query($conn,$sql);

$feed = array();
while($row = mysqli_fetch_assoc($rs)){
    $feed[] = $row;
}
//取出comment表中的数据
$sql = 'select user_name,content as msg_content,add_time as msg_time from comment where status=1;';

$rs = mysqli_query($conn,$sql);

$comment = array();
while($row = mysqli_fetch_assoc($rs)){
    $comment[] = $row;
}
$all = array_merge($feed,$comment);
//print_r($feed);
//print_r($comment);

?>
<html>
<head>
    <title>我也会做留言板</title>
    <meta charset="utf-8">
</head>
<body>
<table border="1px">
    <?php foreach ($all as $v){?>
    <tr>
        <td><?php echo $v['user_name']?></td>
        <td><?php echo $v['msg_time']?></td>
    </tr>
    <tr>
        <td colspan="2"><?php echo $v['msg_content']?></td>

    </tr>
    <?php }?>
</table>

</body>
</html>

效果图
在这里插入图片描述

但是可以使用一句sql语句就可以实现

union

union查询:将2条或多条SQL的查询结果合并成1个结果集

语法：sql1 union sql2

（1）从两张表中查询再union

注：取出的结果集两个列名称不同也是可以取出的；取出的列名以第一条sql为准

mysql> #ecshop留言板简化版union
mysql> select user_name,msg_content,msg_time from feedback where msg_status=1
    -> union
    -> select user_name,content as msg_content,add_time as msg_time from comment where status=1;
+--------------+-----------------------------+------------+
| user_name    | msg_content                 | msg_time   |
+--------------+-----------------------------+------------+
| 匿名用户     | 骗子！寄来的是砖头          | 1351573375 |
| ecshop       | 很好，我很喜欢              | 1242107120 |
+--------------+-----------------------------+------------+
2 rows in set (0.02 sec)

mysql>  select goods_id,goods_name,shop_price from goods where shop_price < 30
    -> union
    ->  select goods_id ,goods_name,shop_price from goods where shop_price > 4000
    -> order by  shop_price;
+----------+--------------------------------+------------+
| goods_id | goods_name                     | shop_price |
+----------+--------------------------------+------------+
|       30 | 移动20元充值卡                 |      18.00 |
|       26 | 小灵通/固话20元充值卡          |      19.00 |
|        5 | 索爱原装m2卡读卡器             |      20.00 |
|       22 | 多普达touch hd                 |    5999.00 |
+----------+--------------------------------+------------+
4 rows in set (0.10 sec)

（2）列类型不一样也可以取出

mysql> select user_name,msg_content,msg_time from feedback where msg_status=1
    -> union
    -> select add_time,user_name,content from comment where status=1;
+--------------+-----------------------------+-----------------------+
| user_name    | msg_content                 | msg_time              |
+--------------+-----------------------------+-----------------------+
| 匿名用户     | 骗子！寄来的是砖头          | 1351573375            |
| 1242107120   | ecshop                      | 很好，我很喜欢        |
+--------------+-----------------------------+-----------------------+
2 rows in set (0.00 sec)

（3）union后还可以进行排序

注：内层的order by的单独使用，不会影响结果集，仅排序；所以mysql将其优化掉

例如：limit会实际影响结果集，所以内层的order by排序是有意义的。

#union后的结果集，能否进行排序
#内层order by无意义

mysql> (select goods_name,shop_price from goods where cat_id =4 order by shop_price)
    -> union
    -> (select goods_name,shop_price from goods where cat_id =5 order by shop_price);
+-----------------+------------+
| goods_name      | shop_price |
+-----------------+------------+
| kd876           |    1388.00 |
| 诺基亚5800xm    |    2625.00 |
| 夏新t5          |    2878.00 |
| 诺基亚n96       |    3700.00 |
+-----------------+------------+
4 rows in set (0.00 sec)

mysql> select goods_name,shop_price from goods where cat_id =4
    -> union
    -> select goods_name,shop_price from goods where cat_id =5
    -> order by shop_price;
+-----------------+------------+
| goods_name      | shop_price |
+-----------------+------------+
| kd876           |    1388.00 |
| 诺基亚5800xm    |    2625.00 |
| 夏新t5          |    2878.00 |
| 诺基亚n96       |    3700.00 |
+-----------------+------------+
4 rows in set (0.00 sec)

#内层order by有意义
mysql> select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3;
+-------------------+--------+------------+
| goods_name        | cat_id | shop_price |
+-------------------+--------+------------+
| 多普达touch hd    |      3 |    5999.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 夏新n7            |      3 |    2300.00 |
+-------------------+--------+------------+
3 rows in set (0.02 sec)

mysql> select goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc limit 3;
+-----------------+--------+------------+
| goods_name      | cat_id | shop_price |
+-----------------+--------+------------+
| 夏新t5          |      4 |    2878.00 |
| 诺基亚5800xm    |      4 |    2625.00 |
| kd876           |      4 |    1388.00 |
+-----------------+--------+------------+
3 rows in set (0.00 sec)

mysql> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3)
    -> union
    -> (select goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc limit 3);
+-------------------+--------+------------+
| goods_name        | cat_id | shop_price |
+-------------------+--------+------------+
| 多普达touch hd    |      3 |    5999.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 夏新n7            |      3 |    2300.00 |
| 夏新t5            |      4 |    2878.00 |
| 诺基亚5800xm      |      4 |    2625.00 |
| kd876             |      4 |    1388.00 |
+-------------------+--------+------------+
6 rows in set (0.00 sec)

mysql> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3)
    -> union
    -> (select goods_name,cat_id,shop_price from goods where cat_id=4 order by shop_price desc limit 3)
    -> order by shop_price desc;
+-------------------+--------+------------+
| goods_name        | cat_id | shop_price |
+-------------------+--------+------------+
| 多普达touch hd    |      3 |    5999.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 夏新t5            |      4 |    2878.00 |
| 诺基亚5800xm      |      4 |    2625.00 |
| 夏新n7            |      3 |    2300.00 |
| kd876             |      4 |    1388.00 |
+-------------------+--------+------------+
6 rows in set (0.00 sec)

（4）union默认去重

想要关闭去重就使用union all

#默认去重
mysql> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3)
    -> union
    -> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 5)
    -> order by shop_price;
+-------------------+--------+------------+
| goods_name        | cat_id | shop_price |
+-------------------+--------+------------+
| 金立 a30          |      3 |    2000.00 |
| 诺基亚e66         |      3 |    2298.00 |
| 夏新n7            |      3 |    2300.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 多普达touch hd    |      3 |    5999.00 |
+-------------------+--------+------------+
5 rows in set (0.00 sec)

mysql> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 3)
    -> union all
    -> (select goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price desc limit 5)
    -> order by shop_price;
+-------------------+--------+------------+
| goods_name        | cat_id | shop_price |
+-------------------+--------+------------+
| 金立 a30          |      3 |    2000.00 |
| 诺基亚e66         |      3 |    2298.00 |
| 夏新n7            |      3 |    2300.00 |
| 夏新n7            |      3 |    2300.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 诺基亚n85         |      3 |    3010.00 |
| 多普达touch hd    |      3 |    5999.00 |
| 多普达touch hd    |      3 |    5999.00 |
+-------------------+--------+------------+
8 rows in set (0.01 sec)

union 的一道面试题

#A表
+------+------+
| id   | num  |
+------+------+
| a    |    5 |
| b    |   10 |
| c    |   15 |
| d    |   10 |
+------+------+

#B表
+------+------+
| id   | num  |
+------+------+
| b    |    5 |
| c    |   15 |
| d    |   20 |
| e    |   99 |
+------+------+

要求查询出以下结果：
+------+----------+
| id   | sum(num) |
+------+----------+
| a    |        5 |
| b    |       15 |
| c    |       15 |
| d    |       30 |
| e    |       99 |
+------+----------+


解题：使用sum（）和group
mysql> select id,sum(num) from (
    -> select * from a
    -> union
    -> select * from b
    -> ) as tmp group by id;
+------+----------+
| id   | sum(num) |
+------+----------+
| a    |        5 |
| b    |       15 |
| c    |       15 |
| d    |       30 |
| e    |       99 |
+------+----------+

d,sum(num) from (
-> select * from a
-> union
-> select * from b
-> ) as tmp group by id;
±-----±---------+
| id | sum(num) |
±-----±---------+
| a | 5 |
| b | 15 |
| c | 15 |
| d | 30 |
| e | 99 |
±-----±---------+