如何基于其他行返回结果集 [英] How to return result set based on other rows

问题描述

我有2张桌子-包装和物品.物料表包含所有属于包装的物料以及位置信息.就像下面的示例表:

I've 2 tables - packages and items. Items table contains all items belonging to the packages along with location information. Like following sample tables:

Packages table
id, type(enum{general,special})
1, general
2, special

Items table
id, package_id, location
1, 1, America
2, 1, Europe
3, 2, Europe

问题:我想查找属于某个位置的所有特殊"软件包，如果未找到特殊软件包，则它应返回属于同一位置的一般"软件包.

Question: I want to find all 'special' packages belonging to a location and if no special package is found then it should return 'general' packages belonging to same location.

所以

1. 欧洲":由于包装2是特殊包装，因此应返回包装2(尽管包装1也属于欧洲，但由于是普通包装而并非必需)

1. for 'Europe' : package 2 should be returned since it is special package (Though package 1 also belongs to Europe but not required since its a general package)

美国":由于没有特殊包装，应返回包装1

for 'America' : package 1 should be returned since there are no special packages

推荐答案

以下是两种不同的解决方案:(注意:我将枚举字段称为"package_type")

Here are two different solutions: (Note: I called the enum field "package_type")

第一个解决方案(通过IF()函数):

select 
  i.location, 
  if(ps.id is not null, ps.id, pg.id) as package_id
from 
  (select distinct location from Items) i
  inner join 
    (select i.location, p.id
     from Items i
       inner join Packages p on (i.package_id = p.id and p.package_type = 'general')
    ) pg on (i.location = pg.location)
  left join 
    (select i.location, p.id
     from Items i
       inner join Packages p on (i.package_id = p.id and p.package_type = 'special')
    ) ps on (i.location = ps.location)

此解决方案本质上采用这些位置，并将其与常规(假定存在；因此为inner join)和特殊软件包(为可选；因此为left join)一起添加到软件包中.它创建如下记录:

This solution essentially takes the locations and joins it to the package with general (which is assumed to exist; hence inner join) and special package (which is optional; hence left join). It creates records such as this:

location | general-package | [special-package]

然后使用MySQL IF函数首先尝试选择特殊软件包的ID，然后回退到常规软件包的ID.

It then uses the MySQL IF function to first attempt to choose special package's ID, and then falls back to the general package's ID.

第二个解决方案(通过将枚举转换为整数):

select i.location, p.id
from
  (select i.location, max(cast(package_type as unsigned)) as package_type
   from Items i
     left join Packages p on (i.package_id = p.id)
   group by location
  ) i
  inner join 
    (select i.location, p.id, p.package_type
     from Items i
       inner join Packages p on (i.package_id = p.id)
    ) p on (i.location = p.location and i.package_type = p.package_type)

此解决方案利用了枚举存储为整数的事实.它将枚举转换为整数.在这种情况下，special将返回2，而general将返回1.由于在这种情况下(保证2> 1)保证这些特殊值高于常规值，因此我们可以使用MAX聚合函数.现在，我们基本上有了一个位置及其推荐包装"的表格(即特殊的，如果有的话，否则是普通的).我们只需将其与期望的包类型一起加入到普通查询中，即可返回正确的结果.

This solution exploits the fact that enums are stored as integers. It casts the enum to an integer. special in this case will return 2 and general will return 1. Because these special is guaranteed to be higher than general in this case (i.e. 2 > 1), we can use the MAX aggregate function. Now we essentially have a table of the locations and their "recommended package" (i.e. special if it exists, general otherwise). We simply join this to the normal query along with the expected package type, and it returns the correct results.

免责声明:我不确定这两种方法的效率，因此您可能需要自己进行测试.

如果您要重新设计表格或将其标准化以提高效率，我认为这种设计可能更合适:

If you are looking to either redesign the table or to denormalize it for efficiency, I think this design may be more suitable:

GeneralPackages table
id, name
1, General Package 1

SpecialPackages table
id, name
1, Special Package 1
2, Special Package 2

Items table
id, general_package_id, special_package_id, location
1, 1, NULL, America
2, 1, 2, Europe

优点是在数据库级别更容易实施一些规则:

The advantage would be that it is easier to enforce several rules at the database level:

• 位置必须始终具有常规包(Items.general_package_id可以定义为NOT NULL)
• 一个位置必须只有一个通用程序包(将其添加到字段中而不是联接中可以保证只指定了一个)
• 一个位置最多只能有一个特殊的程序包(将其添加到字段中而不是联接中可以保证只指定了一个)
• Items.general_package_id = GeneralPackages.id上的外键将确保该列仅包含常规"有效软件包.
• 对special_package_id可以做同样的事情.

缺点是，每次使用一个旧查询时，您可能都需要使用UNION ALL.

The disadvantage would be that you would probably need to use a UNION ALL every time you use one of your old queries.

这篇关于如何基于其他行返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！