使用SQL JOIN和COUNT [英] Using SQL JOIN and COUNT

问题描述

让我们有两个表，一个表保存用户信息，另一个表保存某种用户记录，例如收据.用户和收据之间存在一对多的关系.

Let there be two tables, one holding user information and one holding user records of some sort, say receipts. There is a one-to-many relationship between the users and receipts.

按收据数量最多排序的最佳SQL检索用户方法是什么?

What would be the best SQL method of retrieving users, sorted by the greatest number of receipts?

我能想到的最好的方法是使用join和count(?)返回一组用户及其关联的收据数量.

The best way I can think of is using a join and count(?) to return an array of users and their number of associated receipts.

在这种情况下是否可以使用count函数?

Is there a way to make use of the count function in this instance?

select * from `users` inner join `receipts` on `users`.`id` = `receipts`.`uId`

推荐答案

Dave和meewoK提供的两个答案将满足您的需要.我提供了一种替代方法，它应该提供更好的性能并允许您显示更多的用户信息，因为在Dave的答案中，您只能选择由聚合函数或组中使用的列条款.

Two answers provided by Dave and meewoK will accomplish what you need. I'm providing an alternative, which should provide better performance and allow you to show more user information because in the case with Dave's answer you can only SELECT columns that are used by an aggregate function or in the group clause.

SELECT users.id, users.name, r.numReceipts  
FROM users u
INNER JOIN (
   SELECT uId, count(receipts) as numReceipts
   FROM receipts
   GROUP BY receipts.id
) as r ON r.uId = u.id
ORDER BY r.numReceipts DESC

这将创建一个内联视图.仅返回每个用户的收据计数，然后在用户ID上加入此内联视图.

This creates an inline view. Only return the count of receipts of each user and then join this inline view on the user's ID.

如果我错了，有人可以纠正我，但是有人告诉我，当您在SELECT子句中执行标量子查询时，计划程序的效率不高.最好以这种方式加入临时表.编写此查询的方式有多种，这完全取决于您要如何使用信息！！！干杯！

Some one correct me if I'm wrong, but I've been told that the planner isn't as efficient when you do a scalar subquery in the SELECT clause. It's better to join on a temporary table this way. There are multiple ways to write this query and it all depends on how you want to use the information!!! Cheers!

这篇关于使用SQL JOIN和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！