MAX(Column value), MYSQL에서 다른 열로 파티션을 선택한 행은 어떻게 해야 합니다.
플레이어의 퍼포먼스 표가 있습니다.
CREATE TABLE TopTen (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
home INT UNSIGNED NOT NULL,
`datetime`DATETIME NOT NULL,
player VARCHAR(6) NOT NULL,
resource INT NOT NULL
);
어떤 질문이 독특한 각 각 고유 항목에 대한 행을 반환하는쿼리는 무엇입니까에 즐비한 돌아올 것인가?home최대치를 유지하다의 최대값을 잡는 모습.datetime다른 말로?방법은 최대 즉에 의해 필터링 할 수 있,최대값으로 필터링하려면 어떻게 해야 합니까?datetime((추정자에 따라서도 분류되)home)과 여전히 또한 다른 비열과 같은 다른non-grouped,non-aggregate 기둥(, 비열(예:포함한다.player)결과에?)의결과가 어떻게 됩니까?
이 샘플 데이터의 경우:
INSERT INTO TopTen
(id, home, `datetime`, player, resource)
VALUES
(1, 10, '04/03/2009', 'john', 399),
(2, 11, '04/03/2009', 'juliet', 244),
(5, 12, '04/03/2009', 'borat', 555),
(3, 10, '03/03/2009', 'john', 300),
(4, 11, '03/03/2009', 'juliet', 200),
(6, 12, '03/03/2009', 'borat', 500),
(7, 13, '24/12/2008', 'borat', 600),
(8, 13, '01/01/2009', 'borat', 700)
;
결과는 다음과 같습니다.
| 아이디 | 집입니다 | 날짜 | 플레이어 | 자원 |
|---|---|---|---|---|
| 1 | 10 | 04/03/2009 | 존. | 399 |
| 2 | 11 | 04/03/2009 | 줄리엣 | 244 |
| 5 | 12 | 04/03/2009 | 보랏토 | 555 |
| 8 | 13 | 01/01/2009 | 보랏토 | 700 |
나는 최대 서브쿼리를 시도했는덴 보조 질문 노력했다.datetime각 각각에 대해서에home::
-- 1 ..by the MySQL manual:
SELECT DISTINCT
home,
id,
datetime AS dt,
player,
resource
FROM TopTen t1
WHERE `datetime` = (SELECT
MAX(t2.datetime)
FROM TopTen t2
GROUP BY home)
GROUP BY `datetime`
ORDER BY `datetime` DESC
그 result-set, 그 결과결과 집합에는 130개의 행이의 일부 중복 있지만 데이터베이스에는 187개의행이 있습니다가 포함되는지 여부를 나타내는 값 130줄지만 데이터베이스 187 지키고 있다.결과에는 다음 이야기 행이중복되어 있습니다.home..
그리고 나서 저는 최대 그런참여하려고 했습니다 서브쿼리에 다음을 받는 보조 질문에 참여하려고 했다.datetime각 줄에 각 행마다id::
-- 2 ..join
SELECT
s1.id,
s1.home,
s1.datetime,
s1.player,
s1.resource
FROM TopTen s1
JOIN (SELECT
id,
MAX(`datetime`) AS dt
FROM TopTen
GROUP BY id) AS s2
ON s1.id = s2.id
ORDER BY `datetime`
아뇨, 모든 기록을 주죠
저는 여러 가지 이국적인 질문을 시도했지만, 각각 다양한 결과를 얻었지만, 이 문제를 해결하는 데 전혀 도움이 되지 않았습니다.
거의 다 왔어!시간을 '홈'에.toptenBOTH 드의 both :
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
빠른 ★★★★★★★★★★★★★★★★★」MySQL 및 "없음"을 사용하지 않음GROUP BY:
SELECT m.* -- get the row that contains the max value
FROM topten m -- "m" from "max"
LEFT JOIN topten b -- "b" from "bigger"
ON m.home = b.home -- match "max" row with "bigger" row by `home`
AND m.datetime < b.datetime -- want "bigger" than "max"
WHERE b.datetime IS NULL -- keep only if there is no bigger than max
설명:
자신과 해 보세요.home★★★★★★★★★의 사용법LEFT JOIN는 테이블의 합니다.m결과 세트에 표시됩니다.에 b 가지다NULL: " " " " "b.
on JOIN asks asks asks asks from from from from from from from from from from from .b 큰 .datetime의 행m.
를 사용하여, 「」는, 「 」를해 주세요.LEFT JOIN는 다음 쌍을 생성합니다.
+------------------------------------------+--------------------------------+
| the row from `m` | the matching row from `b` |
|------------------------------------------|--------------------------------|
| id home datetime player resource | id home datetime ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1 | 10 | 04/03/2009 | john | 399 | NULL | NULL | NULL | ... | *
| 2 | 11 | 04/03/2009 | juliet | 244 | NULL | NULL | NULL | ... | *
| 5 | 12 | 04/03/2009 | borat | 555 | NULL | NULL | NULL | ... | *
| 3 | 10 | 03/03/2009 | john | 300 | 1 | 10 | 04/03/2009 | ... |
| 4 | 11 | 03/03/2009 | juliet | 200 | 2 | 11 | 04/03/2009 | ... |
| 6 | 12 | 03/03/2009 | borat | 500 | 5 | 12 | 04/03/2009 | ... |
| 7 | 13 | 24/12/2008 | borat | 600 | 8 | 13 | 01/01/2009 | ... |
| 8 | 13 | 01/01/2009 | borat | 700 | NULL | NULL | NULL | ... | *
+------------------------------------------+--------------------------------+
★★★★★★★★★★★★★★★★★★★.WHERE절은 다음을 가진 쌍만 유지합니다.NULL에 s가 있습니다.b는 (표시가 되어 있습니다.*를 참조해 주세요. 「」의입니다. 즉, 이는 다음 조건으로부터 두 번째 조건 때문에JOIN " "에서 행"m열에서 큰 datetime.
SQL AntiPatterns를 읽어보십시오. 다른 SQL 힌트에 대한 데이터베이스 프로그래밍의 함정 회피.
T-SQL 버전은 다음과 같습니다.
-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700
-- Answer
SELECT id, home, date, player, resource
FROM (SELECT id, home, date, player, resource,
RANK() OVER (PARTITION BY home ORDER BY date DESC) N
FROM @TestTable
)M WHERE N = 1
-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource
FROM @TestTable T
INNER JOIN
( SELECT TI.id, TI.home, TI.date,
RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
FROM @TestTable TI
WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id
★★★★
MySQL RANK() OVER.
그러나 에뮬레이트할 수 있습니다(MySQL을 사용한 분석(AKA 랭킹) 함수 에뮬레이트 참조).
MySQL 버전은 다음과 같습니다.
SELECT id, home, date, player, resource
FROM TestTable AS t1
WHERE
(SELECT COUNT(*)
FROM TestTable AS t2
WHERE t2.home = t1.home AND t2.date > t1.date
) = 0
은 각 합니다.homeDATETIME 개요
SELECT id, home, datetime, player, resource
FROM (
SELECT (
SELECT id
FROM topten ti
WHERE ti.home = t1.home
ORDER BY
ti.datetime DESC
LIMIT 1
) lid
FROM (
SELECT DISTINCT home
FROM topten
) t1
) ro, topten t2
WHERE t2.id = ro.lid
이렇게 하면 원하는 결과를 얻을 수 있을 것입니다.
SELECT home, MAX(datetime)
FROM my_table
GROUP BY home
그러나 다른 열도 필요한 경우 원래 표와 결합하기만 하면 됩니다(체크 표시).Michael La Voie을 클릭합니다.
안부 전합니다.
이 스레드(댓글 날짜의 범위는 1.5년)에 계속 접속하는 것 같기 때문에, 다음과 같이 간단하지 않습니다.
SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home
집계 기능이 필요 없습니다...
건배.
이것도 시도해 볼 수 있습니다.테이블이 클 경우 쿼리 성능이 향상됩니다.각 가정에 대해 기록이 두 개 이하이고 날짜가 다를 때 효과가 있습니다.보다 나은 일반 MySQL 쿼리는 위의 Michael La Voie의 쿼리입니다.
SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM t_scores_1 t1
INNER JOIN t_scores_1 t2
ON t1.home = t2.home
WHERE t1.date > t2.date
또는 Postgres 또는 분석 기능을 제공하는 dbs의 경우
SELECT t.* FROM
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
, row_number() over (partition by t1.home order by t1.date desc) rw
FROM topten t1
INNER JOIN topten t2
ON t1.home = t2.home
WHERE t1.date > t2.date
) t
WHERE t.rw = 1
SELECT tt.*
FROM TestTable tt
INNER JOIN
(
SELECT coord, MAX(datetime) AS MaxDateTime
FROM rapsa
GROUP BY
krd
) groupedtt
ON tt.coord = groupedtt.coord
AND tt.datetime = groupedtt.MaxDateTime
이는 Oracle에서 작동합니다.
with table_max as(
select id
, home
, datetime
, player
, resource
, max(home) over (partition by home) maxhome
from table
)
select id
, home
, datetime
, player
, resource
from table_max
where home = maxhome
SQL Server에 대해 다음을 시도해 보십시오.
WITH cte AS (
SELECT home, MAX(year) AS year FROM Table1 GROUP BY home
)
SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year
그룹 내 중복된 MAX(datetime)가 있는 엔트리를 하나만 인쇄하는 MySQL 버전입니다.
http://www.sqlfiddle.com/ #!2/0a4ae/1 에서 테스트 할 수 있습니다.
샘플 데이터
mysql> SELECT * from topten;
+------+------+---------------------+--------+----------+
| id | home | datetime | player | resource |
+------+------+---------------------+--------+----------+
| 1 | 10 | 2009-04-03 00:00:00 | john | 399 |
| 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 |
| 3 | 10 | 2009-03-03 00:00:00 | john | 300 |
| 4 | 11 | 2009-03-03 00:00:00 | juliet | 200 |
| 5 | 12 | 2009-04-03 00:00:00 | borat | 555 |
| 6 | 12 | 2009-03-03 00:00:00 | borat | 500 |
| 7 | 13 | 2008-12-24 00:00:00 | borat | 600 |
| 8 | 13 | 2009-01-01 00:00:00 | borat | 700 |
| 9 | 10 | 2009-04-03 00:00:00 | borat | 700 |
| 10 | 11 | 2009-04-03 00:00:00 | borat | 700 |
| 12 | 12 | 2009-04-03 00:00:00 | borat | 700 |
+------+------+---------------------+--------+----------+
사용자 변수가 있는 MySQL 버전
SELECT *
FROM (
SELECT ord.*,
IF (@prev_home = ord.home, 0, 1) AS is_first_appear,
@prev_home := ord.home
FROM (
SELECT t1.id, t1.home, t1.player, t1.resource
FROM topten t1
INNER JOIN (
SELECT home, MAX(datetime) AS mx_dt
FROM topten
GROUP BY home
) x ON t1.home = x.home AND t1.datetime = x.mx_dt
ORDER BY home
) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE is_first_appear = 1;
+------+------+--------+----------+-----------------+------------------------+
| id | home | player | resource | is_first_appear | @prev_home := ord.home |
+------+------+--------+----------+-----------------+------------------------+
| 9 | 10 | borat | 700 | 1 | 10 |
| 10 | 11 | borat | 700 | 1 | 11 |
| 12 | 12 | borat | 700 | 1 | 12 |
| 8 | 13 | borat | 700 | 1 | 13 |
+------+------+--------+----------+-----------------+------------------------+
4 rows in set (0.00 sec)
수락된 답변 출력되었습니다.
SELECT tt.*
FROM topten tt
INNER JOIN
(
SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home
) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
+------+------+---------------------+--------+----------+
| id | home | datetime | player | resource |
+------+------+---------------------+--------+----------+
| 1 | 10 | 2009-04-03 00:00:00 | john | 399 |
| 2 | 11 | 2009-04-03 00:00:00 | juliet | 244 |
| 5 | 12 | 2009-04-03 00:00:00 | borat | 555 |
| 8 | 13 | 2009-01-01 00:00:00 | borat | 700 |
| 9 | 10 | 2009-04-03 00:00:00 | borat | 700 |
| 10 | 11 | 2009-04-03 00:00:00 | borat | 700 |
| 12 | 12 | 2009-04-03 00:00:00 | borat | 700 |
+------+------+---------------------+--------+----------+
7 rows in set (0.00 sec)
SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)
SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)
기본적으로 그룹당 각 행의 순위를 계산한 다음 가장 최근 행을 필터링하는 하위 쿼리를 사용하여 그룹당 가장 최근 행을 = 1과 같이 사용하는 또 다른 방법입니다.
select a.*
from topten a
where (
select count(*)
from topten b
where a.home = b.home
and a.`datetime` < b.`datetime`
) +1 = 1
다음은 이해를 돕기 위해 각 행의 순위 번호에 대한 시각적 데모입니다.
코멘트를 읽어 보면, 같은 「home」필드와 「datetime」필드의 값을 가지는 행이 2개 있는 경우는 어떻습니까.
위의 쿼리는 실패하고 위의 상황에서 1개 이상의 행을 반환합니다.이 상황을 덮으려면 위의 상황에 해당하는 행을 결정하기 위한 다른 기준/매개 변수/열이 필요합니다. 키 할 수 있습니다.idauto increment(자동 증분)이할 수 있습니다.이 컬럼을 사용하여 합니다.CASE 것
select a.*
from topten a
where (
select count(*)
from topten b
where a.home = b.home
and case
when a.`datetime` = b.`datetime`
then a.id < b.id
else a.`datetime` < b.`datetime`
end
) + 1 = 1
중 합니다.datetime 「」을 참조해 주세요.
사용하지 않는 이유: SELECT home, MAX(datetime) AS MaxDateTime, player, resource FROM TOPTen GROUP BY home ?내가 뭘 놓쳤나?
MySQL 8.0에서는 row_number() window 함수를 공통 테이블 식과 함께 사용하면 효율적으로 이를 수행할 수 있습니다.
(여기서 row_number()는 기본적으로 자원의 내림차순으로 1부터 시작하는 각 플레이어의 행에 대해 고유한 시퀀스를 생성합니다.따라서 시퀀스 번호가 1인 모든 플레이어 행에 대해 리소스 값이 가장 높습니다.이제 각 플레이어의 시퀀스 번호가 1인 행을 선택하면 됩니다.이 쿼리 주위에 외부 쿼리를 작성하면 됩니다.단, 보다 읽기 쉽기 때문에 일반적인 표 표현을 사용했습니다.)
스키마:
create TABLE TestTable(id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT);
INSERT INTO TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700
쿼리:
with cte as
(
select id, home, date , player, resource,
Row_Number()Over(Partition by home order by date desc) rownumber from TestTable
)
select id, home, date , player, resource from cte where rownumber=1
출력:
| 아이디 | 집입니다 | 날짜. | 플레이어 | 자원 |
|---|---|---|---|---|
| 1 | 10 | 2009-03-04 00:00:00 | 존. | 399 |
| 2 | 11 | 2009-03-04 00:00:00 | 줄리엣 | 244 |
| 5 | 12 | 2009-03-04 00:00:00 | 보랏토 | 555 |
| 8 | 13 | 2009-01-01 00:00:00 | 보랏토 | 700 |
db <>여기에 추가
@Michae 대부분의 경우, 받아들여진 답변은 정상적으로 동작합니다만, 아래와 같은 경우는 실패합니다.
두 줄에 홈이 있는 경우ID 및 Datetime은 쿼리가 서로 다른 홈이 아닌 두 행을 반환합니다.필요에 따라 ID를 쿼리에 다음과 같이 구별을 추가합니다.
SELECT DISTINCT tt.home , tt.MaxDateTime
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
이거 드셔보세요
select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
on a.home = b.home and a.datetime = b.datetime
K에 대하여
필요한 쿼리는 다음과 같습니다.
SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM
(SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a
LEFT JOIN
(SELECT id,home,[datetime],player,resource FROM tbl_1) AS b
ON a.resource = b.resource WHERE a.home =b.home;
아래 쿼리에서 원하는 출력을 얻을 수 있기를 바랍니다.
Select id, home,datetime,player,resource, row_number() over (Partition by home ORDER by datetime desc) as rownum from tablename where rownum=1
(메모: 마이클의 답변은 타겟 칼럼이 다음과 같은 상황에서 완벽합니다.datetime 구별되는 각 값에 할 수 .home
테이블에 x 행이datetime 중복되어 있고 각 개별 열에 대해 하나의 행만 선택해야 하는 경우 다음과 같은 해결 방법이 있습니다.
한 열: your이 ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ()이 필요합니다.id그렇지 않은 경우 보기를 만들고 임의의 열을 추가합니다.
하여 원하는 각 단일 합니다.home 값을 합니다. 이치노id되는 datetime.
SELECT tt.*
FROM topten tt
INNER JOIN
(
SELECT min(id) as min_id, home from topten tt2
INNER JOIN
(
SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt2
ON tt2.home = groupedtt2.home
) as groupedtt
ON tt.id = groupedtt.id
날짜와 집이 같은 레코드가 2개 있는 경우 수락된 답변은 작동하지 않습니다.가입 후 2개의 레코드가 반환됩니다.그 중 하나를 골라야 하는데.이 쿼리는 결합된 하위 쿼리로 사용되므로 제한 1만 사용할 수 없습니다.이것이 내가 원하는 결과를 얻었던 방법이다.하지만 성능에 대해서는 잘 모른다.
select SUBSTRING_INDEX(GROUP_CONCAT(id order by datetime desc separator ','),',',1) as id, home, MAX(datetime) as 'datetime'
from topten
group by (home)
이것은 아직 공개되지 않았습니다.SQLServer에서 동작하며 서브쿼리나 CTE를 필요로 하지 않는 유일한 솔루션입니다.이것이 이러한 문제를 해결하는 가장 우아한 방법이라고 생각합니다.
SELECT TOP 1 WITH TIES *
FROM TopTen
ORDER BY ROW_NUMBER() OVER (PARTITION BY home
ORDER BY [datetime] DESC)
- 은 'Order By' - 'Order By' 창의 각 합니다.home이 가장이 [datetime]를 하는 값1.
★★★★★★★★★★★★★★★★의SELECTTOP 1 WITH TIES첫 번째 ROW_NUMBER 값(1)을 가진 레코드 및 동일한 ROW_NUMBER 값이 1인 다른 모든 레코드를 선택합니다.
그 결과, 1위의 각 레코드의 모든 데이터를 취득합니다.
언급URL : https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys
'programing' 카테고리의 다른 글
| Python에서 트리를 구현하려면 어떻게 해야 하나요? (0) | 2022.10.23 |
|---|---|
| PHP MYSQL UPDATE(존재하는 경우) 또는 INSERT(존재하지 않는 경우)? (0) | 2022.10.23 |
| Vuetify 그리드를 사용하여 v-for 루프의 저장소 항목을 표시하는 방법 (0) | 2022.10.23 |
| Java 열거형에서 valueof() 및 toString()을 덮어씁니다. (0) | 2022.10.23 |
| PHP - 두 배열이 동일한지 확인합니다. (0) | 2022.10.23 |