그룹별 데이타 출력

1. 주문데이타중 주문번호 중복데이타 출력

select * from s_order where ordernum in (
SELECT a.ordernum
FROM s_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order

GROUP BY ordernum HAVING count(orderno) >1 ) B ON A.uid = B.oiuid
)

2. 중복데이터중 순번작은것만 출력 [ MIN(uid) ]

SELECT *
FROM shop_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order

GROUP BY ordernumHAVING count(ordernum) >1 ) B
ON A.uid = B.oiuid

3. 주문번호 중복데이타중 작은순번 1개의 데이타를 제외한 나머지 출력

select * from (
select * from s_order where ordernumin (
SELECT a.orderno
FROM s_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order GROUP BY ordernumHAVING count(ordernum) >1 ) B
ON A.uid = B.oiuid
)
) as so
where so.uid not in (
SELECT uid
FROM s_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order GROUP BY ordernumHAVING count(ordernum) >1 ) B
ON A.uid = B.oiuid
)

4. 중복데이타 삭제

delete from s_order where uid in
(
select uid from (
select * from s_order where orderno in (
SELECT a.orderno
FROM s_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order GROUP BY ordernumHAVING count(ordernum) >1 ) B
ON A.uid = B.oiuid
)
) as so
where so.uid not in (
SELECT uid
FROM s_order A INNER JOIN ( SELECT MIN(uid) AS oiuid FROM s_order GROUP BY ordernumHAVING count(ordernum) >1 ) B
ON A.uid = B.oiuid
)
)