计算每个人最受欢迎的报纸

Question

我有表格：

Persons (person_id, person_name)
Newspapers (newspaper_id, nespaper_title)
Orders (person_id, newspaper_id, time, price)

我需要一份报告，其中将显示列：（person_name、most_popular_newspaper_title）

例：

+----------+------+---------+
| Persons  |  id  |   name  |
+----------+------+---------+
|          |  1   |  Jack   |
|          |  2   |  Wilyam |
|          |  3   |  Dill   |
+----------+------+---------+

+-------------+------+--------+
| Newspapers  |  id  |  name  |
+-------------+------+--------+
|             |  1   |  News  |
|             |  2   |  Times |
+-------------+------+--------+
+---------+------+-------+---------+-----+
| Orders  |  id  |  fid  |  time   |  n  |
+---------+------+-------+---------+-----+
|         |  1   |  1    |  10:25  |  25 |
|         |  1   |  1    |  15;24  |  20 |
|         |  2   |  2    |  17:42  |  30 |
+---------+------+-------+---------+-----+
+--------+-----------+--------+
| Result |           |        |
+--------+-----------+--------+
|        |  Jack     |  News  |
|        |  Willian  |  Times |
+--------+-----------+--------+

需要 sql 查询

Answer 1

如果你使用的是PostgreSQL，你可以使用窗口函数

这是 SQL：

SELECT p.name,n.title
FROM (
  SELECT o.person_id, o.newspaper_id, 
        RANK() over(partition by person_id ORDER BY cnt desc) as rnk
  FROM (
    SELECT o.person_id, o.newspaper_id,count(*) as cnt
    FROM Orders o
    GROUP BY 1,2) o) r
  JOIN persons p on (r.person_id = p.id)
  JOIN Newspapers n on (r.newspaper_id = n.id)
where r.rnk=1

并链接到SQLfiddle：http：//sqlfiddle.com/#！15/54d1e/2

Answer 2

我更喜欢使用公用表表达式，它可以轻松遵循和理解逻辑。

;WITH   base
          AS (
               SELECT o.person_id
                   ,o.newspaper_id
                   ,COUNT(*) AS Cnt
                FROM Orders o
                GROUP BY o.person_id
                   ,o.newspaper_id
             ),
        personalbest
          AS (
               SELECT p.NAME AS PersonName
               ,n.NAME AS NewsPaperName
                   ,ROW_NUMBER() OVER ( PARTITION BY b.person_id ORDER BY b.Cnt DESC ) AS rn
                FROM base b
                JOIN persons p
                ON b.person_id = p.id
                JOIN Newspaper n
                ON b.newspaper_id = n.id
             )
    SELECT PersonName
           ,NewsPaperName   
    FROM personalbest 
    WHERE rn = 1