在SQL或MySQL中，我们可以连接表和子查询结果吗？

发布于 2024-08-30 02:51:31 字数 224 浏览 5 评论 0原文

我们是否可以将子查询的结果连接到一个表中，例如：

select name from gifts
    LEFT OUTER JOIN (select giftID from gifts) ...

如果不能，是否可以通过一些方法来完成，例如创建临时表？

PS 子查询只能使用 IN 或 NOT IN、EXISTS 或 NOT EXISTS 出现吗？

原文

Can we join a table with the result of a subquery, such as:

select name from gifts
    LEFT OUTER JOIN (select giftID from gifts) ...

If not, can it be done by some methods, such as creating a temporary table?

P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS?

分享到QQ

分享到微博

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

发布评论

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

吃→可爱长大的 2024-09-06 02:51:31

是的，sql 适用于集合；子查询返回一个集合作为结果，所以这是可能的。

您必须为子查询指定一个名称：(SELECT * FROM table) AS sub 或 (SELECT * FROM table) sub。作为完整查询：

SELECT name FROM gifts
    LEFT OUTER JOIN (SELECT giftID FROM gifts) AS sub
    ON ...
WHERE ...

Yes, sql works on sets; a subquery returns a set as result, so this is possible.

You have to give the subquery a name: (SELECT * FROM table) AS sub or (SELECT * FROM table) sub. As full query:

SELECT name FROM gifts
    LEFT OUTER JOIN (SELECT giftID FROM gifts) AS sub
    ON ...
WHERE ...

回复收藏 0 原文

情愿 2024-09-06 02:51:31

是的，您可以使用选择作为内部连接，您只需给它一个别名：

SELECT Name FROM Transactions T
INNER JOIN (SELECT Distinct customerID As CustomerID FROM Customers) A 
ON A.CustomerID = T.CustomerID

yes you can use a select as an INNER JOIN you just have to give it an alias:

SELECT Name FROM Transactions T
INNER JOIN (SELECT Distinct customerID As CustomerID FROM Customers) A 
ON A.CustomerID = T.CustomerID

回复收藏 0 原文

一杆小烟枪 2024-09-06 02:51:31

另一种方法是创建子查询的视图。然后像平常一样进行 JOIN（通过引用 VIEW）。

回复收藏 0 原文

鹤仙姿 2024-09-06 02:51:31

SELECT CustomerId,
       Name,
       Address
FROM Table1 M
INNER JOIN Table2 C ON M.CustomerId=C.CustomerId
WHERE CustomerId IN
    (SELECT CustomerId
     FROM Table1 M
     INNER JOIN Table2 ON M.CustomeID=C.CustomerId)
ORDER BY CustomerId,
         Name,
         Address

SELECT CustomerId,
       Name,
       Address
FROM Table1 M
INNER JOIN Table2 C ON M.CustomerId=C.CustomerId
WHERE CustomerId IN
    (SELECT CustomerId
     FROM Table1 M
     INNER JOIN Table2 ON M.CustomeID=C.CustomerId)
ORDER BY CustomerId,
         Name,
         Address

回复收藏 0 原文

~没有更多了~