mallNo | adminNo | type
0 | 10 | A
1 | 90 | A
2 | 10 | B
2 | 10 | C
type 이 A로 그룹핑해서 mallNo 가 큰거에 대한 데이터를 노출시키고싶거든요. (2번째 행이 되겠죠)
type 이 A 인거에 대한 mallno 가 0, 1 두개가있는데 1이 더 크니 adminNO가 90인거만 노출시키고싶어요. 이 조건이 중요합니다.
mallNo | adminNo | type
1 | 90 | A
2 | 10 | B
2 | 10 | C
이런식으로요.
이거 쉽게 해결될거같은데 잘안되네요.ㅠㅠ
고수님들 부탁드립니다!
셀프 조인이나 mysql 버전이 8.0이시면 row_number 함수를 써보시죠~
select mailno, adminno, type
FROM
(
SELECT t1.mailno, t1.adminno, t1.type, count(*) as rn
FROM test as t1, test as t2
WHERE t1.type = t2.type and
t1.mailno <= t2.mailno
GROUP BY t1.mailno, t1.adminno, t1.type
) as t
where rn = 1
select mailno, adminno, type
FROM
(
SELECT mailno, adminno, type, row_number() over (partition by type order by adminno desc) as rn
FROM test
) as t
where rn = 1
-- 분석함수 SELECT mallNo, adminNo, type FROM (SELECT mallNo, adminNo, type , ROW_NUMBER() OVER(PARTITION BY type ORDER BY mallNo DESC) rn FROM t ) a WHERE rn = 1 ; -- 집계결과와 조인 SELECT a.* FROM t a INNER JOIN (SELECT type , MAX(mallNo) mallNo FROM t GROUP BY type ) b ON a.type = b.type AND a.mallNo = b.mallNo ; -- IN SELECT * FROM t WHERE (type, mallNo) IN (SELECT type , MAX(mallNo) mallNo FROM t GROUP BY type ) ; -- Not Exists SELECT * FROM t a WHERE NOT EXISTS (SELECT 1 FROM t b WHERE b.type = a.type AND b.mallNo > a.mallNo ) ; -- 셀프 아우터 조인 후 Null 체크 SELECT a.* FROM t a LEFT OUTER JOIN t b ON a.type = b.type AND a.mallNo < b.mallNo WHERE b.type IS NULL ;