각 그룹의 마지막 레코드 검색-MySQL
messages아래와 같이 데이터가 포함 된 테이블 이 있습니다.
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1
쿼리를 실행 select * from messages group by name하면 결과는 다음과 같습니다.
1 A A_data_1
4 B B_data_1
6 C C_data_1
다음 결과를 반환하는 쿼리는 무엇입니까?
3 A A_data_3
5 B B_data_2
6 C C_data_1
즉, 각 그룹의 마지막 레코드가 반환되어야합니다.
현재 이것은 내가 사용하는 쿼리입니다.
SELECT
*
FROM (SELECT
*
FROM messages
ORDER BY id DESC) AS x
GROUP BY name
그러나 이것은 매우 비효율적으로 보입니다. 동일한 결과를 얻을 수있는 다른 방법이 있습니까?
MySQL 8.0은 이제 거의 모든 인기있는 SQL 구현과 같이 윈도우 기능을 지원합니다. 이 표준 구문을 사용하여 그룹당 최대 n 개 쿼리를 작성할 수 있습니다.
WITH ranked_messages AS (
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;
다음은 2009 년에이 질문에 대해 작성한 원래 답변입니다.
이 방법으로 솔루션을 작성합니다.
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;
성능과 관련하여 데이터의 특성에 따라 하나의 솔루션 또는 다른 솔루션이 더 좋을 수 있습니다. 따라서 두 쿼리를 모두 테스트하고 데이터베이스 성능이 더 우수한 쿼리를 사용해야합니다.
예를 들어, StackOverflow August 데이터 덤프 사본이 있습니다. 벤치마킹에 사용할 것입니다. Posts테이블 에는 1,114,357 개의 행이 있습니다 . 이것은 Macbook Pro 2.40GHz의 MySQL 5.0.75에서 실행 중입니다 .
주어진 사용자 ID (내)에 대한 가장 최근 게시물을 찾는 쿼리를 작성하겠습니다.
먼저 하위 쿼리에서 @Eric이 표시 하는 기술을 사용합니다 GROUP BY.
SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
FROM Posts pi GROUP BY pi.owneruserid) p2
ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;
1 row in set (1 min 17.89 sec)
EXPLAIN분석 에도 16 초 이상 걸립니다.
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 76756 | |
| 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where |
| 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)
이제 내 기술 을 사용하여 동일한 쿼리 결과를 생성하십시오 LEFT JOIN.
SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;
1 row in set (0.28 sec)
EXPLAIN두 테이블은 자신의 인덱스를 사용 할 수있는 분석 프로그램 :
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index |
| 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)
내 Posts테이블 의 DDL은 다음과 같습니다 .
CREATE TABLE `posts` (
`PostId` bigint(20) unsigned NOT NULL auto_increment,
`PostTypeId` bigint(20) unsigned NOT NULL,
`AcceptedAnswerId` bigint(20) unsigned default NULL,
`ParentId` bigint(20) unsigned default NULL,
`CreationDate` datetime NOT NULL,
`Score` int(11) NOT NULL default '0',
`ViewCount` int(11) NOT NULL default '0',
`Body` text NOT NULL,
`OwnerUserId` bigint(20) unsigned NOT NULL,
`OwnerDisplayName` varchar(40) default NULL,
`LastEditorUserId` bigint(20) unsigned default NULL,
`LastEditDate` datetime default NULL,
`LastActivityDate` datetime default NULL,
`Title` varchar(250) NOT NULL default '',
`Tags` varchar(150) NOT NULL default '',
`AnswerCount` int(11) NOT NULL default '0',
`CommentCount` int(11) NOT NULL default '0',
`FavoriteCount` int(11) NOT NULL default '0',
`ClosedDate` datetime default NULL,
PRIMARY KEY (`PostId`),
UNIQUE KEY `PostId` (`PostId`),
KEY `PostTypeId` (`PostTypeId`),
KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
KEY `OwnerUserId` (`OwnerUserId`),
KEY `LastEditorUserId` (`LastEditorUserId`),
KEY `ParentId` (`ParentId`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;
UPD : 2017-03-31, MySQL 버전 5.7.5 에서는 ONLY_FULL_GROUP_BY 스위치가 기본적으로 활성화되었습니다 (따라서 비 결정적 GROUP BY 쿼리가 비활성화 됨). 또한 GROUP BY 구현을 업데이트했으며 스위치를 비활성화해도 솔루션이 더 이상 예상대로 작동하지 않을 수 있습니다. 하나는 확인해야합니다.
위의 Bill Karwin의 솔루션은 그룹 내 항목 수가 다소 적을 때 잘 작동하지만 솔루션이 비교 n*n/2 + n/2만 필요하기 때문에 그룹이 다소 크면 쿼리 성능이 나빠집니다 IS NULL.
그룹이 있는 InnoDB 18684446행 테이블에서 테스트를했습니다 1182. 이 테이블에는 기능 테스트에 대한 테스트 결과가 포함되어 (test_id, request_id)있으며 기본 키로가 있습니다. 따라서 test_id그룹이고 나는 request_id각각 에 대한 마지막 을 찾고 있었다 test_id.
Bill의 솔루션은 이미 내 dell e4310에서 몇 시간 동안 실행 중이며 적용 범위 인덱스 (따라서 using indexEXPLAIN)에서 작동하더라도 언제 완료 될지 알 수 없습니다 .
동일한 아이디어를 기반으로 한 몇 가지 다른 솔루션이 있습니다.
- 기본 인덱스가 BTREE 인덱스 인 경우 (보통 경우), 가장 큰
(group_id, item_value)쌍은 each 내의 마지막 값group_id이며group_id, 인덱스를 내림차순으로 살펴보면 각각의 첫 번째 값입니다 . - 인덱스에 포함 된 값을 읽으면 인덱스 순서대로 값을 읽습니다.
- 각 인덱스에는 해당 인덱스에 추가 된 기본 키 열이 암시 적으로 포함됩니다 (즉, 기본 키가 커버리지 인덱스에 있음). 아래 솔루션에서는 기본 키에서 직접 작업합니다.이 경우 결과에 기본 키 열을 추가하기 만하면됩니다.
- 대부분의 경우 하위 쿼리에서 필요한 순서로 필요한 행 ID를 수집하고 하위 쿼리의 결과를 ID에 결합하는 것이 훨씬 저렴합니다. 하위 쿼리 결과의 각 행에 대해 MySQL은 기본 키를 기반으로 한 단일 가져 오기가 필요하므로 하위 쿼리가 조인에서 먼저 배치되고 행이 하위 쿼리의 ID 순서대로 출력됩니다 (명시적인 ORDER BY를 생략 한 경우). 조인)
MySQL이 인덱스를 사용하는 3 가지 방법 은 몇 가지 세부 사항을 이해하는 훌륭한 기사입니다.
해결책 1
이것은 매우 빠르며 1,800 만 개 이상의 행에서 약 0.8 초가 걸립니다.
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;
순서를 ASC로 변경하려면 하위 쿼리에 넣고 ID 만 반환 한 다음 하위 쿼리로 사용하여 나머지 열에 조인합니다.
SELECT test_id, request_id
FROM (
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC) as ids
ORDER BY test_id;
이것은 내 데이터에 약 1,2 초가 걸립니다.
해결 방법 2
내 테이블에 약 19 초가 걸리는 또 다른 솔루션이 있습니다.
SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC
내림차순으로 테스트를 반환합니다. 전체 인덱스 스캔을 수행하기 때문에 훨씬 느리지 만 각 그룹에 대해 N 개의 최대 행을 출력하는 방법에 대한 아이디어를 제공합니다.
쿼리의 단점은 쿼리 캐시에서 결과를 캐시 할 수 없다는 것입니다.
중간에 있으므로 하위 쿼리 를 사용하여 올바른 그룹을 반환합니다.
이 시도:
select
a.*
from
messages a
inner join
(select name, max(id) as maxid from messages group by name) as b on
a.id = b.maxid
id최대 값을 원하지 않는 경우 :
select
a.*
from
messages a
inner join
(select name, max(other_col) as other_col
from messages group by name) as b on
a.name = b.name
and a.other_col = b.other_col
이렇게하면 매우 느리거나 비효율적 인 경향이있는 하위 쿼리의 상관 관계 및 / 또는 순서를 피할 수 있습니다.
각 그룹 내의 마지막 게시물에 대한 ID를 얻은 다음 첫 번째 쿼리의 결과를 구문에 대한 인수로 사용하여 메시지 테이블에서 선택하는 다른 솔루션에 도달했습니다 WHERE x IN.
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name
);
다른 솔루션과 비교하여 이것이 어떻게 수행되는지는 모르지만 3 백만 개 이상의 행이있는 내 테이블에서 훌륭하게 작동했습니다. (1200 개 이상의 결과로 4 초 실행)
이것은 MySQL과 SQL Server 모두에서 작동합니다.
하위 쿼리 바이올린 링크에 의한 솔루션
select * from messages where id in
(select max(id) from messages group by Name)
조인 조건에 의한 솔루션 바이올린 링크
select m1.* from messages m1
left outer join messages m2
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null
이 게시물의 이유는 바이올린 링크 만 제공하는 것입니다. 다른 답변에서 동일한 SQL이 이미 제공되었습니다.
나는 아직 큰 DB로 테스트하지 않았지만 이것이 테이블을 결합하는 것보다 빠를 수 있다고 생각합니다.
SELECT *, Max(Id) FROM messages GROUP BY Name
상당한 속도로 접근하는 방법은 다음과 같습니다.
SELECT *
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)
결과
Id Name Other_Columns
3 A A_data_3
5 B B_data_2
6 C C_data_1
내 해결책은 다음과 같습니다.
SELECT
DISTINCT NAME,
MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES
FROM MESSAGE;
두 가지 제안이 있습니다. 첫째, mysql이 ROW_NUMBER ()를 지원한다면 매우 간단합니다.
WITH Ranked AS (
SELECT Id, Name, OtherColumns,
ROW_NUMBER() OVER (
PARTITION BY Name
ORDER BY Id DESC
) AS rk
FROM messages
)
SELECT Id, Name, OtherColumns
FROM messages
WHERE rk = 1;
"마지막"은 이드 순서에서 마지막을 의미한다고 가정하고 있습니다. 그렇지 않은 경우 ROW_NUMBER () 창의 ORDER BY 절을 적절하게 변경하십시오. ROW_NUMBER ()를 사용할 수없는 경우 다른 솔루션입니다.
둘째, 그렇지 않은 경우 다음과 같이 진행하는 것이 좋습니다.
SELECT
Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
SELECT * FROM messages as M2
WHERE M2.Name = messages.Name
AND M2.Id > messages.Id
)
즉, 동일한 이름의 later-Id 메시지가없는 메시지를 선택하십시오.
다음은 GROUP_CONCATorder by를 사용하여 마지막 관련 레코드를 가져 오고 SUBSTRING_INDEX목록에서 레코드 중 하나를 선택 하는 또 다른 방법 입니다.
SELECT
`Id`,
`Name`,
SUBSTRING_INDEX(
GROUP_CONCAT(
`Other_Columns`
ORDER BY `Id` DESC
SEPARATOR '||'
),
'||',
1
) Other_Columns
FROM
messages
GROUP BY `Name`
위의 쿼리는 Other_Columns동일한 Name그룹 에있는 ORDER BY id DESC모든 항목을 그룹화하고 사용 은 Other_Columns내 경우에 제공된 구분 기호를 사용하여 특정 그룹의 모든 항목 을 내림차순으로 조인합니다. 이 목록 ||을 SUBSTRING_INDEX통해 사용 하면 첫 번째 항목이 선택됩니다.
바이올린 데모
SELECT
column1,
column2
FROM
table_name
WHERE id IN
(SELECT
MAX(id)
FROM
table_name
GROUP BY column1)
ORDER BY column1 ;
분명히 동일한 결과를 얻는 여러 가지 방법이 있습니다. 귀하의 질문은 MySQL의 각 그룹에서 마지막 결과를 얻는 효율적인 방법 인 것 같습니다. 방대한 양의 데이터로 작업하고 최신 버전의 MySQL (예 : 5.7.21 및 8.0.4-rc)에서도 InnoDB를 사용한다고 가정하면이를 수행하는 효율적인 방법이 없을 수 있습니다.
때로는 행이 6 천만 개 이상인 테이블에서이 작업을 수행해야합니다.
이 예에서는 쿼리가 데이터의 모든 그룹에 대한 결과를 찾아야하는 약 150 만 행의 데이터를 사용합니다. 실제 사례에서는 약 2,000 개 그룹의 데이터를 반환해야하는 경우가 많습니다 (가상적으로 많은 데이터를 검사 할 필요가 없음).
다음 표를 사용하겠습니다.
CREATE TABLE temperature(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
groupID INT UNSIGNED NOT NULL,
recordedTimestamp TIMESTAMP NOT NULL,
recordedValue INT NOT NULL,
INDEX groupIndex(groupID, recordedTimestamp),
PRIMARY KEY (id)
);
CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id));
온도 테이블은 약 150 만 개의 임의 레코드와 100 개의 서로 다른 그룹으로 채워집니다. selected_group은 100 개의 그룹으로 채워집니다 (이 경우 일반적으로 모든 그룹에 대해 20 % 미만).
이 데이터는 임의적이므로 여러 행이 동일한 recordedTimestamps를 가질 수 있음을 의미합니다. 우리가 원하는 것은 각 그룹에 대해 마지막 기록 된 타임 스탬프가있는 groupID 순서로 선택한 모든 그룹의 목록을 가져오고 동일한 그룹에 이와 같은 일치하는 행이 두 개 이상 있으면 해당 행의 마지막 일치 ID를 가져 오는 것입니다.
가상적으로 MySQL에 특별한 ORDER BY 절의 마지막 행에서 값을 반환하는 last () 함수가있는 경우 다음을 수행 할 수 있습니다.
SELECT
last(t1.id) AS id,
t1.groupID,
last(t1.recordedTimestamp) AS recordedTimestamp,
last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;
일반적인 GROUP BY 함수를 사용하지 않으므로이 경우 몇 개의 100 개 행만 검사하면됩니다. 이것은 0 초 안에 실행되므로 매우 효율적입니다. 일반적으로 MySQL에서는 GROUP BY 절 다음에 ORDER BY 절이 표시되지만이 ORDER BY 절은 last () 함수에 대한 ORDER를 결정하는 데 사용됩니다. GROUP BY 이후에 있으면 GROUPS를 정렬합니다. GROUP BY 절이없는 경우 마지막 값은 반환 된 모든 행에서 동일합니다.
그러나 MySQL에는 이것을 가지고 있지 않으므로 그것이 무엇을 가지고 있는지에 대한 다른 아이디어를 살펴보고 이들 중 어느 것도 효율적이지 않다는 것을 증명해 보겠습니다.
예 1
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
SELECT t2.id
FROM temperature t2
WHERE t2.groupID = g.id
ORDER BY t2.recordedTimestamp DESC, t2.id DESC
LIMIT 1
);
이것은 3,009,254 개의 행을 검사하고 5.7.21에서 0.859 초, 8.0.4-rc에서 약간 더 오래 걸렸습니다.
예 2
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM temperature t1
INNER JOIN (
SELECT max(t2.id) AS id
FROM temperature t2
INNER JOIN (
SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
FROM selected_group g
INNER JOIN temperature t3 ON t3.groupID = g.id
GROUP BY t3.groupID
) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
GROUP BY t2.groupID
) t5 ON t5.id = t1.id;
이것은 1,505,331 행을 검사하고 5.7.21에서 ~ 1.25 초, 8.0.4-rc에서 약간 더 오래 걸렸습니다.
예제 3
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM temperature t1
WHERE t1.id IN (
SELECT max(t2.id) AS id
FROM temperature t2
INNER JOIN (
SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
FROM selected_group g
INNER JOIN temperature t3 ON t3.groupID = g.id
GROUP BY t3.groupID
) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
GROUP BY t2.groupID
)
ORDER BY t1.groupID;
이것은 3,009,685 행을 검사하고 5.7.21에서 ~ 1.95 초, 8.0.4-rc에서 약간 더 오래 걸렸습니다.
예 4
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
SELECT max(t2.id)
FROM temperature t2
WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
SELECT max(t3.recordedTimestamp)
FROM temperature t3
WHERE t3.groupID = g.id
)
);
이것은 6,137,810 개의 행을 검사하고 5.7.21에서 ~ 2.2 초, 8.0.4-rc에서 약간 더 오래 걸렸습니다.
예 5
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
SELECT
t2.id,
t2.groupID,
t2.recordedTimestamp,
t2.recordedValue,
row_number() OVER (
PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
) AS rowNumber
FROM selected_group g
INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;
이것은 6,017,808 개의 행을 검사하고 8.0.4-rc에서 ~ 4.2 초가 걸렸습니다.
예제 6
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
SELECT
last_value(t2.id) OVER w AS id,
t2.groupID,
last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp,
last_value(t2.recordedValue) OVER w AS recordedValue
FROM selected_group g
INNER JOIN temperature t2 ON t2.groupID = g.id
WINDOW w AS (
PARTITION BY t2.groupID
ORDER BY t2.recordedTimestamp, t2.id
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
) t1
GROUP BY t1.groupID;
이것은 6,017,908 개의 행을 검사하고 8.0.4-rc에서 ~ 17.5 초가 걸렸습니다.
예제 7
SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2
ON t2.groupID = g.id
AND (
t2.recordedTimestamp > t1.recordedTimestamp
OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
)
WHERE t2.id IS NULL
ORDER BY t1.groupID;
이건 영원히 걸려서 죽여야 했어요.
이 시도:
SELECT jos_categories.title AS name,
joined .catid,
joined .title,
joined .introtext
FROM jos_categories
INNER JOIN (SELECT *
FROM (SELECT `title`,
catid,
`created`,
introtext
FROM `jos_content`
WHERE `sectionid` = 6
ORDER BY `id` DESC) AS yes
GROUP BY `yes`.`catid` DESC
ORDER BY `yes`.`created` DESC) AS joined
ON( joined.catid = jos_categories.id )
안녕하세요 @Vijay Dev 귀하의 테이블 메시지 에 자동 증가 기본 키인 Id 가 포함 된 경우 기본 키에 대한 최신 레코드 기준을 가져 오려면 쿼리가 아래와 같이 읽어야합니다.
SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
여기에서도 볼 수 있습니다.
http://sqlfiddle.com/#!9/ef42b/9
첫 번째 솔루션
SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);
두 번째 솔루션
SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
Group By 레코드에서 마지막 레코드를 가져올 때 MySQL을 사용하는 방법을 살펴 보겠습니다. 예를 들어,이 게시물 세트가있는 경우.
id category_id post_title
1 1 Title 1
2 1 Title 2
3 1 Title 3
4 2 Title 4
5 2 Title 5
6 3 Title 6
제목 3, 제목 5 및 제목 6 인 각 범주의 마지막 게시물을 얻을 수 있기를 원합니다. 범주별로 게시물을 얻으려면 MySQL Group By 키보드를 사용합니다.
select * from posts group by category_id
그러나이 쿼리에서 얻은 결과는 다음과 같습니다.
id category_id post_title
1 1 Title 1
4 2 Title 4
6 3 Title 6
group by는 항상 결과 집합에있는 그룹의 첫 번째 레코드를 반환합니다.
SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );
그러면 각 그룹에서 가장 높은 ID를 가진 게시물이 반환됩니다.
id category_id post_title
3 1 Title 3
5 2 Title 5
6 3 Title 6
이 방법을 사용하여 테이블에서 중복을 삭제할 수있는 방법이 있습니까? 결과 집합은 기본적으로 고유 한 레코드 모음이므로 결과 집합에없는 모든 레코드를 삭제할 수 있다면 사실상 중복이 없을 것입니다. 나는 이것을 시도했지만 mySQL에서 1093 오류가 발생했습니다.
DELETE FROM messages WHERE id NOT IN
(SELECT m1.id
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL)
출력을 임시 변수에 저장 한 다음 NOT IN (임시 변수)에서 삭제하는 방법이 있습니까? @Bill은 매우 유용한 솔루션에 감사드립니다.
편집 : 해결책을 찾았다 고 생각하십시오.
DROP TABLE IF EXISTS UniqueIDs;
CREATE Temporary table UniqueIDs (id Int(11));
INSERT INTO UniqueIDs
(SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
(T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields
AND T1.ID < T2.ID)
WHERE T2.ID IS NULL);
DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
아래 쿼리는 귀하의 질문에 따라 잘 작동합니다.
SELECT M1.*
FROM MESSAGES M1,
(
SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
FROM MESSAGES
GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;
각의 마지막 행을 원하는 경우 Name에, 당신은에 의해 각 행 그룹에 행 번호를 제공 할 수 있습니다 Name에 의해 순서를 Id내림차순으로한다.
질문
SELECT t1.Id,
t1.Name,
t1.Other_Columns
FROM
(
SELECT Id,
Name,
Other_Columns,
(
CASE Name WHEN @curA
THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curA := Name END
) + 1 AS rn
FROM messages t,
(SELECT @curRow := 0, @curA := '') r
ORDER BY Name,Id DESC
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;
SQL 바이올린
SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )
select * from messages group by name desc
이것은 어떤가요:
SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;
비슷한 문제 (postgresql 힘든)와 1M 레코드 테이블에서 발생했습니다. 이 솔루션은 LEFT JOIN으로 생성 된 1.7 초 대 44 초가 걸립니다. 제 경우에는 이름 필드의 해당 항목을 NULL 값에 대해 필터링해야했기 때문에 0.2 초 더 나은 성능을 얻을 수있었습니다.
성능이 정말로 관심사라면 IsLastInGroupBIT 유형 의 테이블에 새 열을 도입 할 수 있습니다 .
마지막 열에 대해 true로 설정하고 모든 행 삽입 / 업데이트 / 삭제로 유지하십시오. 쓰기는 더 느리지 만 읽기에 도움이됩니다. 사용 사례에 따라 다르며 읽기에 중점을 둔 경우에만 권장합니다.
따라서 쿼리는 다음과 같습니다.
SELECT * FROM Messages WHERE IsLastInGroup = 1
계산하여 그룹화 할 수 있으며 다음과 같이 그룹의 마지막 항목을 가져올 수도 있습니다.
SELECT
user,
COUNT(user) AS count,
MAX(id) as last
FROM request
GROUP BY user
참고 URL : https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql
'your programing' 카테고리의 다른 글
| null이 객체 인 이유는 무엇이며 null과 undefined의 차이점은 무엇입니까? (0) | 2020.09.28 |
|---|---|
| 초보자를위한 Git : 확실한 실용 가이드 (0) | 2020.09.28 |
| Mockito로 void 메서드를 모의하는 방법 (0) | 2020.09.28 |
| grep, 특정 파일 확장자 만 (0) | 2020.09.28 |
| Gradle에서 구현과 컴파일의 차이점은 무엇입니까? (0) | 2020.09.28 |