sql - 如果每个用户在本年度添加了新雇主，则编写查询以向每个用户添加标志

Question

    employers table

    column  type
    user_id integer
    year    string 
    employer_ein    string

我们得到一个名为employees 的表，它由user_id、year 和employees EIN 标签组成。用户可以拥有由不同 EIN 标签指定的多个雇主。如果每个用户在当年添加了新雇主，请编写查询以向每个用户添加标志。

   Example: 

   employer

   user_id    year    employer_ein
   # 34323      2018    A 
   # 34323      2018    B
   # 34323      2018    C
   # 34323      2017    F
   # 34323      2017    A
   # 34323      2017    B

   # 86323      2018    A
   # 86323      2018    B
   # 86323      2018    C
   # 86323      2017    B
   # 
   # 98787      2018    A
   # 98787      2018    B
   # 98787      2018    F
   # 98787      2017    F
   # 98787      2017    B
   # 98787      2017    A
   # 
   # 55559      2018    A
   # 55559      2018    B
   # 55559      2018    C


   # Output
   # user_id    year    new_ein_flag
   # 34323      2018      1
   # 86323      2018      1
   # 98787      2018      0  

标签： sql

Answer 1

    select user_id,max(year) as year, max(sumrow) as maxsumrow,
(case min(sumrow) = 1 and max(sumrow) = 2 when TRUE then 1 when FALSE then 0 end) as new_ein_flag 
from 
(SELECT user_id,count(*) as sumrow, max(year) as year,employer_ein FROM `ein` GROUP BY user_id,employer_ein) as grouptable
where year = (select max(year) as maxyear from ein)
group by user_id
 having max(sumrow)=2

我尝试过这个。是截图；