mysql union查询，实现分页查询和count统计

本文主要是介绍mysql union查询，实现分页查询和count统计，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

说明：

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。否则会报错。


union和 union all的关系和区别：
    1.UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。
    
    2.即 ：union会对合并的两个或多个查询的结果集 进行 去重合并的操作。
            union all则不会去重，只做合并，因此查出的结果集中 会有重复的记录

示例：

分页查询sql

 1 ( 
 2     SELECT DISTINCT
 3         c.u_id,
 4         c.create_time
 5  
 6     FROM
 7         表A a
 8         LEFT JOIN 表B b ON b.u_id = a.u_id 
 9         AND b.yn = 1
10         LEFT JOIN 表C c ON c.u_id = a.u_id 
11         AND c.yn = 1
12         LEFT JOIN 表D d ON d.u_id = a.u_id 
13         AND d.yn = 1 
14     WHERE
15         1 = 1 
16         AND a.yn = 1 
17         AND b.vender_id IN ( 1 ) 
18         AND b.join_behavior = 1 
19         AND b.store_join_type = 1 
20     ) UNION
21     (
22     SELECT DISTINCT
23         c.u_id,
24         c.create_time
25         
26     FROM
27         表A a
28         LEFT JOIN 表B b ON b.u_id = a.u_id
29         LEFT JOIN 表C c ON c.u_id = a.u_id
30         LEFT JOIN 表D d ON d.u_id = a.u_id 
31     WHERE
32         1 = 1 
33         AND a.yn = 1 
34         AND b.vender_id IN ( 1 ) 
35         AND b.join_behavior = 1 
36         AND a.join_value IN ( 6 ) 
37         AND b.store_join_type = 2 
38     ) 
39 ORDER BY
40     create_time DESC 
41     LIMIT 0,200;

count统计sql:

 1 select 
 2      COUNT(*)
 3 FROM
 4 (
 5     ( SELECT DISTINCT
 6     c.u_id,
 7     c.create_time
 8  
 9     FROM
10         表A a
11         LEFT JOIN 表B b ON b.u_id = a.u_id 
12         AND b.yn = 1
13         LEFT JOIN 表C c ON c.u_id = a.u_id 
14         AND c.yn = 1
15         LEFT JOIN 表D d ON d.u_id = a.u_id 
16         AND d.yn = 1 
17     WHERE
18         1 = 1 
19         AND a.yn = 1 
20         AND b.vender_id IN ( 1 ) 
21         AND b.join_behavior = 1 
22         AND b.store_join_type = 1 
23     ) UNION
24     (
25     SELECT DISTINCT
26         c.u_id,
27         c.create_time
28         
29     FROM
30         表A a
31         LEFT JOIN 表B b ON b.u_id = a.u_id
32         AND b.yn = 1
33         LEFT JOIN 表C c ON c.u_id = a.u_id
34         AND c.yn = 1
35         LEFT JOIN 表D d ON d.u_id = a.u_id 
36         AND d.yn = 1
37     WHERE
38         1 = 1 
39         AND a.yn = 1 
40         AND b.vender_id IN ( 1 ) 
41         AND b.join_behavior = 1 
42         AND a.join_value IN ( 6 ) 
43         AND b.store_join_type = 2 
44     ) 
45  
46 )
47 AS temp

这篇关于mysql union查询，实现分页查询和count统计的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！