按行排序

发布于 2024-11-02 06:17:49 字数 313 浏览 0 评论 0原文

好的，我有一个正在尝试构建的查询。我有 2 个表，table1 正常情况下有一堆常规记录，具有唯一的 ID（自动增量），table2 的记录包含 table1 中的一些 id。我正在尝试按 table1 中具有相同 ID 的最高记录进行排序。这是我得到的：

SELECT * FROM table1 
WHERE table1.status = 1 
AND (SELECT COUNT(*) FROM table2 WHERE table2.tbl1_id = table1.id) 
ORDER BY table1.id DESC

谢谢:)

原文

OK so I have a query I am trying to build.. I have 2 tables, table1 has a bunch of regular records as normal with a unique ID (auto increment) and table2 has records that include some of those ids from table1. I am trying to order by the highest records with that same ID in table1.. Heres what I've got:

SELECT * FROM table1 
WHERE table1.status = 1 
AND (SELECT COUNT(*) FROM table2 WHERE table2.tbl1_id = table1.id) 
ORDER BY table1.id DESC

Thanks :)

分享到QQ

分享到微博

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

发布评论

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

念﹏祤嫣 2024-11-09 06:17:50

SELECT table1.id 
FROM table1 
LEFT JOIN table2 ON table2.tbl1_id = table1.id
WHERE table1.status = 1 
GROUP BY table1.id
ORDER BY COUNT(table2.tbl1_id) DESC

SELECT table1.id 
FROM table1 
LEFT JOIN table2 ON table2.tbl1_id = table1.id
WHERE table1.status = 1 
GROUP BY table1.id
ORDER BY COUNT(table2.tbl1_id) DESC

回复收藏 0 原文

姜生凉生 2024-11-09 06:17:50

试试这个：

SELECT a.*, b.cnt
  FROM table1 a LEFT JOIN
    (
        SELECT tbl1_id, COUNT(*) cnt
            FROM table2
        GROUP BY tbl1_id
    ) b
 ON a.id = b.tbl1_id  
WHERE table1.status = 1 
ORDER BY cnt DESC

Try this:

SELECT a.*, b.cnt
  FROM table1 a LEFT JOIN
    (
        SELECT tbl1_id, COUNT(*) cnt
            FROM table2
        GROUP BY tbl1_id
    ) b
 ON a.id = b.tbl1_id  
WHERE table1.status = 1 
ORDER BY cnt DESC

回复收藏 0 原文

~没有更多了~