时间戳

首页 > 解决方案 > 冷门产品的 SQL 查询

mysql - 冷门产品的 SQL 查询

问题描述

为了计算 MySQL 5.7 中最不受欢迎的产品，我运行以下查询：

SELECT
    ClientOrderItem.ItemId AS ID_product,
    countIf(Orders.ClientOrderStateID = 3) AS cnt
FROM
    ClientOrderItem
    LEFT JOIN Orders ON ClientOrderItem.ClientOrderID = Orders.id
    LEFT JOIN AdditionalInfo ON ClientOrderItem.ClientOrderID = AdditionalInfo.ClientOrderID
WHERE
    Orders.ClientOrderStateID = 3
    AND (
        AdditionalInfo.code = 'IsTestOrder'
        AND AdditionalInfo.value = '0'
    )
GROUP BY   cnt
ORDER BY   cnt DESC;

我收到以下错误：

SQL 错误 [1305] [42000]: FUNCTION ozon.countIf 不存在

你能告诉我用什么替换“countIf”函数吗？谢谢。

标签： mysqlsql

解决方案

正如评论中提到的 mysql 不支持'countif'，您可以遵循以下语法：

SELECT
    ClientOrderItem.ItemId AS ID_product,
    count(IF Orders.ClientOrderStateID = 3,1,NULL) AS cnt
FROM
 ...

或者

SELECT
    ClientOrderItem.ItemId AS ID_product,
    count(case when Orders.ClientOrderStateID = 3 then 1 end) AS cnt
FROM
 ...

推荐阅读