mysql - 在不存在的情况下使用插入和嵌套选择语句的 SQL

Question

我正在做一个正在进行的工作摘要，为我的老板提供图表，他想要存储数据，以便图表可以显示去年、上季度、上个月和今天的比较。

我有几个 sql 语句可以很好地处理这个问题，但我试图合并为一个语句。

SQL:

INSERT INTO `wip_summary` (`wk_num`, `year`, `a_cnt`, `ae_cnt`, `b_cnt`, `sm_cnt`, `xx_cnt`, `tm_cnt`, `svc_cnt`, `ci_cnt`, `a_amt`, `ae_amt`, `b_amt`, `tm_amt`, `sm_amt`, `xx_amt`, `svc_amt`, `ci_amt`)
   SELECT
      WEEK(CURDATE()),
      YEAR(CURDATE()),
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 10000
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN 1 ELSE 0 END) AS `a_cnt`,
      SUM(CASE WHEN `emc` = 1 THEN 1 ELSE 0 END) AS `ae_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 3500
                  AND `c_price` < 10000
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN 1 ELSE 0 END) AS `b_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 600
                  AND `c_price` < 3500
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN 1 ELSE 0 END) AS `sm_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` < 600
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN 1 ELSE 0 END) AS `xx_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `contractInstall` = '0'
                  AND `terms` = 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN 1 ELSE 0 END) AS `tm_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `wo_type` = 'SERVICE ORDER'
                  AND `contractInstall` = '0'
          THEN 1 ELSE 0 END) AS `svc_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `contractInstall` = '1'
          THEN 1 ELSE 0 END) AS `ci_cnt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 10000
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN `c_price` ELSE 0 END) AS `a_amt`,
      SUM(CASE WHEN `emc` = 1 THEN `c_price` ELSE 0 END) AS `ae_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 3500
                  AND `c_price` < 10000
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN `c_price` ELSE 0 END) AS `b_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `contractInstall` = '0'
                  AND `terms` = 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN `c_price` ELSE 0 END) AS `tm_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` >= 600
                  AND `c_price` < 3500
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN `c_price` ELSE 0 END) AS `sm_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `c_price` < 600
                  AND `contractInstall` = '0'
                  AND `terms` <> 'TIME AND MATERIAL'
                  AND `wo_type` <> 'SERVICE ORDER'
          THEN `c_price` ELSE 0 END) AS `xx_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `wo_type` = 'SERVICE ORDER'
                  AND `contractInstall` = '0'
          THEN `c_price` ELSE 0 END) AS `svc_amt`,
      SUM(CASE WHEN `emc` = '0'
                  AND `contractInstall` = '1'
          THEN `c_price` ELSE 0 END) AS `ci_amt`
    FROM (SELECT CAST(REPLACE(REPLACE(`contract_price`, ',', ''), '$', '') AS Decimal(10,2)) AS `c_price`,
                 `emc`,
                 `contractInstall`,
                 `wo_type`,
                 `terms`
            FROM `orders`
    WHERE `auth_status` = 'ACTIVE') AS `cp_tbl`
WHERE NOT EXISTS (SELECT 1 FROM `wip_summary` WHERE `wk_num` = WEEK(CURDATE()) AND `year` = YEAR(CURDATE()));

我正在做什么:

1. 检查数据库中是否有今年本周的记录
2. 根据指定条件获取计数或求和美元金额

除非表格中包含周和年，否则它效果很好，然后我会得到一行列默认值。

所以 WHERE NOT EXISTS 是让我失望的原因，但我似乎无法弄清楚为什么或如何。

没有嵌套的 SELECT 语句，INSERT ... WHERE NOT EXISTS 可以工作，没有 INSERT ... WHERE NOT EXISTS 语句SELECT 语句有效。

Answer 1

将第二个 WHERE 替换为 AND。如果不这样做，选择将始终生成数据。如果这样做，选择将不会提供数据，因为年份/弱组合已存在于 wip_summary 中。

更改这部分:

        FROM `orders`
    WHERE `auth_status` = 'ACTIVE') AS `cp_tbl`
WHERE NOT EXISTS (SELECT 1 FROM `wip_summary` WHERE `wk_num` = WEEK(CURDATE()) AND `year` = YEAR(CURDATE()));

至

        FROM `orders`
    WHERE `auth_status` = 'ACTIVE') 
    AND NOT EXISTS 
(SELECT 1 FROM `wip_summary` WHERE `wk_num` = WEEK(CURDATE()) AND `year` = YEAR(CURDATE()))
AS `cp_tbl`;