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

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

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

날짜	이름
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	1059
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	1666
9176	group by 를 하고 싶은데요. [3]	DOOLLY	2016.02.16	2193
9175	재귀쿼리를 통해 트리구조를 가져올수있게 하는 쿼리를 작성하였는데 시간이 느립니다.	뽀구	2016.02.16	2414
9174	[질문] 오라클 start with ~ connect by 쿼리문입니다. [1]	매발톱	2016.02.16	2283
9173	case when 질문입니다. [2]	큘러	2016.02.16	2319
9172	쿼리문의	방자	2016.02.16	1923
9171	날짜를 기준으로 사전과 사후 나누기... [2]	DOOLLY	2016.02.15	2170
9170	태스크의 스크립트 생성을 SQL문이나 에이전트로 실행할 수 없을까요? [1]	sincie	2016.02.15	2194
9169	정수 올림에 관해 질문입니다. [3]	leesoek	2016.02.11	2559
9168	쿼리에서 특정시간을 기점으로 결과값을 다르게 받는게 가능할까요? [4]	고구망	2016.02.11	2390
9167	조회조건이 포함된 선입선출 형태 질문~ [3]	정민채	2016.02.11	2561
9166	group by 를 할때.. case 로... [2]	DOOLLY	2016.02.11	2231
9165	오라클 쿼리가 안되요 ㅠㅠ [1]	올해는2016	2016.02.09	2716
9164	SQL Server Agent가 구동이 안되서	크라우저2세	2016.02.08	2395
9163	MSSQL 2014 버전 DB를 2008 마이그레이션 방법 [2]	마찡가	2016.02.05	2604
9162	날짜가 같은 필드에 특정 카테고리 번호를 순서대로 붙이는 쿼리 질문좀 드립니다. [3]	다자녀아빠	2016.02.04	2355
»	데이터 50건뽑기...조건이 좀 애매합니다..ㅠㅠ [9]	SQL왕왕초보	2016.02.04	2619
9160	Oracle 쿼리 문의사항 [4]	손원일	2016.02.04	2847
9159	닷넷 윈도우 프로그램 개발 시 db 보안 [2]	청소하자	2016.02.04	2490
9158	SQL 서버 운용관련 질문 [2]	HSQL	2016.02.04	2201
9157	다른이름 DB 로 시점 복구(STOPAT) 문의 드립니다. [2]	구로동라이더	2016.02.04	3914

Write Tag

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

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

Comment 9

SQL왕왕초보

Terry

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

Terry

항해자™

SQL왕왕초보

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발