如何在mysql中创建带查询表格的视图

原创已于 2023-10-18 10:19:53 修改 · 243 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

于 2023-06-07 09:58:45 首次发布

文章描述了如何在SQL中创建一个函数`sbid()`用于后台传值，并基于此函数创建一个视图`getnouse`，该视图通过多表联接操作获取并处理特定的数据。视图的查询逻辑中包含了对空值的处理和特定条件的过滤（如`sbid()`函数的使用和`NOTIN`子查询）。

1：先创建函数，主要是用来进行后台传值操作

CREATE FUNCTION `sbid`() RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_bin
NO SQL
DETERMINISTIC
return @sbid

2：创建视图(就是在sql语句中加上函数)

CREATE VIEW getnouse AS select * from (SELECT
           x2.wlmc,
           x2.ggxh,
           x2.dw,
           x1.xshth,
           x1.sbbh,
           cgdmx.hsdj,
           x3.fbillstatus,
           ( x2.zsl ) AS zsl,
           ( CASE WHEN ISNULL( x2.bcdhl ) THEN 0 ELSE x2.bcdhl END ) AS bcdhl,
           ( CASE WHEN ISNULL( x2.cksyl ) THEN 0 ELSE x2.cksyl END ) AS cksyl,
           ( CASE WHEN ISNULL( x2.bccksl ) THEN 0 ELSE x2.bccksl END ) AS bccksl,
           cgdmx.ncczbz,
           x2.remark,
           x2.id
       FROM
           yw_wlrcd x1
           LEFT JOIN yw_wlrcd_mx x2 ON x2.wlrcdid = x1.id
           LEFT JOIN yw_wlcgd x21 ON x1.wlqgdid = x21.id
           LEFT JOIN yw_wlcgd_mx cgdmx ON cgdmx.wlcgdid = x21.id
           AND cgdmx.wlmc = x2.wlmc
           AND cgdmx.ggxh = x2.ggxh
           LEFT JOIN yw_clqgd x3 ON x21.clqgdid = x3.id
           LEFT JOIN xm_sbxx xsb ON xsb.xmfileid = x3.xmfileid
           AND x3.sbid = xsb.id
       WHERE
           xsb.id =sbid()
       GROUP BY
           x2.wlmc,
           x2.ggxh,
           x2.dw,
           x2.cpbh,
           x1.xshth,
           x2.remark ) as a where a.id not in (
   SELECT
       x1.glid
   FROM
       yw_crkjl x1
       LEFT JOIN yw_wlrcd_mx x2 ON x1.glid = x2.id
       LEFT JOIN yw_wlrcd x3 ON x3.id = x2.wlrcdid
       LEFT JOIN yw_wlcgd x21 ON x3.wlqgdid = x21.id
       LEFT JOIN yw_wlcgd_mx cgdmx ON cgdmx.wlcgdid = x21.id
       AND cgdmx.wlmc = x2.wlmc
       AND cgdmx.ggxh = x2.ggxh
       LEFT JOIN yw_clqgd x4 ON x21.clqgdid = x4.id
       LEFT JOIN xm_sbxx xsb ON xsb.xmfileid = x4.xmfileid
       AND x4.sbid = xsb.id
   WHERE
       x1.ywmc LIKE '物料出库%'
   AND xsb.id =sbid()
   )