데이터 50건뽑기...조건이 좀 애매합니다..ㅠㅠ

SQL왕왕초보 2016.02.04 16:26 Views : 2617

안녕하세요 궁금한 사항이 잇어서 오랫만에 글 남깁니다.....

날짜	이름
20160101	홍길동
20160102	홍길동
20160103	이순신
20160104	김철민
20160105	김철수
20160106	이철수

A테이블안에 데이터 껀수는 대략 2만건정도 되며...

이름종류가 무수히 많습니다...

SELECT 시 각 이름별로 최신날짜기준 50건씩 뽑고싶습니다...

SELECT TOP 50으로 한명한명 이름을 줘서 뽑을순 있지만 전체적으로 다 뽑아야되기때문에 불가능하여...

여러가지 방법을 찾아봤지만 찾을수가 없어서...글을 남깁니다....

매번 글을 보며 많은걸 배우고 감사함을 느낍니다...

이번에도 선배님들 조언이 부탁합니다 수고하세요...

I want to

cmd_document_vote_user Update Log List

Comment 9

SQL왕왕초보

2016.02.04 16:37

아 조건이 하나 빠졌습니다....50건이 안되는데이터는 있는수만큼만 불러와야합니다...

cmd_comment_vote_user Upvote0 Downvote0
Terry

2016.02.04 17:00

하기 쿼리 참고하세요..

---쿼리시작---

