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'; |
| ``` |
