我需要根据另一个表选择具有延期值的两次同一列名称

发布于 2025-01-31 19:18:32 字数 516 浏览 4 评论 0 原文

我创建了2个表第一桌“ storages” 注意ID是pk

第二表“事务”，其中字段名称为（id，source，qty，destination）还指出ID是PK

我的SQL语句没有给我以下内容，

Select Storages.Name as 'From', Storages.Name as 'To'
from Storages,
     Transactions
where Storages.Id = Transactions.Source
  and Storages.Id = Transactions.Destination

我还需要显示结果，即使它并没有不同

原文

I created 2 tables
1st table "Storages"
noting that Id is PK

2nd table "Transactions" where fields names are (Id, Source, Qty, Destination)
Also noting that Id is PK

My SQL statement gave me nothing as follow

Select Storages.Name as 'From', Storages.Name as 'To'
from Storages,
     Transactions
where Storages.Id = Transactions.Source
  and Storages.Id = Transactions.Destination

I need to display the result even it is not distinct

分享到QQ

分享到微博

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

发布评论

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

蹲墙角沉默 2025-02-07 19:18:32

您可以使用两个 JOIN 获得预期结果，一个在交易来源上，一个在目的地上：

SELECT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

如果您不希望多次使用相同的源和目的地组合，请使用独特的仅向他们展示一次：

SELECT DISTINCT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

请查看SQL教程或文档 JOIN> JOIN 的工作方式，因为这是编写SQL查询时最重要的事情之一。

最后一个说明：您的描述说您想在“从”和“到”的结果中命名列。我建议避免这种情况，因为“来自”是SQL关键字，因此您不能仅将其用作列名。因此，我将其命名为“源”和“目的地”。如果您想使用“从”和“无论如何），则可以使用引号：

SELECT DISTINCT s1.name AS "From", s2.name AS "To"
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

You can get your expected result using two JOIN, one on the source of the transaction and one on the destination:

SELECT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

In case you don't want the same combination of source and destination multiple times, use DISTINCT to show them only once:

SELECT DISTINCT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

Please have a look on SQL tutorials or documentations how JOIN works because this is one of the most important things when writing SQL queries.

A last note: Your description says you want to name your columns in the result "From" and "To". I recommend to avoid this because "FROM" is a SQL key word, so you can't just use it as column name. Therefore, I named them "Source" and "Destination". If you want to use "From" and "To" anyway, you can use quotes:

SELECT DISTINCT s1.name AS "From", s2.name AS "To"
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

回复收藏 0 原文

~没有更多了~