怎么写这个SQL语句? [英] How to write this SQL statement?

问题描述

这是表格: 用户

Name:  Subject:
Peter   Math
Mary    Chinese
Mary    Computer
Mary    Hist
Mary    PE
Mary    English
Peter   Art
Chris   English
Chris   Computer
Peter   Computer
Paul    Math

我想让顶部出现在名称中，并返回前4个结果应该是使用者名称.例如，在这种情况下，最上面出现的名称是Mary，并且基于主题，中文，计算机，英语的顺序，因此我想得到以下结果:

I would like to get the the top appear in name, and return top 4 result should be subject name. For example, in this case top appear name is Mary, and base on the order in subject, the Chinese , Computer, English, so I would like to have the result:

Mary    Chinese
Mary    Computer
Mary    English
Mary    Hist

如果Mary不足以显示结果，那么第二个人将是跟随者，例如，假设表格将如下所示:

If Mary is not the most enough to show the result, the second people will be the follow, like, let say the table will like this:

Name:  Subject:
Peter   Math
Mary    Chinese
Mary    Computer
Mary    Hist
Peter   Art
Chris   English
Chris   Computer
Peter   Computer
Paul    Math

结果将是

Mary    Chinese
Mary    Computer
Mary    Hist
Peter   Art

因为玛丽是出现次数最多的人，所以玛丽会回来，但玛丽不足以填补4个职位，因此，倒数第二的位置将位居第二，在这种情况下，我们使用彼得.

Because Mary is the most appear, so Mary will return, but Mary is not enough to fill in 4 positions, so the second most appear will take the place, in this case, we use Peter.

推荐答案

SELECT user.name, user.subject
FROM user
INNER JOIN (
    SELECT name, COUNT(1) AS occurrences
    FROM user
    GROUP BY name
  ) AS user_occurrences
  ON user.name = user_occurrences.name
ORDER BY user_occurrences.occurrences DESC, user.name ASC, user.subject ASC
LIMIT 4

编辑，这可能会更好，具体取决于您使用的RDBMS和数据集的大小.尝试两者并进行比较.

edit This might perform better, depending on the RDBMS you're using and the size of the dataset. Try both and compare.

SELECT user.name, user.subject
FROM user
INNER JOIN user AS user_occurrences
  ON user.name = user_occurrences.name
GROUP BY user.name --, user.subject Second GROUP BY not needed on MySQL, but it should logically be there
ORDER BY COUNT(user_occurrences.subject) DESC, user.name ASC, user.subject ASC
LIMIT 4

这篇关于怎么写这个SQL语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！