mysql - 有没有更有效的方法来构建没有联合和重复语句的查询？

Question

我有一个查询，基本上可以找到有资格购买特定产品的所有成员及其家人。每个家庭成员都列在单独的行中，即使他们属于同一个家庭。我们使用的是 MySQL。

例如(实际返回的所有数据的一小部分):

ID  | familyid |  firstname | lastname | address      | city      | relationship
==============================================================================
1   |    1     |   john     |  davis   | 942 James Ln | cityplace | primary
2   |    1     |   suzy     |  davis   | 942 James Ln | cityplace | spouse
3   |    2     |   andrew   |  smith   | 444 A Rd     | new york  | primary
4   |    3     |   Mike     |  lewis   | 123 Street   | dallas    | primary
5   |    3     |   Donna    |  lewis   | 123 Street   | dallas    | child
1   |    3     |   Jamie    |  lewis   | 123 Street   | dallas    | child

上表中的(编造的)数据来自两个表:

1. Primary Information (contains all general information about member and address)
2. Family Information (contains spouse and children's name and gender information)

为简单起见，我们假设这些表中包含的唯一信息如下:

成员

ID | familyid | firstname | lastname | address | city
-----------------------------------------------------

家庭

ID | familyid | firstname | lastname | type | gender
------------------------------------------------------

我们系统中的成员(member)可以选择多个产品(信息位于单独的表格中)。不幸的是，我知道构建上述语句的唯一方法是基本上选择相同的信息两次，然后执行 UNION ALL 语句，如以下示例语句:

SELECT r.* FROM (
  SELECT familyid, firstname, lastname, address, city, 'primary'
  FROM member
 UNION ALL
  SELECT a.familyid, b.firstname, b.lastname, a.address, a.city, IF(b.type = 1, 'spouse', 'child')
  FROM member a
  JOIN family b ON (a.familyid = b.familyid)
) r

这已经有点乱了，但是当我开始将其他几个表加入到已收集的“名称”信息中时，“真正”的问题就来了。例如，我可以在整个查询中添加一个连接语句，以仅显示选择 HBO 电视服务的成员或家庭:

after last statement: JOIN channels c ON (r.familyid = c.familyid)

这将清除先前选择的语句中没有 HBO 的所有成员，但如果我想选择 HBO 的子部分(例如，只有成员版本与整个家庭版本的成员)怎么办？这些后续查询基本上迫使我进行两次完全相同的调用，只返回略有不同的信息。

SELECT r.* FROM (
  SELECT familyid, firstname, lastname, address, city, 'primary'
  FROM member m
  JOIN channels c ON (m.familyid = c.familyid)
  WHERE c.name = "HBO" and c.memtype = "member only"
 UNION ALL
  SELECT a.familyid, b.firstname, b.lastname, a.address, a.city, IF(b.type = 1, 'spouse', 'child')
  FROM member a
  JOIN family b ON (a.familyid = b.familyid)
  JOIN channels c ON (b.familyid = c.familyid)
  WHERE c.name = "HBO" and c.memtype = "family"
) r

有没有办法更好地优化这些语句？在这个问题上，MySQL 只是 SOL 吗？

注意:表格按原样标准化，因此请不要建议改进它们。此特定查询仅适用于每月自动生成一次并根据需要(很少见)生成的单个报告。我只是想找出是否有可能避免 Union 语句与模式的原样。架构无法修改。

Answer 1

您似乎在使用 member 表来存储家庭的“主要”成员和家庭地址，并使用 family 表来存储有关休息的人在一个家庭。这似乎设计得不太好，名称也选得不太好。

建议:将所有“主要”成员也添加到 family 表中，使用新的第三个 type:

INSERT INTO family
  (family_id, type, firstname, lastname)
SELECT family_id, 2, firstname, lastname
FROM member ;

然后您可以对 r 查询使用简单的连接:

SELECT a.familyid, b.firstname, b.lastname, a.address, a.city, 
       CASE b.type 
         WHEN 0 THEN 'child'
         WHEN 1 THEN 'spouse'
         WHEN 2 THEN 'primary'
       END AS type
FROM member a
  JOIN family b 
    ON a.familyid = b.familyid ;

您甚至可以从 member 表中删除 firstname 和 lastname 列(并且可能重命名表以更好地反射(reflect)它们的用法)。

---

第二种情况可以写成:

SELECT a.familyid, b.firstname, b.lastname, a.address, a.city, 
       CASE b.type 
         WHEN 0 THEN 'child'
         WHEN 1 THEN 'spouse'
         WHEN 2 THEN 'primary'
       END AS type
FROM member a
  JOIN family b 
    ON a.familyid = b.familyid
  JOIN channels c 
    ON b.familyid = c.familyid
WHERE c.name = 'HBO' AND ( b.type <> 2 AND c.memtype = 'family'  
                        OR b.type  = 2 AND c.memtype = 'member only'
                         ) ;