主页 / dba / 问题 / 285315
Accepted
gstlouis
Asked: 2021-02-13 12:32:21 +0800 CST

mysql if else 以语句为条件？

  • 772

我正在尝试从数据库级别计算一些东西，并想知道是否可以使用 if/else 或 ifnull 或其他来评估它？如果不是什么是好方法？

有可能以这种方式做某事吗？

我尝试了几种方法，但这是我的最新示例。

IF (SELECT markups.markup FROM markups LIMIT 1) 
THEN
    SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1
ELSE
    SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"
mysql

2 个回答

  1. Best Answer

    您可以为此目的使用动态 sql

    if 语句 chekcm if there are markup if so 它将第一个查询记录到用户定义的变量中@sql

    并执行它（见手册）

    SELECT IF(EXISTS(SELECT markups.markup FROM markups)
, @sql:=' SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
,@sql:='SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    • 2

  2. 感谢@nbk

    在复习 multi_query() 之后，我开始执行以下操作

            $sql = " SELECT IF(EXISTS(SELECT markups.markup FROM markups)
                                , @sql:='SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'') * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'') AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
                                
                                , @sql:='SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'''); ";

        $sql .= "PREPARE stmt FROM @sql; ";
        $sql .= "EXECUTE stmt; ";
        $sql .= "DEALLOCATE PREPARE stmt; ";

        if($cnx->multi_query($sql)){
            do {
                if ($res = $cnx->store_result()) {
                    $averageQuery = $res->fetch_all(MYSQLI_ASSOC)[0];
                    $res->free();
                }
            } while ($cnx->more_results() && $cnx->next_result());
        }
    • 0

相关问题