时间戳

首页 > 解决方案 > 试图在mysql中连接三个不同的行

mysql - 试图在mysql中连接三个不同的行

问题描述

所以，我有一个属性表，我正在尝试构建一个查询以连接到一列

attributes
------------
id, c_id, key, value
1,  1,    'day', 11
2,  1,    'month', 09
3,  1,    'year', 1999
4,  2,    'day', 14
5,  2,    'month', 11
6,  2,    'year', 2004

这是我写的查询，

SELECT 
consumer_id,
CONCAT(
    (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_day'),
    '_',
    CONCAT(
        (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_month'),
        '_',
        CONCAT(
            (SELECT `value` FROM consumer_attributes WHERE `key` = 'select_year'),
            '',
            ''
        )
    )
) AS dob
FROM consumer_attributes

它抛出

错误代码：1242 子查询返回多于 1 行

有人可以帮我吗？

我试图实现的输出

consumer_id, concat
1, 11_09_1999
2, 14_11_2004

标签： mysqlconcatenation

解决方案

SELECT c_id, CONCAT_WS('_',
  (SELECT value FROM consumer_attributes a WHERE `key`='day' AND a.c_id = c.c_id),
  (SELECT value FROM consumer_attributes a WHERE `key`='month' AND a.c_id = c.c_id),
  (SELECT value FROM consumer_attributes a WHERE `key`='year' AND a.c_id = c.c_id)) AS dob
FROM (SELECT DISTINCT c_id FROM consumer_attributes) c;

https://www.db-fiddle.com/f/pB6b5xrgPKCivFWcpQHsyE/14

推荐阅读