首页 文章

MYSQL JOIN多个表，避免重复行上的重复条目

提问于
浏览 次
0

我想SELECT JOIN三个表 . 其中两个具有唯一条目，但是一个可能有多行用于我的主表中的唯一标识符 . 我希望在我的查询结果中获得多行，如果在表3中有多个条目，但我想只获得不同的值，并且之前没有重复 . 对于一个简单的例子，它应该如下所示：

表用户：

+----------+-----------+--------------+
| user_id  | user_name | email        |
+----------+-----------+--------------+
| 1        | name1     | one@ex.tld   |
| 2        | name2     | two@ex.tld   |
| 3        | name3     | three@ex.tld |
+----------+-----------+--------------+

表信息：

+----------+---------+--------+
| info_id  | user_id | info   |
+----------+---------+--------+
| 1        | 1       | text1  |
| 2        | 2       | text2  |
| 3        | 3       | text3  |
+----------+---------+--------+

表地址：

+------------+--------+----------------+
| address_id | user_id| address_field1 |
+------------+--------+----------------+
| 1          | 1      | City A         |
| 2          | 1      | City B         |
| 3          | 2      | City C         |
| 4          | 2      | City D         |
| 5          | 2      | City E         |
| 6          | 3      | City F         |
+------------+--------+----------------+

我的实际查询是：SELECT u.user_name，u.email，i.info，a.address_field1 FROM user u INNER JOIN info i ON（u.user_id = i.user_id）INNER JOIN地址a ON（u.user_id = a . 用户身份）;

^^我尝试了LEFT JOIN和JOIN也没有成功 .

查询结果：

+-----------+--------------+--------+----------------+
| user_name | email        | info   | address_field1 |
+-----------+--------------+--------+----------------+
| name1     | one@ex.tld   | text1  | City A         |
| name1     | one@ex.tld   | text1  | City B         |
| name2     | two@ex.tld   | text2  | City C         |
| name2     | two@ex.tld   | text2  | City D         |
| name2     | two@ex.tld   | text2  | City E         |
| name3     | three@ex.tld | text3  | City F         |
+-----------+--------------+--------+----------------+

查询结果我尝试实现：

+-----------+--------------+--------+----------------+
| user_name | email        | info   | address_field1 |
+-----------+--------------+--------+----------------+
| name1     | one@ex.tld   | text1  | City A         |
|           |              |        | City B         |
| name2     | two@ex.tld   | text2  | City C         |
|           |              |        | City D         |
|           |              |        | City E         |
| name3     | three@ex.tld | text3  | City F         |
+-----------+--------------+--------+----------------+

我知道这是可能的，但我不知道如何，我不确定我应该搜索什么 . 有人可以直接把我放在右边吗？

mysql join

4 回答

  • 0

    GROUP_CONCAT与GROUP BY结合使用或多或少 .
    没有SEPARATOR选项的GROUP_CONCAT将生成逗号分隔值

    SELECT u.user_name, u.email, i.info, GROUP_CONCAT(address_field1) AS address_field1
FROM user u
INNER JOIN info i ON (u.user_id = i.user_id)
INNER JOIN address a ON (u.user_id = a.user_id)
GROUP BY u.user_name, u.email, i.info
    回复于
  • 0

    随意浏览SQLFiddle以进一步测试此查询及其输出！

    SELECT u.user_name, u.email, 
GROUP_CONCAT(info) AS info, 
GROUP_CONCAT(address_field1) AS address_field1
FROM user u
INNER JOIN info i ON (u.user_id = i.user_id)
INNER JOIN address a ON (u.user_id = a.user_id)
GROUP BY u.user_name ASC

| user_name |        email |              info |       address_field1 |
|-----------|--------------|-------------------|----------------------|
|     name1 |   one@ex.tld |       text1,text1 |        City A,City B |
|     name2 |   two@ex.tld | text2,text2,text2 | City C,City D,City E |
|     name3 | three@ex.tld |             text3 |               City F |

    谢谢！
    奥马尔

    回复于
  • 0

    我决定自定义API并将存储以下几个地址数据：

    +-----------+--------------+--------+----------------+----------------+----------------+
| user_name | email        | info   | address_field1 | address_field2 | address_field3 |
+-----------+--------------+--------+----------------+----------------+----------------+
| name1     | one@ex.tld   | text1  | City A         | City B         |                |
| name2     | two@ex.tld   | text2  | City C         | City D         | City E         |
| name3     | three@ex.tld | text3  | City F         |                |                |
+-----------+--------------+--------+----------------+----------------+----------------+
    回复于
  • 0

    使用标准SQL中的 LAG 或 ROW_NUMBER 可以轻松地抑制重复数据，但MySQL也没有 .

    你可以做的是再次在地址表中查找第一个地址：

    select
  case when is_first then user_name end as user_name,
  case when is_first then email end as email,
  case when is_first then info end as info,
  address_field1
from
(
  select 
    u.user_name,
    u.email,
    i.info,
    a.address_field1,
    a.address_field1 = (select min(address_field1)
                        from address fa 
                        where fa.user_id = a.user_id) as is_first
  from user u 
  join info i on u.user_id = i.user_id
  join address a on u.user_id = a.user_id
) compared
order by user_name, email, info, address_field1;
    回复于

相关问题