34,576
社区成员
发帖
与我相关
我的任务
分享
select * from Stock where year=2015 and exists( select * from Stock where year=2015)
union select * from Stock where year=2014 and not exists( select * from Stock where year=2015)
select * from Stock where year=2015 and id in ( select id from Stock where year=2015)
union select * from Stock where year=2014 and id not in ( select id from Stock where year=2015)
-- 论坛里有很多这样的例子,你可以搜索一下
-- PS: 1# 没有批评你的意思
create table test(code varchar(10),y varchar(10),price int);
insert into test values
('A','2014',100),('A','2015',80),
('B','2014',88),('B','2015',87),
('C','2014',12),('C','2015',19),
('D','2015',11),
('E','2014',11)
go
select * from test
go
with m as (
select * , row_number() over(partition by code order by y desc) rn from test
)
select code, y, price from m where rn =1
go
drop table test
go
(8 行受影响)
code y price
---------- ---------- -----------
A 2014 100
A 2015 80
B 2014 88
B 2015 87
C 2014 12
C 2015 19
D 2015 11
E 2014 11
(8 行受影响)
code y price
---------- ---------- -----------
A 2015 80
B 2015 87
C 2015 19
D 2015 11
E 2014 11
(5 行受影响)
select * from Stock where year=2015 and id in ( select id from Stock where year=2015)
union select * from Stock where year=2014 and id not in ( select id from Stock where year=2015)