요구 사항은 날짜별로 구별되고 최신 인 기록을 가져 오는 것입니다. 바구니 ID는 같지만 다른 정보는 고유 한 레코드가 있습니다. 아래의 접근 방식에 따라 바구니 ID별로 그룹화 된 설명 날짜로 정렬 된 레코드를 얻을 수 있지만 내 요구 사항을 충족시킬 정렬 된 레코드 중 TOP 1을 얻는 방법을 모르겠습니다. 이것이 어떻게 달성 될 수 있는지 제안하십시오.
ID Flower Country Date
==================================================
A12 Rose China 2016:08:18 16:02:19
A65 Turnip China 2016:08:25 18:25:54
A11 unknown China 2016:08:18 16:45:27
A43 angelica Mexico 2016:08:25 18:25:54
A43 Rose Mexico 2016:08:25 15:46:35
A43 angelica Mexico 2016:08:19 12:33:43
A43 Rose Mexico 2016:08:18 16:23:01
A44 solomon Denmark 2016:08:22 18:49:10
A44 Turnip Denmark 2016:08:18 09:44:06
A49 duetzia Mexico 2016:08:23 21:25:38
A49 Rose Mexico 2016:08:23 18:54:55
A49 angelica Mexico 2016:08:19 12:23:58
A49 Rose Mexico 2016:08:18 16:23:01
B15 solomon Denmark 2016:08:22 18:58:35
B43 solomon Denmark 2016:08:24 20:00:14
B43 Turnip Denmark 2016:08:22 13:57:08
B43 impCalamus Denmark 2016:08:18 17:28:53
B43 impCalamus Denmark 2016:08:18 15:40:45
B45 solomon Denmark 2016:08:24 20:00:14
Expected output
===============
ID Flower Country Date
=============================================
A11 unknown China 2016:08:18 16:45:27
A12 Rose China 2016:08:18 16:02:19
A43 angelica Mexico 2016:08:25 18:25:54
A44 solomon Denmark 2016:08:22 18:49:10
A49 duetzia Mexico 2016:08:23 21:25:38
A65 Turnip China 2016:08:25 18:25:54
B15 solomon Denmark 2016:08:22 18:58:35
B43 solomon Denmark 2016:08:24 20:00:14
B45 solomon Denmark 2016:08:24 20:00:14
Steps to create the Table.
---------------------------
CREATE TABLE FLOWER( ID varchar(12) , FLOWER varchar(12), COUNTRY varchar(12), DATE TIME);
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A12', 'Rose', 'China', '2016:08:18 16:02:19' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A65', 'Turnip' , 'China' , '2016:08:25 18:25:54' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A11', 'unknown' , 'China' , '2016:08:18 16:45:27' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A43', 'angelica' , 'Mexico' , '2016:08:25 18:25:54' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A43', 'Rose' , 'Mexico' , '2016:08:25 15:46:35' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A43', 'angelica' , 'Mexico' , '2016:08:19 12:33:43' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A43', 'Rose' , 'Mexico' , '2016:08:18 16:23:01' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A44', 'solomon' , 'Denmark' , '2016:08:22 18:49:10' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A44', 'Turnip' , 'Denmark' , '2016:08:18 09:44:06' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A49', 'duetzia' , 'Mexico' , '2016:08:23 21:25:38' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A49', 'Rose' , 'Mexico' , '2016:08:23 18:54:55' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A49', 'angelica' , 'Mexico' , '2016:08:19 12:23:58' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('A49', 'Rose' , 'Mexico' , '2016:08:18 16:23:01' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B15', 'solomon' , 'Denmark' , '2016:08:22 18:58:35' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B43', 'solomon' , 'Denmark' , '2016:08:24 20:00:14' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B43', 'Turnip' , 'Denmark' , '2016:08:22 13:57:08' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B43', 'impCalamus' , 'Denmark' , '2016:08:18 17:28:53' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B43', 'impCalamus' , 'Denmark' , '2016:08:18 15:40:45' );
INSERT INTO FLOWER (ID,FLOWER,COUNTRY,DATE) VALUES ('B45', 'solomon' , 'Denmark' , '2016:08:24 20:00:14' );
My Approach
===========
select * from flower group by id, date order by id ,date desc;
ID FLOWER COUNTRY DATE
---------- ---------- ---------- -------------------
A11 unknown China 2016:08:18 16:45:27 <---
A12 Rose China 2016:08:18 16:02:19 <---
A43 angelica Mexico 2016:08:25 18:25:54 <---
A43 Rose Mexico 2016:08:25 15:46:35
A43 angelica Mexico 2016:08:19 12:33:43
A43 Rose Mexico 2016:08:18 16:23:01
A44 solomon Denmark 2016:08:22 18:49:10 <---
A44 Turnip Denmark 2016:08:18 09:44:06
A49 duetzia Mexico 2016:08:23 21:25:38 <---
A49 Rose Mexico 2016:08:23 18:54:55
A49 angelica Mexico 2016:08:19 12:23:58
A49 Rose Mexico 2016:08:18 16:23:01
A65 Turnip China 2016:08:25 18:25:54 <---
B15 solomon Denmark 2016:08:22 18:58:35 <---
B43 solomon Denmark 2016:08:24 20:00:14 <---
B43 Turnip Denmark 2016:08:22 13:57:08
B43 impCalamus Denmark 2016:08:18 17:28:53
B43 impCalamus Denmark 2016:08:18 15:40:45
B45 solomon Denmark 2016:08:24 20:00:14 <---
화살표 <----로 표시된 항목은 내가 관심있는 항목입니다.
where절 및 상관 하위 쿼리를 사용하여이를 수행 할 수 있습니다 .
select t.*
from t
where t.time = (select max(t2.time)
from t t2
where t2.id = t.id
);
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다