mysql - 比较两个数字并得出真或假？

Question

select *, COUNT(*), DATE_FORMAT(CREATE_DATE,'%m-%Y') AS form_date
from incident_view
where (create_month = month(NOW() - INTERVAL 1 MONTH)
       and (create_year =  year(NOW() - INTERVAL 1 MONTH)))  
   OR (create_month = month(NOW() - INTERVAL 2 MONTH)
       and (create_year = year(NOW() - INTERVAL 2 MONTH)))
AND CUSTOMER_COMPANY_NAME = "Company"
GROUP BY CREATE_MONTH

大家好，

我上面的查询工作正常。

我得到的结果是一些行，但重要的行是:

COUNT(*)  | form_date
667          01-16
1964         02-16

我想知道是否可以比较最近两个月的两次计数，是否上个月(02-16)> 上第二个月(01-16)。如果 02-16 > 01-16 我希望结果为 true，否则为 false。

非常感谢任何帮助。

问候。

Answer 1

在计算计数时，不要按月/年分组，而是在 CASE 或 IF 中使用它。

SELECT SUM(IF(create_month = MONTH(NOW() - INTERVAL 1 MONTH)
              AND create_year = YEAR(NOW() - INTERVAL 1 MONTH), 1, 0)) > 
       SUM(IF(create_month = MONTH(NOW() - INTERVAL 2 MONTH)
              AND create_year = YEAR(NOW() - INTERVAL 2 MONTH), 1, 0)) AS count_higher
FROM incident_view
WHERE customer_company_name = "Company"

如果您想要大于、等于或小于 3 个不同的结果，最好的方法是在子查询中计算总和，以便您可以命名它们并使用 CASE 返回不同的值对于每种情况。

SELECT CASE WHEN last_month > prev_month THEN 1
            WHEN last_month = prev_month THEN 2
            ELSE 0
       END AS diff
FROM (
    SELECT SUM(IF(create_month = MONTH(NOW() - INTERVAL 1 MONTH)
                  AND create_year = YEAR(NOW() - INTERVAL 1 MONTH), 1, 0)) AS last_month, 
           SUM(IF(create_month = MONTH(NOW() - INTERVAL 2 MONTH)
                  AND create_year = YEAR(NOW() - INTERVAL 2 MONTH), 1, 0)) AS prev_month
    FROM incident_view
    WHERE customer_company_name = "Company"
) AS subquery