尝试将一个表中的多个值相乘

8wigbo56 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(215)

我已经花了一天的大部分时间阅读和试图弄明白这一点。当长x宽x高=>4000时，我想显示post\u id的列表
表中的meta_值是longtext类型，因此必须在执行任何乘法之前对其进行强制转换。我想可能我的sql操作顺序错了。我已经有一段时间没做这件事了。

SELECT post_id, meta_key
FROM postmeta
WHERE (
        DECLARE @temp_length = (SELECT meta_value
                              FROM postmeta
                             WHERE meta_key = '_length'),
         @temp_width = (SELECT meta_value
                          FROM postmeta
                         WHERE meta_key = '_width'),

         @temp_height = (SELECT meta_value
                          FROM postmeta
                         WHERE meta_key = '_height')
        AND

    (CAST(@temp_length AS UNSIGNED) * CAST(@temp_width AS UNSIGNED) * CAST(@temp_height AS UNSIGNED) > 250))
ORDER BY post_id;

我得到错误：

MySQL said: Documentation

# 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @temp_length = (SELECT meta_value

                                  FRO' at line 4

mysql

来源：https://stackoverflow.com/questions/51252656/trying-to-multiply-several-values-from-a-table

1条答案

按热度按时间

xlpyo6sf1#

使用一个查询，将每个值放入一个单独的列中。然后使用 HAVING 根据产品进行过滤。

SELECT post_id,
    CAST(MAX(CASE WHEN meta_key = '_length' THEN meta_value END) AS UNSIGNED) AS temp_length,
    CAST(MAX(CASE WHEN meta_key = '_width' THEN meta_value END) AS UNSIGNED) AS temp_width,
    CAST(MAX(CASE WHEN meta_key = '_height' THEN meta_value END) AS UNSIGNED) AS temp_height
FROM postmeta
GROUP BY post_id
HAVING temp_length * temp_width * temp_height >= 4000

赞(0）回复(0）举报 2021-06-21

我来回答

尝试将一个表中的多个值相乘

1条答案

相关问题

热门标签

最新问答