SQL Server How to get unique record which is present in group 2 after comparing with group 1?

pdtvr36n  于 2023-08-02  发布在  其他
关注(0)|答案(2)|浏览(89)
Modelyear
Apple2009
Apple2010
HTC2010
Motorola2009
Motorola2010
Nokia2009
Nokia2010
One Plus2009
One Plus2010
Samsung2009
Samsung2010

how to get the model which is present in year 2010 but not in 2009. result should be |HTC| 2010|

rur96b6h

rur96b6h1#

with data as (
    select model from T where "year" = 2010
    except
    select model from T where "year" = 2009
)
select model, 2010 from data;

or

select model, 2010
from T
where "year" in (2009, 2010)
group by model
having min("year") = 2010;

or

select * from T
where "year" = 2010
and model not in (
    select model from T
    where "year" = 2009
        and model is not null
);

or

select * from T t1
where "year" = 2010
    and not exists (
        select 1 from T t2
        where t2.model = t1.model
            and t2."year" = 2009
    );

or

select t1.*
from T t1 left outer join T t2
    on t2.model = t1.model and t2."year" = 2009
where t1."year" = 2010 and t2.model is null;
3pmvbmvn

3pmvbmvn2#

You can do something like this:

SELECT Model, year
FROM test 
where Model not in (select Model from test where year != 2010)
      and Model in (select Model from test where year = 2010)

This will return rows with Model that exist only once

相关问题