;with tblA(날짜,이름) As
(
      Select '20160101','홍길동' Union All
      Select '20160102','홍길동' Union All
      Select '20160103','홍길동' Union All
      Select '20160104','홍길동' Union All
      Select '20160105','홍길동' Union All
      Select '20160106','홍길동' Union All
      Select '20160107','홍길동' Union All
      Select '20160108','홍길동' Union All
      Select '20160109','홍길동' Union All
      Select '20160110','홍길동' Union All
      Select '20160111','홍길동' Union All
      Select '20160112','홍길동' Union All
      Select '20160113','홍길동' Union All
      Select '20160114','홍길동' Union All
      Select '20160115','홍길동' Union All
      Select '20160116','홍길동' Union All
      Select '20160117','홍길동' Union All
      Select '20160118','홍길동' Union All
      Select '20160119','홍길동' Union All
      Select '20160120','홍길동' Union All
      Select '20160121','홍길동' Union All
      Select '20160122','홍길동' Union All
      Select '20160123','홍길동' Union All
      Select '20160124','홍길동' Union All
      Select '20160125','홍길동' Union All
      Select '20160126','홍길동' Union All
      Select '20160127','홍길동' Union All
      Select '20160128','홍길동' Union All
      Select '20160129','홍길동' Union All
      Select '20160130','홍길동' Union All
      Select '20160131','홍길동' Union All
      Select '20160201','홍길동' Union All
      Select '20160202','홍길동' Union All
      Select '20160203','홍길동' Union All
      Select '20160204','홍길동' Union All
      Select '20160205','홍길동' Union All
      Select '20160206','홍길동' Union All
      Select '20160207','홍길동' Union All
      Select '20160208','홍길동' Union All
      Select '20160209','홍길동' Union All
      Select '20160210','홍길동' Union All
      Select '20160211','홍길동' Union All
      Select '20160212','홍길동' Union All
      Select '20160213','홍길동' Union All
      Select '20160214','홍길동' Union All
      Select '20160215','홍길동' Union All
      Select '20160216','홍길동' Union All
      Select '20160217','홍길동' Union All
      Select '20160218','홍길동' Union All
      Select '20160219','홍길동' Union All
      Select '20160220','홍길동' Union All
      Select '20160221','홍길동' Union All
      Select '20160222','홍길동' Union All
      Select '20160223','홍길동' Union All
      Select '20160224','홍길동' Union All
      Select '20160225','홍길동' Union All
      Select '20160226','홍길동' Union All
      Select '20160227','홍길동' Union All
      Select '20160228','홍길동' Union All
      Select '20160229','홍길동' Union All
      Select '20160230','홍길동' Union All
      Select '20160231','홍길동' Union All
      Select '20160232','홍길동' Union All
      Select '20160233','홍길동' Union All
      Select '20160234','홍길동' Union All
      Select '20160235','홍길동' Union All
      Select '20160236','홍길동' Union All
      Select '20160237','홍길동' Union All
      Select '20160238','홍길동' Union All
      Select '20160239','홍길동' Union All
      Select '20160240','홍길동' Union All
      Select '20160241','홍길동' Union All
      Select '20160242','홍길동' Union All
      Select '20160243','홍길동' Union All
      Select '20160244','홍길동' Union All
      Select '20160245','홍길동' Union All
      Select '20160246','홍길동' Union All
      Select '20160247','홍길동' Union All
      Select '20160248','홍길동' Union All
      Select '20160249','홍길동' Union All
      Select '20160250','홍길동' Union All
      Select '20160251','홍길동' Union All
      Select '20160252','홍길동' Union All
      Select '20160253','홍길동' Union All
      Select '20160254','홍길동' Union All
      Select '20160255','홍길동' Union All
      Select '20160256','홍길동' Union All
      Select '20160257','홍길동' Union All
      Select '20160258','홍길동' Union All
      Select '20160259','홍길동' Union All
      Select '20160260','홍길동' Union All
      Select '20160261','홍길동' Union All
      Select '20160262','홍길동' Union All
      Select '20160263','홍길동' Union All
      Select '20160264','이순신' Union All
      Select '20160101','이순신' Union All
      Select '20160102','이순신' Union All
      Select '20160103','이순신' Union All
      Select '20160104','이순신' Union All
      Select '20160105','이순신' Union All
      Select '20160106','이순신' Union All
      Select '20160107','이순신' Union All
      Select '20160108','이순신' Union All
      Select '20160109','이순신' Union All
      Select '20160110','이순신' Union All
      Select '20160111','이순신' Union All
      Select '20160112','이순신' Union All
      Select '20160113','이순신' Union All
      Select '20160114','이순신' Union All
      Select '20160115','이순신' Union All
      Select '20160116','이순신' Union All
      Select '20160117','이순신' Union All
      Select '20160118','이순신' Union All
      Select '20160119','이순신' Union All
      Select '20160120','이순신' Union All
      Select '20160121','이순신' Union All
      Select '20160122','이순신' Union All
      Select '20160123','이순신' Union All
      Select '20160124','이순신' Union All
      Select '20160125','이순신' Union All
      Select '20160126','이순신' Union All
      Select '20160127','이순신' Union All
      Select '20160128','이순신' Union All
      Select '20160129','이순신' Union All
      Select '20160130','이순신' Union All
      Select '20160131','이순신' Union All
      Select '20160201','이순신' Union All
      Select '20160202','이순신' Union All
      Select '20160203','이순신' Union All
      Select '20160204','이순신' Union All
      Select '20160205','이순신' Union All
      Select '20160206','이순신' Union All
      Select '20160207','이순신' Union All
      Select '20160208','이순신' Union All
      Select '20160209','이순신' Union All
      Select '20160210','이순신' Union All
      Select '20160211','이순신' Union All
      Select '20160212','이순신' Union All
      Select '20160213','이순신' Union All
      Select '20160214','이순신' Union All
      Select '20160215','이순신' Union All
      Select '20160216','이순신' Union All
      Select '20160217','이순신' Union All
      Select '20160218','이순신' Union All
      Select '20160219','이순신' Union All
      Select '20160220','이순신' Union All
      Select '20160221','이순신' Union All
      Select '20160222','이순신' Union All
      Select '20160223','이순신' Union All
      Select '20151201','이순신' Union All
      Select '20151202','이순신' Union All
      Select '20151203','이순신' Union All
      Select '20151204','이순신' Union All
      Select '20151205','이순신' Union All
      Select '20151206','이순신' Union All
      Select '20151207','이순신' Union All
      Select '20151208','이순신' Union All
      Select '20151209','이순신' Union All
      Select '20151210','이순신' Union All
      Select '20151211','이순신' Union All
      Select '20151212','이순신' Union All
      Select '20151213','이순신' Union All
      Select '20151214','이순신' Union All
      Select '20151215','이순신' Union All
      Select '20151216','이순신' Union All
      Select '20151217','이순신' Union All
      Select '20151218','이순신' Union All
      Select '20151219','이순신' Union All
      Select '20151220','이순신' Union All
      Select '20151221','이순신' Union All
      Select '20151222','이순신' Union All
      Select '20151223','이순신' Union All
      Select '20151224','이순신' Union All
      Select '20151225','이순신' Union All
      Select '20151226','이순신' Union All
      Select '20151227','이순신' Union All
      Select '20151228','이순신' Union All
      Select '20151229','이순신' Union All
      Select '20151230','이순신' Union All
      Select '20151231','김철민' Union All
      Select '20160104','김철수' Union All
      Select '20160105','김철수' Union All
      Select '20160106','김철수' Union All
      Select '20160107','김철수' Union All
      Select '20160108','김철수' Union All
      Select '20160109','김철수' Union All
      Select '20160110','김철수' Union All
      Select '20160111','김철수' Union All
      Select '20160112','김철수' Union All
      Select '20160113','김철수' Union All
      Select '20160114','김철수' Union All
      Select '20160115','김철수' Union All
      Select '20160116','김철수' Union All
      Select '20160117','김철수' Union All
      Select '20160118','김철수' Union All
      Select '20160119','김철수' Union All
      Select '20160120','김철수' Union All
      Select '20160121','김철수' Union All
      Select '20160122','김철수' Union All
      Select '20160123','김철수' Union All
      Select '20160124','김철수' Union All
      Select '20160125','김철수' Union All
      Select '20160126','김철수' Union All
      Select '20160127','김철수' Union All
      Select '20160128','김철수' Union All
      Select '20160129','김철수' Union All
      Select '20160130','김철수' Union All
      Select '20160131','김철수' Union All
      Select '20160201','김철수' Union All
      Select '20160202','김철수' Union All
      Select '20160203','김철수' Union All
      Select '20160204','김철수' Union All
      Select '20160205','김철수' Union All
      Select '20160206','김철수' Union All
      Select '20160207','김철수' Union All
      Select '20160208','김철수' Union All
      Select '20160209','김철수' Union All
      Select '20160210','김철수' Union All
      Select '20160211','김철수' Union All
      Select '20160212','김철수' Union All
      Select '20160213','김철수' Union All
      Select '20160214','김철수' Union All
      Select '20160215','김철수' Union All
      Select '20160216','김철수' Union All
      Select '20160217','김철수' Union All
      Select '20160218','김철수' Union All
      Select '20160219','김철수' Union All
      Select '20160220','이철수' Union All
      Select '20160106','이철수' Union All
      Select '20160107','이철수' Union All
      Select '20160108','이철수' Union All
      Select '20160109','이철수' Union All
      Select '20160110','이철수' Union All
      Select '20160111','이철수' Union All
      Select '20160112','이철수' Union All
      Select '20160113','이철수'
)
      Select a.날짜
            ,a.이름
        From (
                   Select a.*
                         ,ROW_NUMBER() Over (Partition By a.이름 Order By a.날짜 Desc) As rn
                     From tblA a
             ) a
       Where a.rn <= 50
    Order By a.이름 Asc
            ,a.날짜 Desc

