mysql - 排序分组依据 - 选择 V​​S View

Question

我得到了一些产品表及其图像表，比例为 1:N。为了简单起见:

create table t_Product(
ID int not null,
Name varchar(255),
primary key (ID)
);
create table t_Images(
ID int not null,
PID int not null,
IsDefault int,
primary key (ID),
foreign key (PID) references t_Product (ID)
);
insert into t_Product (ID, Name) values (1, 'test1');
insert into t_Product (ID, Name) values (2, 'test2');
insert into t_Images (ID, PID, IsDefault) values (1, 1, 1);
insert into t_Images (ID, PID, IsDefault) values (2, 1, 0);
insert into t_Images (ID, PID, IsDefault) values (3, 2, 0);
insert into t_Images (ID, PID, IsDefault) values (4, 2, 1);

现在我做出了一个选择，这正是我所需要的(选择每个产品的默认图像或第一个图像):

select
    I.*
from
(
select
    ID,
    PID,
    IsDefault
from
    t_Images
order by
    PID asc,
    IsDefault desc,
    ID asc
) I
group by
    I.PID

ID  PID IsDefault
1   1   1
4   2   1

现在我从内部选择中创建一个 View :

create view v_Images
as
select
    ID,
    PID,
    IsDefault
from
    t_Images
order by
    PID asc,
    IsDefault desc,
    ID asc 

但是选择此 View 在分组依据时不应用任何排序:

select
    VI.*
from
    v_Images VI
group by
    VI.PID

ID  PID IsDefault
1   1   1
3   2   0

但是如果我首先从 View 中选择，然后进行分组，它会再次起作用:

select
    VI2.*
from
(
    select
        VI.*
    from
        v_Images VI
) VI2
group by
    VI2.PID

ID  PID IsDefault
1   1   1
4   2   1

有人可以解释一下，为什么分组选择的结果与 View 不同，以及为什么它在双选 View 上再次起作用？

Answer 1

这是您的查询:

select I.*
from (select ID, PID, IsDefault
      from t_Images
      order by PID asc, IsDefault desc, ID asc
     ) I
group by I.PID;

尽管这种表述并不罕见，但它使用了 MySQL documentation 的构造。明确警告反对。以下是相关部分的全文(重点是我的):

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

因此，尽管该查询似乎有效，但文档对此提出了警告。我认为这是不好的做法。另外，这仅适用于 MySQL。其他数据库不允许这种构造。而且，ANSI 具有类似的功能，涉及具有“功能依赖性”的列，并且您面临着 MySQL 的 future 版本将支持这种合理功能的风险，从而使这种方法失效。

我的建议是使用合理的方法，不仅在 MySQL 中表现良好，而且还符合 ANSI 标准。像这样的东西:

select ti.*
from t_Images ti
where not exists (select 1
                  from t_images ti2
                  where ti2.pid = ti.pid and
                        (ti2.IsDefault > ti.IsDefault or
                         (ti2.IsDefault = ti.IsDefault and
                          ti2.Id < ti.Id
                         )
                        )
                 )

或者使用 substring_index()/group_concat() 进行聚合:

select pid, max(IsDefault) as IsDefault,
       substring_index(group_concat(id order by isDefault desc id asc), ',', 1) as id
from ti_images
group by pid;