如何根据其他行返回结果集

问题描述：

我有2个表格 - 包和项目。项目表包含属于包的所有项目以及位置信息。就像下面的示例表：如何根据其他行返回结果集

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

问：我想找到属于一个位置的所有“特别”套餐，如果没有特殊的包被找到，那么它应该返回属于同一个位置“一般”的软件包。

所以，

为“欧洲”：包2应该，因为它被退回是特殊的包（虽然包1也属于欧洲，但因为它的一般包装不需要）
对于“美”：包1应当返还，因为没有特殊的包

应该指出的是：“没有这样的假设，特殊的包装有更高的IDS，我只是为了清晰起见而制作了更小的表格。” - 理解 – Senseful 2010-05-05 21:29:39

@Eagle：我不明白为什么它会导致混淆，但澄清此外，软件包表有超过'n'条目。每个条目都有一个ID和一个类型。类型可以是“一般”或“特殊”。 – understack 2010-05-05 21:45:15

对不起，我误解了这个问题。我想我现在明白了。我感到困惑与枚举认为它可能包含超过2个不同的值。 – Senseful 2010-05-05 22:31:27

答

这里有两个不同的解决方案：（注：我叫枚举场“package_t YPE“）

第一溶液（通过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）。它创造的记录，如本：

location | general-package | [special-package]

它然后使用MySQL IF功能的第一次尝试选择特殊包的ID，然后回落到普通包的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)

将该溶液利用了Enum也被存储为整数的事实。它将枚举转换为整数。 special在这种情况下将返回2和general将返回1。因为在这种情况下这些特殊值保证高于一般值（即2> 1），所以我们可以使用MAX聚合函数。现在我们基本上有一张地点表和他们的“推荐套餐”（如果存在的话，一般情况下是特殊的）。我们只需将它与预期的包类型一起加入到普通查询中，并返回正确的结果。

声明：我不确定这些方法的效率，所以你可能想自己测试一下。

如果你正在寻找要么重新设计表或进行非规范化以提高效率，我觉得这样的设计可能更适合：

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

的好处是，它更容易执行

一个位置必须始终有一个大致的包（Items.general_package_id可以被定义为NOT NULL）
一个位置必须只有一个通用包（在域中添加它，而不是连接保证只有一个指定）
一个位置最多只能包含一个特殊包（将其添加到一个字段中而不是一个连接保证只有一个指定）
Items.general_package_id = GeneralPackages.id上的外键将保证该列只包含“普通”的有效包。
对于special_package_id可以做同样的事情。

缺点是您每次使用旧查询时都可能需要使用UNION ALL。

如何根据其他行返回结果集

相关推荐