---쿼리끝---

cmd_comment_vote_user Upvote0 Downvote0
SQL왕왕초보

2016.02.05 09:44

답변감사합니다...위와같은 방식은 데이량이 많아 불가해보일듯합니다...

아니면 제가 이해를 잘못하는지...ㅠ_ㅠ 소중한 답변 감사드립니다...

cmd_comment_vote_user Upvote0 Downvote0
Terry

2016.02.05 11:02

데이터량이 많아지진 않을겁니다^^;

with 구문에 있는건 테이블과 동일한 역할을 하구요..

상기 덧글에 작성한 쿼리중에서 실제 사용이 되는 쿼리는

하기와 같습니다.

tblA 대신에 실제 사용하는 테이블명

컬럼들에는 해당 테이블의 실제 사용되는 컬럼명들을 적어주시면 될듯하네요..

그럼 수고하세요~~

---쿼리시작---

    Select a.날짜
            ,a.이름
        From (
                   Select a.*
                         ,ROW_NUMBER() Over (Partition By a.이름 Order By a.날짜 Desc) As rn
                     From tblA a
             ) a
       Where a.rn <= 50
---쿼리끝---

cmd_comment_vote_user Upvote0 Downvote0
항해자™

2016.02.04 18:27

select b.*
from dbo.tSample as a
cross apply (
select top 50 *
from dbo.tSample
where cName = a.cName
order by cDate
) as b

