主页 / coding / 问题 / 79239839
Accepted
MarkS
Asked: 2024-11-30 23:11:45 +0800 CST

将列值与 CASE WHEN 中的常量进行比较失败

  • 772

MySQL 8.0.40

以下代码完全按照预期工作。我创建了两个 CTE，每个 CTE 返回两列。然后，我将 2022 年 1 月列的值与 2021 年 1 月列的值进行比较。如果 2022 年 1 月更大，则创建一个名为“2022_is_greater”的新列并填充 1，否则填充 0。

但是，如果我尝试过滤新创建的“2022_is_greater”列，它不起作用。

ON
 Pr_2021 = Pr_2022
WHERE
 '2022_is_greater' = 1;

附加到最终结果中返回零行（应该是 18）。

为了好玩我还尝试了：

ON
 Pr_2021 = Pr_2022
WHERE
 '2022_is_greater' > x;

其中“x”是任何整数，0 或更大，它将返回全部 18 行。（事实证明，每行中 2022 年 1 月的值都更大）。但是，对于任何大于 0 的 x 值，这应该返回零行。

当我通过 WHERE 子句添加比较时，我也得到了此信息：

截断了错误的 DOUBLE 值：“2022_is_greater”

我查了一下，但不明白为什么会得到这个结果。我只是检查列中的值是否为 1。我没有执行字符串比较。

正确运行完整查询，无需在最后使用 WHERE 子句：

WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022;

在此处输入图片描述

mysql

1 个回答

  1. Best Answer

    您可以进一步创建 CTE 并将其用作基础

    WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
), S_UM  as (
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022
 FROM
 S_UM
 WHERE 2022_is_greater = 1;

    或者你可以使用HAVING

    WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022
 HAVING  2022_is_greater = 1;
    • 0

相关问题