时间戳

首页 > 解决方案 > SQL 查询：连接表中出现频率最高的值

sql - SQL 查询：连接表中出现频率最高的值

问题描述

我正在处理一个 SQL 查询，在该查询中，我想根据另一个字段的最频繁出现来连接两个表中最频繁出现的一个字段。

有一张特定餐厅的餐桌，我想从中获得最频繁的订单。这些项目可以由多个不同的人在同一家餐厅订购。我想获得每家餐厅最常出现的项目的结果，并且该项目中，我想要请求它的最频繁的人。

Table Restaurants  
Restaurant
=======
R1
R2 

Table Orders
Item    RequestedBy    Restaurant     Date
==========================================
B1          A           R1             123
B1          B           R1             234
B2          C           R2             456
B1          A           R1             567

我想要的是，

Restaurant  Item   RequestedBy
============================
 R1          B1        A
 R2          B2        C

我目前有以下脚本：

SELECT Restaurants.Restaurant, Orders.Item, Orders.RequestedBy
FROM Restaurants 

LEFT JOIN (Select Restaurant, Item, RequestedBy
From [Orders] T1
Where Item=  
(Select TOP 1 Item
    from [Orders] T2
    where T1. Restaurant = T2. Restaurant
    group by Item
    order by count(Item) desc
)
group by Restaurant, Item, RequestedBy) Orders

ON  Restaurants.Restaurant = Orders.Restaurant

这当前返回的数据如下：

 Restaurant  Item   RequestedBy
 ===========================
 R1           B1        A
 R1           B1        B
 R2           B2        C

任何帮助，将不胜感激！谢谢

标签： sqlsql-server

解决方案

我认为最简单的方法是使用apply：

select r.restaurant, ri.item, rir.requestedby
from restaurant r cross apply
     (select top (1) o.item, count(*) as cnt
      from orders o
      where o.restaurant = r.restaurant
      order by count(*) desc
     ) ri cross apply
     (select top (1) o2.requrestedby, count(*) as cnt
      from orders o2
      where o2.restaurant = r.restaurant and
            o2.item = r.item
      order by count(*) desc
     ) rir;

另一种有效的方法是order by使用窗口函数：

select top (1) with ties restaurant, item, requestedby
from (select o.restaurant, o.item, o.requestedby,
             count(*) as rir_cnt,
             sum(count(*)) over (partition by o.restaurant, o.item) as ri_cnt
      from orders o
      group by o.restaurant, o.item, o.requestedby
     ) rir
order by row_number() over (partition by restaurant order by ri_cnt desc),
         row_number() over (partition by restaurant, item order by rir_cnt desc);

推荐阅读