cmd_comment_vote_user Upvote0 Downvote0
SQL왕왕초보

2016.02.05 09:45

안녕하세요 항해자님

위와같은 방식으로 쿼리 진행 시 실 데이터량이 늘어나게됩니다..

제 설명이 이상했던걸까요..ㅜ.ㅜ

데이터량은 그대로 유지하고 최대 50건씩 뽑아내고 50건이 안되는데이터는 최대건수만 표현하고 싶은데..

글로는 표현이 이상하네요...

소중한 답변 감사드립니다...

cmd_comment_vote_user Upvote0 Downvote0
Terry

2016.02.05 11:08

      Select b.날짜
            ,b.이름
        From tblA a

                             Cross Apply

                                                    (

                                                              Select

                                                               Top 50 b.*

                                                                 From tblA b

                                                               Where b.이름 = a.이름

                                                           Order By b.날짜 Desc

                                                    ) b

       Group By b.날짜

                       ,b.이름

cmd_comment_vote_user Upvote0 Downvote0
항해자™

2016.02.05 22:03

제가 실수를 했네요;;
;with cteSample as (
select cName
from dbo.tSample
group by cName
)
select b.*
from cteSample as a
cross apply (
select top 50 *
from dbo.tSample
where cName = a.cName
order by cDate desc
) as b

cmd_comment_vote_user Upvote0 Downvote0
SQL왕왕초보

2016.02.26 09:01

많은 댓글 감사드립니다...

위 내용 참고하여 처리가 되었습니다....

다시한번 소중한 답변 감사드립니다...^^

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	2023년 1월 - SQLER의 업데이트 강좌 리스트	코난(김대우)	2023.01.02	1254
»	데이터 50건뽑기...조건이 좀 애매합니다..ㅠㅠ [9]	SQL왕왕초보	2016.02.04	2617
9160	Oracle 쿼리 문의사항 [4]	손원일	2016.02.04	2846
9159	닷넷 윈도우 프로그램 개발 시 db 보안 [2]	청소하자	2016.02.04	2470
9158	SQL 서버 운용관련 질문 [2]	HSQL	2016.02.04	2198
9157	다른이름 DB 로 시점 복구(STOPAT) 문의 드립니다. [2]	구로동라이더	2016.02.04	3894
9156	서버이전후 백업시간 증가 [3]	류종근	2016.02.04	2653
9155	날짜검색 쿼리 문의드려요 [2]	Sisylian	2016.02.03	2893
9154	join 문을 처음 해보는데 잘안됩니다.. [3]	슈토파이터	2016.02.03	2268
9153	데이타에서 IN 사용하기..? [6]	문상연	2016.02.02	2284
9152	JOIN할 테이블을 SELECT문으로 설정할 순 없을까요 [7]	sincie	2016.02.02	2595
9151	트랜잭션 로그 용량 오류 문의 [7]	솔총이	2016.02.01	3280
9150	mssql 질문 좀 드릴께요. 일정한 수의 행을 반복하면서 열로 변경하는 방법이 있을까요? [2]	시가넘버쓰리	2016.01.30	2816
9149	월별 ,년별 통계쿼리 질문 [4]	다자녀아빠	2016.01.29	2975
9148	이런 시스템에 트리거 ? CDC (Change Data Capture)? 어떤게..좋을까요? [1]	둥이가이	2016.01.29	3261
9147	hashbytes() 질문 [2]	형님	2016.01.29	3112
9146	MS SQL FAIL OVER시 쿼리에 대해서... [1]	그늘	2016.01.29	3310
9145	MySQL -> MS-SQL로 데이터베이스 마이그레이션 궁금증입니다. [2]	Gusto	2016.01.28	3967
9144	다국어(NVARCHAR) 에 대한 CHECKINDEX 문의 드립니다 [4]	구로동라이더	2016.01.28	3119
9143	무조건 프로시저로 만드는게 최선인가요? [4]	문상연	2016.01.27	2459
9142	SQL2012 SSMS에서 리프레쉬 단축키(F5) 질문입니다. [1]	sincie	2016.01.27	2301

Write Tag

First Page 83 84 85 86 87 88 89 90 91 92 Last Page

데이터 50건뽑기...조건이 좀 애매합니다..ㅠㅠ

Comment 9

SQL왕왕초보

Terry

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발