mysql union查询、以及分页查询

最新推荐文章于 2024-07-28 02:51:25 发布

王子様~

最新推荐文章于 2024-07-28 02:51:25 发布

阅读量1.1k

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_31424825/article/details/107681430

版权

mysql 专栏收录该内容

58 篇文章 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

在这里插入图片描述

分页查询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