mysql-尝试获取多个与重复外键匹配的行作为附加列

tmb3ates 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(329)

我有一个包含以下两个表的数据库：

+------------+-------+---------+
| customer_id| name  | surname |  
+------------+-------+---------+  
| 123        | Bob   | Johnson |  
| 124        | Alice | Smith   |  
| 125        | Fred  | Rogers  |  
+------------+-------+---------+

+------------+-------------+--------------+------------+  
| address_id | customer_id | address_1    | address_2  |  
+------------+-------------+--------------+------------+  
| 1          |     123     | 123 A Street | Oneville   |  
| 2          |     124     | 321 B Street | Twoville   |  
| 3          |     124     | 42  C Street | Threeville |  
| 4          |     125     | 23  D Street | Fourville  |  
+------------+-------------+--------------+------------+

我尝试使用查询将地址表中具有相同客户id的其他行转换为这样的格式，并将额外的地址添加到行中：

+-------------+---------------+--------------+------------+--------------+------------+  
| customer_id | customer_name | address_1    | address_2  |  address_3   | address_4  |
+-------------+---------------+--------------+------------+--------------+------------+  
| 123         |     Bob       | 123 A Street | Oneville   |              |            |
| 124         |     Alice     | 321 B Street | Twoville   | 42  C Street | Threeville |
| 125         |     Fred      | 23  D Street | Fourville  |              |            |
+-------------+---------------+--------------+------------+--------------+------------+

当前我的（简化）查询如下所示：

SELECT
    c.customer_id,
    c.name AS 'customer_name',
    a.address_1,
    a.address_2,
FROM
    customer c
LEFT JOIN 
    address a ON c.customer_id = a.customer_id
GROUP BY a.customer_id

基于我在这里已经找到的内容，我尝试添加一个子查询来完成这项工作，但我认为我对它们的理解不够好，无法让它按预期的方式工作。
另外，我要感谢这个网站的贡献者让我了解了在此之前导出的极其复杂的产品数据库。网站迁移不是很有趣吗？

mysql select mysql-workbench

来源：https://stackoverflow.com/questions/53278499/mysql-trying-to-get-multiple-rows-matching-duplicate-foreign-keys-as-additiona

1条答案

按热度按时间

ppcbkaq51#

理想情况下，此类与数据显示相关的需求通常应该使用应用程序代码（例如：php、c++、java等）来解决。但是，根据您的评论，您只需要使用sql就可以了。此外，您确认您最多关注一个客户的2个地址。
在mysql版本<8.0.2中，我们可以模拟 Row_Number() 使用用户定义的变量。在派生表中，我们将获得客户的所有地址。在这个子查询中以特定的顺序获取数据是很重要的，这样外部子查询就可以利用它来计算数据分区中的行数值 customer_id 正确地。
在外部子查询中，我们将使用 CASE..WHEN 表达。在最外层的子查询中，我们将对一组 customer_id . address_1 以及 address_2 行号为1时确定；以及 address_3 以及 address_4 将在行号为2时确定

SELECT 
  dt2.customer_id, 
  dt2.customer_name, 
  MAX(CASE WHEN dt2.row_no = 1 THEN dt2.address_1 END) AS address_1,
  MAX(CASE WHEN dt2.row_no = 1 THEN dt2.address_2 END) AS address_2,
  MAX(CASE WHEN dt2.row_no = 2 THEN dt2.address_1 END) AS address_3,
  MAX(CASE WHEN dt2.row_no = 2 THEN dt2.address_2 END) AS address_4
FROM 
(
  SELECT 
    @rn := CASE WHEN @cid = dt.customer_id THEN @rn + 1
                ELSE 1
           END AS row_no, 
    @cid := dt.customer_id AS customer_id, 
    dt.customer_name, 
    dt.address_1, 
    dt.address_2
  FROM 
  (
    SELECT
      c.customer_id,
      c.name AS 'customer_name',
      a.address_1,
      a.address_2 
    FROM
      customer c
    LEFT JOIN 
      address a ON c.customer_id = a.customer_id
    ORDER BY c.customer_id, a.address_id  -- this ordering is important
  ) AS dt
  CROSS JOIN (SELECT @cid := 0, @rn := 0) AS user_init_vars -- initialize variables
) AS dt2
GROUP BY dt2.customer_id, dt2.customer_name 
ORDER BY dt2.customer_id

赞(0）回复(0）举报 2021-06-18

我来回答

mysql-尝试获取多个与重复外键匹配的行作为附加列

1条答案

相关问题

热门标签

最新问答