在另一个选择中重用表别名

发布于 2024-12-03 10:01:18 字数 219 浏览 0 评论 0原文

我有一个sql语句：

select id from table1 t1, table t2 
where.....
order by ( select count(owner_id) from t2) ASC;

我在这里要做的是选择所有者拥有最少项目数的项目的id。

这可能吗？如果没有，我可以做什么来实现目标？

提前致谢！

原文

I have a sql statement:

select id from table1 t1, table t2 
where.....
order by ( select count(owner_id) from t2) ASC;

What I want to do here is to select the id of the item whose owner has least number of items.

Is this possible? If not, what I can do to achieve to goal?

Thanks in advance!

分享到QQ

分享到微博

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

发布评论

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

稍尽春風 2024-12-10 10:01:18

你没有提到你正在使用什么 SQL，但你可以在 PL 中执行此操作或类似的操作（我相信）；我假设您在 id 上链接表 1 和表 2；我没有单独按 count(owner_id) 进行排序，因为这始终是相同的值。显然，可以根据您想要的任何内容进行分区，以获得所需的正确计数。

select id
  from ( select t1.id, t2.ct
           from table1 t1
              , ( select id, count(owner_id) over ( partition by id ) as ct
                    from table2 )  t2 
          where t1.id = t2.id 
          order by t2.ct ASC )
       ;

You don't mention what SQL you're using but you can do this, or something similar, in PL ( and My I believe ); I'm assuming you're linking table 1 and 2 on id; I haven't ordered by the count(owner_id) alone as this will always be the same value. Obviously partition by whatever you want to get the correct count you're after.

select id
  from ( select t1.id, t2.ct
           from table1 t1
              , ( select id, count(owner_id) over ( partition by id ) as ct
                    from table2 )  t2 
          where t1.id = t2.id 
          order by t2.ct ASC )
       ;

回复收藏 0 原文

~没有更多了~