预订GROUP BY语句 [英] Pre-ordering a GROUP BY statement

问题描述

这是上一个问题的后续内容:按订单分组.

This is a follow-up to a previous question: GROUP BY ordering.

我有以下sql表和数据: http://sqlfiddle.com/#！9/81c3b6/2/0 .

I have the following sql table and data: http://sqlfiddle.com/#!9/81c3b6/2/0.

基本的无序SQL语句是:

The basic un-ordered SQL statement is:

SELECT territory_id, platform_type_id, store_url
FROM main_itemmaster
GROUP BY platform_type_id

我想为表中的每个platform_type_id获取一个条目，并优先选择territory_id='US'.也就是说，如果在territory_id='US'位置存在一个条目，我想在GROUP BY语句中抓住该条目.

I want to get a single entry for each platform_type_id that is in the table, with a preference for territory_id='US'. That is, if an entry exists where territory_id='US', I would like to grab that one in the GROUP BY statement.

什么是正确的SQL语句，以便首先返回优先于美国商品的GROUPED BY语句?

What would be the correct SQL statement to return the GROUPed by statement with a preference for the US item first?

推荐答案

在MySQL中，最安全的方法可能涉及变量:

In MySQL, the safest way to do this probably involves variables:

select im.*
from (select im.*,
             (@rn := if(@p = platform_type_id, @rn + 1,
                        if(@p := platform_type_id, 1, 1)
                       )
             ) as rn
      from main_itemmaster im cross join
           (select @rn := 0, @p := '') params
      order by platform_type_id, (territory_id = 'US') desc
     ) im
where rn = 1;

这不涉及使用MySQL(错误)功能，该功能允许聚合查询的SELECT中的列不聚合且不在GROUP BY中.

The does not involve using the MySQL (mis)feature that permits columns in the SELECT of an aggregation query that are not aggregated and not in the GROUP BY.

此处是显示它正常工作的SQL提琴.

Here is a SQL Fiddle showing it working.

关于主题的变量评估顺序.从文档:

On the subject of the order of evaluation of variables. From the documentation:

作为一般规则，除了SET语句中的内容外，切勿 为用户变量分配一个值，并在同一变量中读取该值 陈述.例如，要增加变量，可以:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

对于其他语句，例如SELECT，您可能会得到结果 期望，但是并不能保证.在下面的语句中，您 可能认为MySQL首先会评估@a然后执行 作业第二:

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

但是，涉及用户的表达式的求值顺序 变量未定义.

However, the order of evaluation for expressions involving user variables is undefined.

从技术上讲，上面的代码确实在相同的语句中读取变量，但在相同的 expression 中也是如此. if()(有时也使用case)的语义保证了表达式的求值顺序.

The above code does, technically, read the variable in the same statement, but it is also in the same expression. The semantics of if() (and case which I sometimes also use) guarantee the order of evaluation of the expressions.

这篇关于预订GROUP BY语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！