请教一句sql select的写法

islxyqwe

风怒

北海生未径

嵌套

islxyqwe

mysql要写子查询。先把三个表串起来按a.id分组取id和max（c.value）,再串回去把其他的项取出来就行了。postgres可以直接按id取以value排序的rownumber然后过滤出rownumber为1即可。

alvin051414

--a表数据可靠的话
--标准sql
select
  a.id,
  a.name,
  case when sub_a.max_value is null then 0 else sub_a.max_value end as highest,
  case when sub_b.max_value is null then 0 else sub_b.max_value end as highestFullTime
from a
left join
  (select b.aid,max(c.value) as max_value from b left join c on b.level=c.level group by b.aid) sub_a
on a.id=sub_a.aid
left join
  (select b_full.aid,max(c.value) as max_value from (select * from b where type='全日制')  b_full
  left join c on b_full.level=c.level group by b_full.aid) sub_b
on a.id=sub_b.aid

--Oracle SQL之类的可以写的更漂亮
select
  a.id,
  a.name,
  nvl(max(c.value),0) as highest,
  max(decode(b.type,'全日制',c.value,0)) as highestFullTime
from a
left join b on a.id=b.aid
left join c on b.level=c.level
group by a.id,a.name

--ab表缺数据的话就刺激了，要outer join再加一堆case when或者nvl2了
--s1这代码模式不兼容case when的么…

hamano_taiki

1. select
   
2. a.id,
   
3. a.name,
   
4. max(c.value) as hightest,
   
5. h.value as hightestFullTime
   
6. from a
   
7. left join b on a.id=b.id
   
8. left join c on b.level=c.level
   
9. left join
   
10. (select
    
11. a.id,
    
12. max(c.value) as value
    
13. from a
    
14. left join b on a.id=b.id
    
15. left join c on b.level=c.level
    
16. where b.type="全日制"
    
17. group by a.id)
    
18. group by a.id

复制代码

lancherliu

完了，完全忘了，这才两年不用sql

  -- 来自 能看大图的 Stage1官方 Android客户端

赤色彗星SEXY

mysql这种没窗口函数的渣渣这种时候特别烂

  -- 来自 能搜索的 Stage1官方 Android客户端