mysql多表联合查询操作实例分析

本文实例讲述了mysql多表联合查询操作。分享给大家供大家参考，具体如下：

MySQL多表联合查询是MySQL数据库的一种查询方式，下面就为您介绍MySQL多表联合查询的语法，供您参考学习之用。

MySQL多表联合查询语法：

复制代码 代码如下: SELECT * FROM 插入表 LEFT JOIN 主表 ON t1.lvid=t2.lv_id select * from mytable,title where 表名1.name=表名2.writer ;

 

mysql版本大于4.0，使用UNION进行查询，示例如下：

?

1 2 3 4

SELECT `id`, ` name `, ` date `, '' AS `type` FROM table_A WHERE 条件语句……   UNION SELECT `id`, ` name `, ` date `, '未完成' AS `type` FROM table_B WHERE 条件语句……   ORDER BY `id` LIMIT num;

mysql版本小于4.0，需要建立临时表，分为三步，示例如下：

第一步：建立临时表tmp_table_name并插入table_A中的相关记录

复制代码 代码如下: $sql = "CREATE TEMPORARY TABLE tmp_table_name SELECT `id`, `name`, `date`, '完成' AS `type` FROM table_A WHERE 条件语句……";

 

第二步：从table_B中取得相关记录插入临时表tmp_table_name中

复制代码 代码如下: INSERT INTO tmp_table_name SELECT `id`, `name`, `date2` AS `date`, '未完成' AS `type` FROM table_B WHERE 条件语句……

 

第三步：从临时表tmp_table_name中取出记录

?

1	SELECT * FROM tmp_table_name ORDER BY id DESC

union和order by、limit区别分析

代码示例:

?

1 2 3 4 5 6

CREATE TABLE `test1` (   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,   ` name ` varchar (20) NOT NULL ,   ` desc ` varchar (100) NOT NULL ,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. 以下查询会报错误：[Err] 1221 - Incorrect usage of UNION and ORDER BY

代码示例:

?

1 2 3

select * from test1 where name like 'A%' order by name union select * from test1 where name like 'B%' order by name

修改为：

代码示例:

?

1 2 3

select * from test1 where name like 'A%' union select * from test1 where name like 'B%' order by name

说明，在union中，不用括号的情况下，只能用一个order by(思考：union两边的order by的列名不一样时，会出现什么样的结果？),这会对union后的结果集进行排序。

修改为：

代码示例:

?

1 2 3

( select * from test1 where name like 'A%' order by name ) union ( select * from test1 where name like 'B%' order by name )

也是可以的，这两个order by在union前进行。

2. 同样

代码示例:

?

1 2 3

select * from test1 where name like 'A%' limit 10 union select * from test1 where name like 'B%' limit 20

相当于：

代码示例:

?

1 2 3

( select * from test1 where name like 'A%' limit 10) union ( select * from test1 where name like 'B%' ) limit 20

即后一个limit作用于的是union后的结果集，而不是union后的select。
也可以用括号括起来，以得到预期的结果：

3. UNION和UNION ALL区别

union会过滤掉union两边的select结果集中的重复的行，而union all不会过滤掉重复的行。

代码示例:

?

1 2 3

( select * from test1 where name like 'A%' limit 10) union ( select * from test1 where name like 'B%' limit 20)

下面试一个年龄段分析的复杂sql语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

(   SELECT    '5~19' AS `age`,    SUM (`impression`) AS impression,    SUM (`click`) AS click,    sum (`cost`) AS cost   FROM    `adgroup_age_report`   WHERE    (     (      (`age` <= 19)      AND (`adgroup_id` = '61' )     )     AND (` date ` >= '2015-11-22' )    )   AND (` date ` <= '2017-02-20' ) ) UNION   (    SELECT     '20~29' AS `age`,     SUM (`impression`) AS impression,     SUM (`click`) AS click,     sum (`cost`) AS cost    FROM     `adgroup_age_report`    WHERE     (      (       ((`age` <= 29) AND (`age` >= 20))       AND (`adgroup_id` = '61' )      )      AND (` date ` >= '2015-11-22' )     )    AND (` date ` <= '2017-02-20' )   ) UNION   (    SELECT     '30~39' AS `age`,     SUM (`impression`) AS impression,     SUM (`click`) AS click,     sum (`cost`) AS cost    FROM     `adgroup_age_report`    WHERE     (      (       ((`age` <= 39) AND (`age` >= 30))       AND (`adgroup_id` = '61' )      )      AND (` date ` >= '2015-11-22' )     )    AND (` date ` <= '2017-02-20' )   ) UNION   (    SELECT     '40~49' AS `age`,     SUM (`impression`) AS impression,     SUM (`click`) AS click,     sum (`cost`) AS cost    FROM     `adgroup_age_report`    WHERE     (      (       ((`age` <= 49) AND (`age` >= 40))       AND (`adgroup_id` = '61' )      )      AND (` date ` >= '2015-11-22' )     )    AND (` date ` <= '2017-02-20' )   ) UNION   (    SELECT     '50~59' AS `age`,     SUM (`impression`) AS impression,     SUM (`click`) AS click,     sum (`cost`) AS cost    FROM     `adgroup_age_report`    WHERE     (      (       ((`age` <= 59) AND (`age` >= 50))       AND (`adgroup_id` = '61' )      )      AND (` date ` >= '2015-11-22' )     )    AND (` date ` <= '2017-02-20' )   )

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/isuben/p/3831074.html