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

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

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

날짜	이름
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	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36240
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	18821
9163	MSSQL 2014 버전 DB를 2008 마이그레이션 방법 [2]	마찡가	2016.02.05	2632
9162	날짜가 같은 필드에 특정 카테고리 번호를 순서대로 붙이는 쿼리 질문좀 드립니다. [3]	다자녀아빠	2016.02.04	2377
»	데이터 50건뽑기...조건이 좀 애매합니다..ㅠㅠ [9]	SQL왕왕초보	2016.02.04	2648
9160	Oracle 쿼리 문의사항 [4]	손원일	2016.02.04	2877
9159	닷넷 윈도우 프로그램 개발 시 db 보안 [2]	청소하자	2016.02.04	2516
9158	SQL 서버 운용관련 질문 [2]	HSQL	2016.02.04	2234
9157	다른이름 DB 로 시점 복구(STOPAT) 문의 드립니다. [2]	구로동라이더	2016.02.04	3965
9156	서버이전후 백업시간 증가 [3]	류종근	2016.02.04	2704
9155	날짜검색 쿼리 문의드려요 [2]	Sisylian	2016.02.03	2922
9154	join 문을 처음 해보는데 잘안됩니다.. [3]	슈토파이터	2016.02.03	2300
9153	데이타에서 IN 사용하기..? [6]	문상연	2016.02.02	2311
9152	JOIN할 테이블을 SELECT문으로 설정할 순 없을까요 [7]	sincie	2016.02.02	2630
9151	트랜잭션 로그 용량 오류 문의 [7]	솔총이	2016.02.01	3370
9150	mssql 질문 좀 드릴께요. 일정한 수의 행을 반복하면서 열로 변경하는 방법이 있을까요? [2]	시가넘버쓰리	2016.01.30	2877
9149	월별 ,년별 통계쿼리 질문 [4]	다자녀아빠	2016.01.29	5751
9148	이런 시스템에 트리거 ? CDC (Change Data Capture)? 어떤게..좋을까요? [1]	둥이가이	2016.01.29	3294
9147	hashbytes() 질문 [2]	형님	2016.01.29	3153
9146	MS SQL FAIL OVER시 쿼리에 대해서... [1]	그늘	2016.01.29	3345
9145	MySQL -> MS-SQL로 데이터베이스 마이그레이션 궁금증입니다. [2]	Gusto	2016.01.28	4103
9144	다국어(NVARCHAR) 에 대한 CHECKINDEX 문의 드립니다 [4]	구로동라이더	2016.01.28	3155

Write Tag

First Page 86 87 88 89 90 91 92 93 94 95 Last Page

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

Comment 9

SQL왕왕초보

Terry

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발