mysql - 2 个联合查询之间的差异

Question

以下两个查询有什么区别？ (它们都产生相同的结果)

select *
from (
  select * from (
           select *
           from phppos_items
           where name like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
           where item_number like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
           where category like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  ) as top_rows
order by `name` limit 16

对比

select *
from (
           (select *
           from phppos_items
           where name like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  union
           (select *
           from phppos_items
           where item_number like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  union
           (select *
           from phppos_items
           where category like 'AB10LA2%' and deleted = 0
           order by `name` limit 16)
  ) as top_rows
order by `name` limit 16

Answer 1

第一个版本有另一组临时表，在这种特定情况下没有用，而且浪费资源。
以下所有内容都会产生相同的结果:

SELECT * FROM T1;

SELECT * FROM (SELECT * FROM T1);


SELECT * FROM (SELECT * FROM (SELECT * FROM T1));

...
...

到无穷大:-)

在您的具体情况下，这就足够了:

select *
           from phppos_items
           where 
                 (name like 'AB10LA2%' OR item_number like 'AB10LA2%' OR category like 'AB10LA2%')
           and deleted = 0
           order by `name` limit 16