为同一属性选择多个等于另一个属性的条目

gz5pxeao  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(387)

我想修改现有的sql查询,但无法使其按我所希望的方式工作(这与家庭作业/考试无关)
我想要一个像现在一样获取所有Mapid、Map名称和奖励点数的查询,但是获取所有不同运行id的Map,而不是只获取一次Mapid。现在,如果在inf\ U时间内有一个用户的mapid,它将不再显示在最终列表中。如果mapid不在该用户的inf\u时间内,我希望为每个runid显示一次map。
表结构:

inf_maps: mapid, mapname
inf_simpleranks_maps: mapid, runid, rewardpoints
inf_times: uid, mapid, runid
select a.mapid, b.mapname, a.rewardpoints 
from (select r.mapid, rewardpoints 
      from inf_maps r, inf_simpleranks_maps srm 
      where r.mapid 
      not in (select mapid 
              from inf_times 
              where uid = %d 
              group by uid, mapid) 
      and r.mapid = srm.mapid 
      order by rewardpoints desc) a, 
inf_maps b 
where a.mapid = b.mapid;

例子

inf_times
uid mapid runid   
1    4     1

inf_simpleranks_maps
mapid runid rewardpoints
4       1       10
4       2       12 

inf_maps
mapid mapname
4      mapmap

查询应返回

mapid mapname rewardpoints
4      mapmap     12

相反,它现在什么也不返回。

x33g5p2x

x33g5p2x1#

用下列方法尝试 not exists . 这是演示。

select
  im.mapid,
  im.mapname,
  ism.rewardpoints
from inf_maps im
join inf_simpleranks_maps ism
on im.mapid = ism.mapid
where not exists
(
  select
    mapid
  from inf_times it
  where ism.mapid = it.mapid
  and ism.runid = it.runid
)

输出:

| mapid | mapname | rewardpoints |
| ----- | ------- | ------------ |
| 4     | mapmap  | 12           |
bprjcwpo

bprjcwpo2#

你的解释很难理解。但是,根据您的示例数据,以下查询将生成您期望的结果:

select  m.*, r.rewardpoints
from inf_maps m
inner join inf_simpleranks_maps r on r.mapid = m.mapid
where not exists (
    select 1 from inf_times t where t.mapid = m.mapid and t.runid = r.runid
)

相关问题