My<cod/>.net
составить SQL запрос
Просмотров: 294
Ответов: 3
База данных mysql.
таблица, с такими данными (упрощено):
id____ project_id_____year
1________1____________2010
2________1____________2008
3________1____________2009
4________2____________2007
5________2____________2009
Хотелось бы получить вот такой результат:
(данные сгруппированы по project_id и взята строка где year — минимальный)
id________project_id_____year
2____________1___________2008
4____________2___________2007
1________1____________2010
2________1____________2008
3________1____________2009
4________2____________2007
5________2____________2009
2____________1___________2008
4____________2___________2007
Предположим таблица называется project.
SELECT p1.id, p1.project_id, p1.year FROM project as p1 LEFT OUTER JOIN project as p2 ON (p1.project_id = p2.project_id AND p1.year > p2.year) where p2.project_id is NULL;
SELECT p1.id, p1.project_id, p1.year FROM project as p1 JOIN (SELECT p2.project_id, MIN(p2.year) AS min_year FROM project as p2 GROUP BY p2.project_id) as p3 ON (p1.year = p3.min_year);
Предложу свои 2 варианта:
SELECT ID, PROJECT_ID, YEAR
FROM PROJECT AS P1
JOIN (SELECT PROJECT_ID, MIN(YEAR) AS YEAR
FROM PROJECT
GROUP BY PROJECT_ID
) AS P2
ON P1.PROJECT_ID = P2.PROJECT_ID AND P1.YEAR = P2.YEAR
SELECT ID, PROJECT_ID, YEAR
FROM PROJECT
WHERE (PROJECT_ID, YEAR) IN
(
SELECT PROJECT_ID, MIN(YEAR) AS YEAR
FROM PROJECT
GROUP BY PROJECT_ID
)
SELECT ID, PROJECT_ID, YEAR
FROM PROJECT AS P1
JOIN (SELECT PROJECT_ID, MIN(YEAR) AS YEAR
FROM PROJECT
GROUP BY PROJECT_ID
) AS P2
ON P1.PROJECT_ID = P2.PROJECT_ID AND P1.YEAR = P2.YEARSELECT ID, PROJECT_ID, YEAR
FROM PROJECT
WHERE (PROJECT_ID, YEAR) IN
(
SELECT PROJECT_ID, MIN(YEAR) AS YEAR
FROM PROJECT
GROUP BY PROJECT_ID
)