回答編集履歴

1 追記

yambejp

yambejp score 57456

2016/11/01 20:49  投稿

```SQL
create table table_a(id int not null primary key,company varchar(20),name varchar(20),year int,value int null);
insert into table_a values
(1,'A','asset1', 2013,100),
(2,'A','asset1',2014,150),
(3,'A','asset1',2015,200),
(4,'A','asset1',2016,220),
(5,'A','asset2',2015,100),
(6,'A','asset2',2016,NULL),
(7,'A','asset3',2016,60),
(8,'B','asset4',2015,100),
(9,'B','asset4',2016,NULL),
(10,'B','asset5',2016,NULL),
(11,'B','asset6',2014,30),
(12,'B','asset6',2015,25);
```
だとして
```SQL
SELECT t1.company,group_concat(id) as id from table_a as t1
INNER JOIN (
SELECT company,max(year) as year
from table_a where value IS NOT NULL GROUP BY company
) as t2
on t1.year=t2.year and t1.company=t2.company
group by company;
```
```
# 追記
該当するレコードをすべてとりだすならこうしてください
```SQL
SELECT t1.* from table_a as t1
INNER JOIN (
SELECT company,max(year) as year
from table_a where value IS NOT NULL GROUP BY company
) as t2
on t1.year=t2.year and t1.company=t2.company
WHERE t1.company='A';
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る