MySQL:每个组最大n个连接和条件

我有一张类似如下的桌子:

场馆

下表描述了业务列表

id    name
50    Nando's
60    KFC

奖励

该表描述了奖励的数量、对应的地点以及兑换奖励所需的点数。

id    venue_id    name        points
1     50          5% off      10
2     50          10% off     20
3     50          11% off     30
4     50          15% off     40
5     50          20% off     50
6     50          30% off     50
7     60          30% off     70
8     60          60% off     100
9     60          65% off     120
10    60          70% off     130
11    60          80% off     140

点\u数据

下表描述了用户在每个场地的剩余积分数。

venue_id    points_remaining
50           30
60          90

请注意,此查询实际上是在SQL中计算的,如下所示:

select * from (
  select venue_id, (total_points - points_redeemed) as points_remaining
  from (
         select venue_id, sum(total_points) as total_points, sum(points_redeemed) as points_redeemed
         from (
                (
                  select venue_id, sum(points) as total_points, 0 as points_redeemed
                  from check_ins
                  group by venue_id
                )
                UNION
                (
                  select venue_id, 0 as total_points, sum(points) as points_redeemed
                  from reward_redemptions rr
                    join rewards r on rr.reward_id = r.id
                  group by venue_id
                )
              ) a
         group by venue_id
       ) b
  GROUP BY venue_id
) points_data

但是对于这个问题,您可以忽略这个庞大的查询,并假设这个表只是被调用的 points_data

我想得到一个查询,它得到:

• 用户有资格获得每个场馆的前2项奖励
• 用户还没有资格获得每个场地的最低2项奖励

id    venue_id    name        points
2     50          10% off     20
3     50          11% off     30
4     50          15% off     40
5     50          20% off     50
7     60          30% off     70
8     60          60% off     100
9     60          65% off     120

到目前为止,我找到的最佳解决方案是首先获取点数据,然后使用代码(即PHP)动态编写以下内容:

(
  select * from rewards
  where venue_id = 50
  and points > 30
  ORDER BY points desc
  LIMIT 2
)
union all
(
  select * from rewards
  where venue_id = 50
        and points <= 30
  ORDER BY points desc
  LIMIT 2
)
UNION ALL
(
  select * from rewards
  where venue_id = 60
        and points <= 90
  ORDER BY points desc
  LIMIT 2
)
UNION ALL
(
  select * from rewards
  where venue_id = 60
        and points > 90
  ORDER BY points desc
  LIMIT 2
)
ORDER BY venue_id, points asc;

但是,我觉得查询可能会变得有点太长,而且效率很高。例如,如果一个用户在400个场馆中有积分,则表示有800个子查询。

我也尝试过这样的加入,但没有比:

select * from points_data
INNER JOIN rewards on rewards.venue_id = points_data.venue_id
where points > points_remaining;

这与我想要的相去甚远。