mysql union查询、以及分页查询

古城微笑少年丶 2023-03-03 04:55 9阅读 0赞

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

*  查询语句
    
        SELECT
              *
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL
              UNION 
              SELECT
              *
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]

*  分页查询sql
    
        (
               SELECT
              *
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL
              )
              UNION 
              (
              SELECT
              *
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL
              ) LIMIT 10
 *  count统计sql
    
        select 
             COUNT(*)
            FROM
             ( 
              (
               SELECT DISTINCT
               b.id
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL
              )
              UNION 
              (
              SELECT DISTINCT
              b.id 
              FROM evaluate_t_dept_period_base b
              left join pmi_t_department d on (d.id = b.dept_id)
              left join evt_t_accept_flow lf on (lf.last_flow_id = b.flow_id)
              LEFT JOIN evt_t_roll_back_accept_flow df ON (df.accept_flow_id=lf.id)
              WHERE  b.eva_s_time = '2020-07-01 00:00:00' AND b.eva_e_time = '2020-07-31 23:59:59'
              and  d.dept_code LIKE CONCAT((SELECT dept_code FROM pmi_t_department WHERE id = 1),'%')
              and d.dept_type = 1 
              AND '2020-07-31 23:59:59'> DATE_ADD(b.overtime_point,INTERVAL 10 DAY)
              AND b.dept_e_time IS NULL
              ) 
             ) AS tmp

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]: /images/20230208/962438c88908467eb811628df939190d.png