다음은 가장 간단한 예이지만 모든 솔루션을 확장 할 수 있어야하지만 많은 n 개의 상위 결과가 필요합니다.
아래 표에 개인, 그룹 및 연령 열 이있는 경우 각 그룹에서 가장 나이가 많은 두 사람을 어떻게 구할 수 있습니까? (그룹 내 동점은 더 많은 결과를 산출하지 않지만 알파벳 순서로 처음 2 개를 제공해야 함)
+ -------- + ------- + ----- + | 사람 | 그룹 | 나이 | + -------- + ------- + ----- + | 밥 | 1 | 32 | | 질 | 1 | 34 | | 숀 | 1 | 42 | | 제이크 | 2 | 29 | | 폴 | 2 | 36 | | 로라 | 2 | 39 | + -------- + ------- + ----- +
원하는 결과 집합 :
+ -------- + ------- + ----- + | 숀 | 1 | 42 | | 질 | 1 | 34 | | 로라 | 2 | 39 | | 폴 | 2 | 36 | + -------- + ------- + ----- +
참고 : 이 질문은 각 그룹화 된 SQL 결과 그룹에 대해 최대 값을 가진 이전의 하나의 레코드 가져 오기-각 그룹에서 단일 최상위 행을 가져오고 @Bohemian에서 MySQL 관련 답변을 받았습니다.
select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`나는 방법을 모르겠지만 이것을 구축 할 수 있기를 바랍니다.
답변
다음을 사용하여이를 수행하는 한 가지 방법이 있습니다 UNION ALL( Demo with SQL Fiddle 참조 ). 그룹이 두 개 이상인 경우 두 그룹으로 작업 할 수 있습니다. 그룹 group수 를 지정하고 각 그룹에 대해 쿼리를 추가해야합니다 group.
(
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
)이를 수행하는 다양한 방법이 있습니다. 상황에 가장 적합한 경로를 결정하려면이 기사를 참조하십시오.
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
편집하다:
이것은 당신에게도 효과가있을 수 있으며 각 레코드마다 행 번호를 생성합니다. 위의 링크에서 예제를 사용하면 행 번호가 2 이하인 레코드 만 반환합니다.
select person, `group`, age
from
(
select person, `group`, age,
(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
from test t
CROSS JOIN (select @num:=0, @group:=null) c
order by `Group`, Age desc, person
) as x
where x.row_number <= 2;데모 보기
답변
다른 데이터베이스에서는을 사용하여이 작업을 수행 할 수 있습니다 ROW_NUMBER. MySQL은 지원하지 않지만 ROW_NUMBER변수를 사용하여 에뮬레이션 할 수 있습니다.
SELECT
person,
groupname,
age
FROM
(
SELECT
person,
groupname,
age,
@rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
@prev := groupname
FROM mytable
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2온라인 작업 참조 : sqlfiddle
편집 나는 단지 bluefeet가 그에게 +1이라는 매우 유사한 대답을 게시 한 것을 알았습니다. 그러나이 답변에는 두 가지 작은 장점이 있습니다.
- 단일 쿼리입니다. 변수는 SELECT 문 내에서 초기화됩니다.
- 질문에 설명 된대로 이름을 알파벳 순서로 지정합니다.
누군가를 도울 수 있도록 여기에 남겨 두겠습니다.
답변
이 시도:
SELECT a.person, a.group, a.age FROM person AS a WHERE
(SELECT COUNT(*) FROM person AS b
WHERE b.group = a.group AND b.age >= a.age) <= 2
ORDER BY a.group ASC, a.age DESC답변
자체 조인 사용은 어떻습니까?
CREATE TABLE mytable (person, groupname, age);
INSERT INTO mytable VALUES('Bob',1,32);
INSERT INTO mytable VALUES('Jill',1,34);
INSERT INTO mytable VALUES('Shawn',1,42);
INSERT INTO mytable VALUES('Jake',2,29);
INSERT INTO mytable VALUES('Paul',2,36);
INSERT INTO mytable VALUES('Laura',2,39);
SELECT a.* FROM mytable AS a
LEFT JOIN mytable AS a2
ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;나에게 준다 :
a.person a.groupname a.age
---------- ----------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36 Bill Karwin의 답변에서 영감을 얻었습니다. 을 통해 각 카테고리별로 상위 10 개 레코드 선택했습니다.
또한 SQLite를 사용하고 있지만 MySQL에서 작동합니다.
다른 것 : 위의 group열을 열로 바꿨습니다.groupname 편의를 위해 열입니다.
편집하다 :
누락 된 타이 결과에 관한 OP의 의견에 이어, 나는 모든 관계를 보여주기 위해 snuffin의 답변을 증가시켰다. 즉, 마지막 행이 연결되어 있으면 아래와 같이 2 개 이상의 행이 리턴 될 수 있습니다.
.headers on
.mode column
CREATE TABLE foo (person, groupname, age);
INSERT INTO foo VALUES('Paul',2,36);
INSERT INTO foo VALUES('Laura',2,39);
INSERT INTO foo VALUES('Joe',2,36);
INSERT INTO foo VALUES('Bob',1,32);
INSERT INTO foo VALUES('Jill',1,34);
INSERT INTO foo VALUES('Shawn',1,42);
INSERT INTO foo VALUES('Jake',2,29);
INSERT INTO foo VALUES('James',2,15);
INSERT INTO foo VALUES('Fred',1,12);
INSERT INTO foo VALUES('Chuck',3,112);
SELECT a.person, a.groupname, a.age
FROM foo AS a
WHERE a.age >= (SELECT MIN(b.age)
FROM foo AS b
WHERE (SELECT COUNT(*)
FROM foo AS c
WHERE c.groupname = b.groupname AND c.age >= b.age) <= 2
GROUP BY b.groupname)
ORDER BY a.groupname ASC, a.age DESC;나에게 준다 :
person groupname age
---------- ---------- ----------
Shawn 1 42
Jill 1 34
Laura 2 39
Paul 2 36
Joe 2 36
Chuck 3 112 답변
Snuffin 솔루션은 행이 많을 때 실행 속도가 느린 것처럼 보이며 Mark Byers / Rick James 및 Bluefeet 솔루션은 select by 실행 후 order by가 적용되기 때문에 내 환경 (MySQL 5.6)에서 작동하지 않으므로 여기에 변형이 있습니다. 이 문제를 해결하기위한 Marc Byers / Rick James 솔루션 (별도의 함축 된 선택) :
select person, groupname, age
from
(
select person, groupname, age,
(@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb,
@prev:= groupname
from
(
select person, groupname, age
from persons
order by groupname , age desc, person
) as sortedlist
JOIN (select @prev:=NULL, @rn :=0) as vars
) as groupedlist
where rownumb<=2
order by groupname , age desc, person;5 백만 행이있는 테이블에서 비슷한 쿼리를 시도했지만 3 초 미만의 결과를 반환합니다.
답변
이것 좀 봐:
SELECT
p.Person,
p.`Group`,
p.Age
FROM
people p
INNER JOIN
(
SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`
UNION
SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group`
) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group`
ORDER BY
`Group`,
Age DESC,
Person;SQL 피들 : http://sqlfiddle.com/#!2/cdbb6/15
답변
다른 답변이 충분히 빠르지 않은 경우이 코드 를 사용해보십시오.
SELECT
province, n, city, population
FROM
( SELECT @prev := '', @n := 0 ) init
JOIN
( SELECT @n := if(province != @prev, 1, @n + 1) AS n,
@prev := province,
province, city, population
FROM Canada
ORDER BY
province ASC,
population DESC
) x
WHERE n <= 3
ORDER BY province, n;산출:
+---------------------------+------+------------------+------------+
| province | n | city | population |
+---------------------------+------+------------------+------------+
| Alberta | 1 | Calgary | 968475 |
| Alberta | 2 | Edmonton | 822319 |
| Alberta | 3 | Red Deer | 73595 |
| British Columbia | 1 | Vancouver | 1837970 |
| British Columbia | 2 | Victoria | 289625 |
| British Columbia | 3 | Abbotsford | 151685 |
| Manitoba | 1 | ...