php - 无法让 Order by Case 在 Mysql 中工作

Question

这个问题已经有答案了:

MYSQL ORDER BY CASE Issue (4 个回答)

已关闭 6 年前。

我看到了另一个问题的答案，这就是我开始的方式，但我无法让我的代码工作。我认为这与 (Price > '0.00') 有关，因为 mysql 不断给我这个错误，

"#1064 - 您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，了解在 'ORDER BY Price = 0, Price asc, Style asc,Manufacturer asc END WHEN 附近使用的正确语法((价格 <= ' 在第 8 行"。

<?php 
$order = mysqli_query($con, "SELECT DivisionOf, Manufacturer, Style, Include,
Fiber, Width, Backing
FROM CarpetInfo

WHERE (DivisionOf='Mohawk') AND (Manufacturer IN ('Aladdin'))
AND CASE
WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes')
THEN ORDER BY Price = 0, Price asc, Style asc, Manufacturer asc
END

WHEN ((Price <= '0.00') OR (Price > '49.95') OR (ShowPrice!='Yes'))
THEN ORDER BY Price = 0, Style asc, Manufacturer asc
END
");
$result = mysqli_fetch_row($order);

mysqli_data_seek($order, 0);
while($info = mysqli_fetch_array( $order ))
include($_SERVER['DOCUMENT_ROOT'].$info['Include']);?>

Answer 1

跟进我的评论。

1. 您不能将整个 ORDER BY 子句放入 CASE 语句中
2. 每个 CASE 允许有一个 END(在 case 语句的末尾)

相反，类似下面的内容应该有效:

SELECT DivisionOf, Manufacturer, Style, Include,Fiber, Width, Backing
FROM CarpetInfo
WHERE (DivisionOf='Mohawk') AND (Manufacturer IN ('Aladdin'))
ORDER BY
    /*sort field 1*/
    CASE
        WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes')
            THEN Price
        WHEN ((Price <= '0.00') OR (Price > '49.95') OR (ShowPrice!='Yes'))
            THEN Style
    END asc,
    /*sort field 2*/
    CASE
        WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes')
            THEN Style
        WHEN ((Price <= '0.00') OR (Price > '49.95') OR (ShowPrice!='Yes'))
            THEN Manufacturer
    END asc,
    /*sort field 3*/
    CASE
        WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes')
            THEN Manufacturer       
    END asc;

这有点难看，因为您只能从 case 语句返回一个字段，然后在 END 为该一个 ORDER BY 字段指定方向后。

另外，虽然我不熟悉你的数据。我认为这些 CASE 语句可以使用 ELSE 进行简化:

SELECT DivisionOf, Manufacturer, Style, Include,Fiber, Width, Backing
FROM CarpetInfo
WHERE (DivisionOf='Mohawk') AND (Manufacturer IN ('Aladdin'))
ORDER BY
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Price ELSE Style END asc,
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Style ELSE Manufacturer asc,
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Manufacturer END asc;

<小时/>

更新:因为我们混合了字段类型，所以在某些情况下您最终会得到字典排序而不是数字排序。一些关于 case 语句的放屁应该会有所帮助:

SELECT DivisionOf, Manufacturer, Style, Include,Fiber, Width, Backing
FROM CarpetInfo
WHERE (DivisionOf='Mohawk') AND (Manufacturer IN ('Aladdin'))
ORDER BY
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Price ELSE 0 END asc,
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN NULL ELSE Style END asc,
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Style ELSE Manufacturer asc,
    CASE WHEN (Price > '0.00') AND (Price <= '49.95') AND (ShowPrice='Yes') THEN Manufacturer END asc;

因此，现在第一个排序是 price 中的值或 0，然后 style 移动到第二个排序顺序。你必须胡思乱想才能让它按你的意愿工作。