同步选择的投影

发布于 2024-12-02 06:00:30 字数 400 浏览 0 评论 0原文

我想在另一个 select 语句的投影中使用表别名，如下所示：

SELECT [ID]
      ,[Name]
      ,CASE WHEN 'reptile' IN 
      (SELECT [Type] FROM dbo.Pets [S] INNER JOIN [P] ON S.People_ID = P.ID) 
      THEN 1 ELSE 0 END [HasReptile]
  FROM People [P]

在投影中有一个 select 语句，它试图使用表别名 [P]。显然这是不合法的。如何在不使用两个单独的查询的情况下实现我的目标？

实际上，我的查询是“对任何拥有“爬行动物”类型宠物的人显示“1”。

我正在使用 SQL Server 2008。

原文

I would like to use a table alias within the projection of another select statement, like this:

SELECT [ID]
      ,[Name]
      ,CASE WHEN 'reptile' IN 
      (SELECT [Type] FROM dbo.Pets [S] INNER JOIN [P] ON S.People_ID = P.ID) 
      THEN 1 ELSE 0 END [HasReptile]
  FROM People [P]

Within the projection there is a select statement, which is trying to use the table alias [P]. Evidentally this is not legal. How can I achieve my goal without using two separate queries?

In effect, my query is saying "show '1' for any person who owns a pet of type 'reptile'.

I am using SQL Server 2008.

分享到QQ

分享到微博

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

发布评论

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

纵情客 2024-12-09 06:00:30

SELECT [ID]
      ,[Name]
      ,CASE WHEN 'reptile' IN 
      (SELECT [Type] FROM dbo.Pets [S] WHERE S.People_ID = P.ID) 
      THEN 1 ELSE 0 END [HasReptile]
  FROM People [P]

SELECT [ID]
      ,[Name]
      ,CASE WHEN 'reptile' IN 
      (SELECT [Type] FROM dbo.Pets [S] WHERE S.People_ID = P.ID) 
      THEN 1 ELSE 0 END [HasReptile]
  FROM People [P]

回复收藏 0 原文

~没有更多了~