根据多个列对结果行排序

I have to put down the lines that have two distinct fields with value = 0

我需要写出有两个不同字段值为0的直线

Let me explain, all records that have stock_it=0 and stock_eu=0 must go to the bottom of the results

让我解释一下，所有stock_it=0和stock_eu=0的记录都必须位于结果的底部

I have used this solution, however, it is not strictly correct because it puts down even rows that do not have both values = 0

我用过这个解，但是，它不是严格正确的，因为它把没有两个值都为0的行写下来

$dati = mysql_query("
    SELECT      * 
    FROM        $tb_article
    ORDER BY
        FIELD($tb_art.article_status,'n') DESC, 
        FIELD($tb_art.article_stock_it,'0') ASC, 
        FIELD($tb_art.article_stock_eu,'0') ASC,  
        $tb_art.article_rank
");

article_status=n indicates new products and they have to stay on top!
article_status=n表示新产品，它们必须保持在顶部!
Push to bottom all the article that have stock IT and stock EU = 0 (products not available)
将所有有库存和库存EU = 0的物品推到最底层(产品不可用)
In the end, order the rest of the articles according to the rank assigned
最后，按照指定的等级顺序排列文章的其余部分。

2 个解决方案

#1

If you want to use ORDER BY FIELD : SQL Fiddle

如果您想使用字段的顺序:SQL Fiddle。

MySQL 5.6 Schema Setup:

MySQL 5.6模式设置:

CREATE TABLE tb_article
(
    article_stock_it int,
    article_stock_eu int,
    article_status varchar(11),
    article_rank int
);

INSERT INTO tb_article VALUES
  (1, 1, 'n', 1)
, (0, 1, 'n', 1)
, (0, 0, 'n', 1)
, (1, 1, 'o', 1)
, (1, 1, 'o', 2);

Query 1:

查询1:

SELECT      * 
    FROM        tb_article
    ORDER BY
        FIELD(tb_article.article_status,'n') DESC, 
        FIELD(
          IF(tb_article.article_stock_it = 0 AND tb_article.article_stock_eu = 0,'1','0')
          ,'0') DESC,
       tb_article.article_rank

Results:

结果:

| article_stock_it | article_stock_eu | article_status | article_rank |
|------------------|------------------|----------------|--------------|
|                1 |                1 |              n |            1 |
|                0 |                1 |              n |            1 |
|                0 |                0 |              n |            1 |
|                1 |                1 |              o |            1 |
|                1 |                1 |              o |            2 |

#2

You can use the CASE statement in ORDER BY, like this:

您可以按如下顺序使用CASE语句:

SELECT      * 
FROM        $tb_article
ORDER BY
   CASE WHEN article_status = 'n' THEN 0
        WHEN article_stock_it = 0 THEN 100000
        WHEN article_stock_eu = 0 THEN 100000
        ELSE article_rank
   END

#1