在mysql的4个表的子查询中使用外部别名

2ekbmq32 于 2021-06-21 发布在 Mysql

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

大家好，我在mysql中有4个表作为键值存储

t1 (article):          t2:
| id | date |          | id | key     | value    |
-------------          ---------------------------
| 1  | 2016 |          | 1  | title   | title1   |
| 2  | 2017 |          | 1  | user_id | 1        |
| 3  | 2018 |          | 2  | title   | title2   |
-------------          | 2  | user_id | 2        |          
                       | 3  | title   | title3   |
                       | 3  | user_id | 1        |
                       ---------------------------

t1 (user):             t2:
| id | date |          | id | key   | value    |
-------------          -------------------------
| 1  | NULL |          | 1  | name  | user1    |
| 2  | NULL |          | 2  | name  | user2    |
-------------          -------------------------          

SELECT t1.id,
    GROUP_CONCAT(IF(t2.key='title',t2.value,NULL)) AS title,
    t1.date,
    GROUP_CONCAT(IF(t2.key='user_id',t2.value,NULL)) AS user_id,

(
SELECT GROUP_CONCAT(IF(t4.key='user_name',t4.value,NULL))
FROM t4
GROUP BY t4.id
HAVING t4.id = user_id

) AS user_name

FROM t1
    INNER JOIN t2
        ON t1.id = t2.id
GROUP BY t1.id

我想打印出在t2中存储为id的用户名，如：

| id | title    | date   | user_id | user_name |
------------------------------------------------
| 1  | title1   | 2016   | 1       | user1     |
| 2  | title2   | 2017   | 2       | user2     |
| 3  | title3   | 2018   | 1       | user1     |
------------------------------------------------

我已经测试了where子句和having子句，但都不适合我。

sql mysql php Database

来源：https://stackoverflow.com/questions/48334084/use-outer-alias-in-subquery-with-4-tables-in-mysql

1条答案

按热度按时间

kzipqqlq1#

我发现您的表引用太混乱了，所以我使用了对示例数据的解释。顺便说一下，我只需要4张table中的3张。演示
mysql 5.6架构设置：

CREATE TABLE articles
    (`id` int, `date` date)
;

INSERT INTO articles
    (`id`, `date`)
VALUES
    (1, '2016-01-01'),
    (2, '2017-01-01'),
    (3, '2018-01-01')
;

CREATE TABLE users
    (`id` int, `date` date)
;

INSERT INTO users
    (`id`, `date`)
VALUES
    (1, NULL),
    (2, NULL)
;

CREATE TABLE t2_upper
    (`id` int, `key` varchar(7), `value` varchar(6))
;

INSERT INTO t2_upper
    (`id`, `key`, `value`)
VALUES
    (1, 'title', 'title1'),
    (1, 'user_id', '1'),
    (2, 'title', 'title2'),
    (2, 'user_id', '2'),
    (3, 'title', 'title3'),
    (3, 'user_id', '1')
;

CREATE TABLE t2_lower
    (`id` int, `key` varchar(4), `value` varchar(5))
;

INSERT INTO t2_lower
    (`id`, `key`, `value`)
VALUES
    (1, 'name', 'user1'),
    (2, 'name', 'user2')
;

查询1：

select a.id, tn.value article_title, a.date, tu.id user_id, u.value user_name
from articles a
inner join (
    select
    *
    from t2_upper
    where `key` = 'title'
    ) tn on a.id = tn.id
inner join (
    select
    *
    from t2_upper
    where `key` = 'user_id'
  ) tu on a.id = tu.id
inner join (
    select
    *
    from t2_lower
    where `key` = 'name'
  ) u on tu.value = u.id

结果：

| id | article_title |       date | user_id | user_name |
|----|---------------|------------|---------|-----------|
|  1 |        title1 | 2016-01-01 |       1 |     user1 |
|  2 |        title2 | 2017-01-01 |       2 |     user2 |
|  3 |        title3 | 2018-01-01 |       3 |     user1 |

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

我来回答

在mysql的4个表的子查询中使用外部别名

1条答案

相关问题

热门标签

最新问答