我需要帮助编写一个复杂的SELECT语句

i am trying to write an sql statement that will select items from one table and order it by the result of the other table..

我正在尝试编写一个sql语句，该语句将从一个表中选择项，并根据另一个表的结果对其排序。

the 2 tables are:

2表:

events: id, name

事件:id、名称

attendance: user, event

出席:用户、事件

ive got this statement:

我得到了这句话:

SELECT * 
FROM `attendance` 
WHERE event='1' 
  AND user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)

I am using this to get the number of friends that coming to event number 1.

我用这个来得到参加第一个活动的朋友的数目。

now I want to combine that statement with another one to create a statement that select all the events, and order it by the number of friends that going... how can i write that statement?

现在我想把这个语句和另一个语句结合起来，创建一个语句，该语句选择所有事件，并按将要访问的朋友的数量排序。我该怎么写呢?

Thank you very much, Amir.

非常感谢你，阿米尔。

3 个解决方案

#1

I would try something like this:

我想试试这样的东西:

SELECT
    id,
    name,
    (SELECT COUNT(*) FROM attendance att WHERE att.event = ev.id) AS attending
FROM
    event ev
ORDER BY
    attending DESC

However, although I would try something like that, fact is I haven't, so this might not work as is.

然而，尽管我想尝试这样的东西，但事实是我没有，所以这可能不可行。

#2

You could also use a join like:

您还可以使用以下连接:

SELECT events.id, COUNT(attendance.user) AS attending
FROM attendance INNER JOIN events ON attendance.event = events.id
WHERE (attendance.user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153))
GROUP BY events.id
ORDER BY attending DESC

#3

Assuming that the list of numbers is the set of friends you're interested in (it would be better to store them in a table - that's how you record information in a database), then:

假设数字列表是你感兴趣的朋友的集合(最好将它们存储在一个表中——这是你在数据库中记录信息的方式)，那么:

SELECT e.id, e.name, COUNT(*) AS friends
  FROM attendance AS a
  JOIN event      AS e ON a.event = e.id
 WHERE a.user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)
 GROUP BY e.id, e.name;

Complexity, of course, is in the eye of the beholder - this is not actually complex. Note that as written, it does not list events to which none of the friends go. If you want that, you need a LEFT OUTER JOIN and you need the 'friend filter' earlier than the main WHERE clause:

当然，复杂性在观察者的眼中——这并不复杂。请注意，正如所写的，它没有列出所有朋友都不会去的事件。如果你想要这样，你需要一个左外连接，你需要在主WHERE子句之前使用“好友过滤器”:

SELECT e.id, e.name, COUNT(user) AS friends
  FROM event AS e
  LEFT OUTER JOIN
       (SELECT user, event
          FROM attendance
         WHERE user IN (1,2,3,4,5,6,7,8,9,10,11,12,13,444,153)
       ) AS a
    ON a.event = e.id
 GROUP BY e.id, e.name;

The COUNT(user) aggregate returns zero if all the rows in a group contain NULL in the user column.

如果组中的所有行在user列中包含NULL，那么COUNT(用户)聚合将返回0。

Untested SQL.

未经考验的SQL。

#1