mysql - 如何计算两组表的不同之处

Question

我有两组表，一组称为成功，另一组称为不成功，我已经做了一个联合，我希望能够对玩家姓名进行不同的计数。两个集合具有相同的列名称。

SELECT
  s.date,
  s.location,
  sn.name
FROM
  s_location s
  INNER JOIN s_players sp ON sp.p_id = s.id
  INNER JOIN s_name sn ON sn.id = sp.p_id
WHERE
  s.date BETWEEN '2018-11-16' AND '2018-11-16'
UNION
SELECT
  u.date,
  u.location,
  un.name
FROM
  u_location u
  INNER JOIN u_players up ON up.p_id = u.id
  INNER JOIN u_name un ON un.id = up.p_id
WHERE
    u.date BETWEEN '2018-11-16' AND '2018-11-16'
ORDER BY
  location

成功表位置表s_location

+-----------+----------+------------+
| id        | location | date       |
+-----------+----------+------------+
| 10        | LOCA     | 2018-11-18 |
+-----------+----------+------------+

玩家表 s_players

+-----------+----------+
| LOC_id    | p_id     |
+-----------+----------+
| 10        | 667      |
| 10        | 104      |
| 10        | 733      | 
+-----------+----------+

玩家姓名表 s_name

+-----------+----------+
| id        | name     |
+-----------+----------+
| 667       | John     |
| 104       | Peter    |
| 733       | Mary     |
+-----------+----------+

不成功的表位置表u_location

+-----------+----------+------------+
| id        | location | date       |
+-----------+----------+------------+
| 11        | LOCB     | 2018-11-18 |
+-----------+----------+------------+

玩家表 u_players

+-----------+----------+
| LOC_id    | p_id     |
+-----------+----------+
| 11        | 667      |
| 11        | 114      |
| 11        | 243      | 
+-----------+----------+

球员姓名表u_name

+-----------+----------+
| id        | name     |
+-----------+----------+
| 667       | John     |
| 114       | Sally    |
| 733       | James    |
+-----------+----------+

两组结果表

+------------+----------+------------+
| date       | location | name       |
+------------+----------+------------+
| 2018-11-18 | LOCA     | John       |
| 2018-11-18 | LOCA     | Peter      |
| 2018-11-18 | LOCA     | Mary       |
| 2018-11-18 | LOCB     | John       |
| 2018-11-18 | LOCB     | Sally      |
| 2018-11-18 | LOCB     | James      |
+------------+----------+------------+

如何对名称进行不同的计数，以便得到 5(因为 John (p_id 667) 在两组表中(成功和不成功))

我追求的结果是

+------------+------------+
| date       | name       |
+------------+------------+
| 2018-11-18 | John       |
| 2018-11-18 | Peter      |
| 2018-11-18 | Mary       |
| 2018-11-18 | Sally      |
| 2018-11-18 | James      |
+------------+------------+

Answer 1

用另一个在 name 和 date 列上具有不同的选择来包装上述查询。

SELECT DISTINCT t.date, t.name
FROM (
    SELECT
      s.date,
      s.location,
      sn.name AS name
    FROM
      s_location s
      INNER JOIN s_players sp ON sp.p_id = s.id
      INNER JOIN s_name sn ON sn.id = sp.p_id
    WHERE
      s.date BETWEEN '2018-11-16' AND '2018-11-16'
    UNION
    SELECT
      u.date,
      u.location,
      un.name AS name
    FROM
      u_location u
      INNER JOIN u_players up ON up.p_id = u.id
      INNER JOIN u_name un ON un.id = up.p_id
    WHERE
        u.date BETWEEN '2018-11-16' AND '2018-11-16'
    ORDER BY
      location
) t