mysql通过view实现组合数据的翻页

1 问题背景

• 一个表格展现的数据，涉及多张表，还有分组的概念在其中
• 另外，需要关键字搜索
• 需要分页（只要是多表分页，view就跑不掉）

2 总结一下涉及的mysql关键字

• 视图创建

  create view view_name as (查询数据语sql)

• 左连接:确保左侧数据存在，右侧可能为null

  left join

• 双连接: 这一步，主要用来过滤条件，某张表某个字段不能为null

  join

• union all : 另一部分的数据，需要相关条件满足后，再分组

• left join need_table_name where id is null

  为了满足union, 所以强制关联了相关表，使字段能够对上

• 多字段条件判断

  where (column_1, column_2) not in ( select column1,coolumn2 from table_name)

3 感慨

mysql本身就很强大，还有太多需要学习，保持敬畏

4 本次的view sql语句

      create or replace view vw_assigned_solution_process_with_business_process as(
      select
          bs2bp.business_process_id as business_process_id_init,
          bs2bp.business_scenario_id,
          bs2bp.idx as business_process_idx,
          bpg.date_created as business_process_group_date_created,
          ss.id as solution_scenario_id,
          sp.*,
          sp.id,sp.change_by,sp.create_by,sp.date_created,
          sp.is_deleted as is_deleted,
          sp.last_updated,sp.version,sp.content_version,
          sp.diagram_id,sp.is_generic,sp.languages,
          sp.stable_id, sp.status,sp.code,sp.countries,
          sp.description, sp.industries,sp.name,sp.company_id,
          sp.master_bom_id, sp.source,sp.products,sp.release_version,
          sp.business_process_id,sp.job_id,
          ss2sp.id as solution_scenario2solution_process_id
          from business_scenario2business_process bs2bp
          left join business_process bp on bp.id = bs2bp.business_process_id
          left join business_process_group bpg on bpg.id = bp.business_process_group_id
          left join solution_scenario ss on ss.business_scenario_id = bs2bp.business_scenario_id
          left join solution_process sp on sp.business_process_id = bs2bp.business_process_id
          join solution_scenario2solution_process  ss2sp on ss2sp.solution_scenario_id = ss.id and ss2sp.solution_process_id = sp.id
      union all
      (select
          bs2bp.business_process_id as business_process_id_init,
          bs2bp.business_scenario_id,
          bs2bp.idx as business_process_idx,
          bpg.date_created as business_process_group_date_created,
          ss.id as solution_scenario_id,
          bp.id,sp.change_by,sp.create_by,sp.date_created,
          bp.is_deleted as is_deleted,
          sp.last_updated,bp.version,sp.content_version,
          sp.diagram_id,bp.is_generic,sp.languages,
          sp.stable_id, sp.status,sp.code,sp.countries,
          sp.description, sp.industries,sp.name,sp.company_id,
          sp.master_bom_id, sp.source,sp.products,sp.release_version,
          sp.business_process_id,sp.job_id,
          ss2sp.id as solution_scenario2solution_process_id
          from business_scenario2business_process bs2bp
          left join business_process bp on bp.id = bs2bp.business_process_id
          left join business_process_group bpg on bpg.id = bp.business_process_group_id
          left join solution_scenario ss on ss.business_scenario_id = bs2bp.business_scenario_id
          left join solution_process sp on sp.id is null
          left join solution_scenario2solution_process ss2sp on ss2sp.id is null
          where (ss.id,bp.id) not in (
          select ss2sp.solution_scenario_id,bp.id
          from solution_scenario2solution_process ss2sp
          join solution_process sp on sp.id = ss2sp.solution_process_id
          join business_process bp on bp.id = sp.business_process_id)
      )
  );