当前位置：文江博客话题详情

正确的 SQL 数据表查询编写

发布于 2024-10-08 12:08:44 字数 162 浏览 0 评论 0原文

我有一个表 t1，其中包含三列 id、c1、c2、c3 和某些数字。我有一个不同的表 t2 ，其中 id,name 中的值是 c1,c2,c3 的值。

我无法编写一个查询来获取结果作为

id | c1 的名称 | c2 的名称 | c3 的名称

有人可以帮忙吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寻梦旅人 2024-10-15 12:08:45

您需要对每个值连接三次才能获得它们的名称。

SELECT T1.id, FirstJoin.Name, SecondJoin.Name, ThirdJoin.Name FROM T1
JOIN T2 FirstJoin On T1.c1 = FirstJoin.Id
JOIN T2 SecondJoinOn T1.c2 = SecondJoin.Id
JOIN T2 ThirdJoinOn T1.c3 = ThirdJoin.Id

对于蹩脚的别名，我深表歉意。

You will need to join three times on each of the values to get their name.

SELECT T1.id, FirstJoin.Name, SecondJoin.Name, ThirdJoin.Name FROM T1
JOIN T2 FirstJoin On T1.c1 = FirstJoin.Id
JOIN T2 SecondJoinOn T1.c2 = SecondJoin.Id
JOIN T2 ThirdJoinOn T1.c3 = ThirdJoin.Id

I apologize for the crappy aliasing.

回复收藏 0 原文

风吹短裙飘 2024-10-15 12:08:45

SELECT
    t1.id,
    sub1.name,
    sub2.name,
    sub3.name
FROM
    t1
        JOIN t2 AS sub1 ON t1.c1 = sub1.id
        JOIN t2 AS sub2 ON t1.c2 = sub2.id
        JOIN t2 AS sub3 ON t1.c3 = sub3.id;

但是你应该修复你的数据模型，3 列具有相同类型的数据，听起来像是一个糟糕的数据模型。做一些标准化。

SELECT
    t1.id,
    sub1.name,
    sub2.name,
    sub3.name
FROM
    t1
        JOIN t2 AS sub1 ON t1.c1 = sub1.id
        JOIN t2 AS sub2 ON t1.c2 = sub2.id
        JOIN t2 AS sub3 ON t1.c3 = sub3.id;

But you should fix your datamodel, 3 columns with the same type of data, sounds like a bad datamodel. Do some normalization.

回复收藏 0 原文

深巷少女 2024-10-15 12:08:44

一种方法是编写子查询来检索每列的名称：

select  t1.id
,       (select name from t2 where t2.id = t1.c1) as C1Name
,       (select name from t2 where t2.id = t1.c2) as C2Name
,       (select name from t2 where t2.id = t1.c3) as C3Name
from    t1

或者您可以使用 Tripe 连接：

select  t1.id
,       t2_1.name as C1Name
,       t2_2.name as C2Name
,       t2_3.name as C3Name
from    t1
join    t2 as t2_1 on t1.c1 = t2_1.id
join    t2 as t2_2 on t1.c2 = t2_2.id
join    t2 as t2_3 on t1.c3 = t2_3.id

One way would be to write subqueries to retrieve the name for each column:

select  t1.id
,       (select name from t2 where t2.id = t1.c1) as C1Name
,       (select name from t2 where t2.id = t1.c2) as C2Name
,       (select name from t2 where t2.id = t1.c3) as C3Name
from    t1

Or you could use a tripe join:

select  t1.id
,       t2_1.name as C1Name
,       t2_2.name as C2Name
,       t2_3.name as C3Name
from    t1
join    t2 as t2_1 on t1.c1 = t2_1.id
join    t2 as t2_2 on t1.c2 = t2_2.id
join    t2 as t2_3 on t1.c3 = t2_3.id

回复收藏 0 原文

~没有更多了~