英文:
How to get unique record which is present in group 2 after comparing with group 1?
问题
| Model | year |
|---|---|
| HTC | 2010 |
英文:
| Model | year |
|---|---|
| Apple | 2009 |
| Apple | 2010 |
| HTC | 2010 |
| Motorola | 2009 |
| Motorola | 2010 |
| Nokia | 2009 |
| Nokia | 2010 |
| One Plus | 2009 |
| One Plus | 2010 |
| Samsung | 2009 |
| Samsung | 2010 |
how to get the model which is present in year 2010 but not in 2009.
result should be |HTC| 2010|
答案1
得分: 1
你可以像这样做：
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)
这将返回只存在一次的 Model 的行。
英文:
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
答案2
得分: 1
以下是已翻译的内容：
with data as (
select model from T where "year" = 2010
except
select model from T where "year" = 2009
)
select model, 2010 from data;
或
select model, 2010
from T
where "year" in (2009, 2010)
group by model
having min("year") = 2010;
或
select * from T
where "year" = 2010
and model not in (
select model from T
where "year" = 2009
and model is not null
);
或
select * from T t1
where "year" = 2010
and not exists (
select 1 from T t2
where t2.model = t1.model
and t2."year" = 2009
);
或
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论