UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。否则会报错。 union和 union all的关系和区别: 1.UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。 2.即 :union会对合并的两个或多个查询的结果集 进行 去重合并的操作。 union all则不会去重,只做合并,因此查出的结果集中 会有重复的记录
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;
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