| Home | Print | Q/A | Guest | NewsLetter |
|
[edit]
1 개요 #다음의 테이블에서 결과와 같이 그룹 중 첫 번째 값만 나타내게 하는 패턴이다. 일반적으로 보고서 작성시 쓰인다.
Seq GameType GameName ConnCount
----------- -------------------- -------------------- -----------
1 MMORPG 뮤 588456
2 MMORPG 리니지 25476
3 FPS 헉슬리 2352
4 MMORPG 썬 34576
5 FPS 스페셜포스 23452
6 MMORPG 십이지천 45234
7 CASUAL 파르페 34234
8 CASUAL 위키 12121
9 CASUAL 라그라로크 11111
--결과
GameType GameName ConnCount
-------------------- -------------------- -----------
CASUAL 파르페 588456
라그라로크 25476
위키 2352
FPS 스페셜포스 34576
헉슬리 23452
MMORPG 십이지천 45234
리니지 34234
뮤 12121
썬 11111
[edit]
2 테스트 데이터 #CREATE TABLE #temp( Seq int , GameType varchar(20) , GameName nvarchar(20) , ConnCount int ) INSERT #temp VALUES(1, 'MMORPG', N'뮤', 12121) INSERT #temp VALUES(2, 'MMORPG', N'리니지', 34234) INSERT #temp VALUES(3, 'FPS' , N'헉슬리', 23452) INSERT #temp VALUES(4, 'MMORPG', N'썬', 11111) INSERT #temp VALUES(5, 'FPS' , N'스페셜포스', 34576) INSERT #temp VALUES(6, 'MMORPG', N'십이지천', 45234) INSERT #temp VALUES(7, 'CASUAL', N'파르페', 588456) INSERT #temp VALUES(8, 'CASUAL', N'위키', 2352) INSERT #temp VALUES(9, 'CASUAL', N'라그라로크', 25476) [edit]
3 2000 버전의 솔루션 #use pubs
go
set statistics io on
select
isnull(b.type, '') type
, a.title_id
, a.title
from titles a
left outer join (
select
type
, min(title_id) title_id
from titles
group by type
) b
on a.title_id = b.title_id
/*
type title_id title
------------ -------- --------------------------------------------------------------------------------
business BU1032 The Busy Executive's Database Guide
BU1111 Cooking with Computers: Surreptitious Balance Sheets
BU2075 You Can Combat Computer Stress!
BU7832 Straight Talk About Computers
mod_cook MC2222 Silicon Valley Gastronomic Treats
MC3021 The Gourmet Microwave
UNDECIDED MC3026 The Psychology of Computer Cooking
popular_comp PC1035 But Is It User Friendly?
PC8888 Secrets of Silicon Valley
PC9999 Net Etiquette
psychology PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations
PS2091 Is Anger the Enemy?
PS2106 Life Without Fear
PS3333 Prolonged Data Deprivation: Four Case Studies
PS7777 Emotional Security: A New Algorithm
trad_cook TC3218 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
TC4203 Fifty Years in Buckingham Palace Kitchens
TC7777 Sushi, Anyone?
*/
select
isnull(b.GameType, '') GameType
, a.GameName
, a.ConnCount
from #temp a
left join (
select
GameType
, min(GameName) GameName
from #temp
group by GameType
) b
on a.GameName = b.GameName
order by a.GameType, a.GameName
/*
GameType GameName ConnCount
-------------------- -------------------- -----------
CASUAL 라그라로크 25476
위키 2352
파르페 588456
FPS 스페셜포스 34576
헉슬리 23452
MMORPG 리니지 34234
뮤 12121
십이지천 45234
썬 11111
*/
[edit]
4 2005 버전의 솔루션 #--2005 방식, 조낸 간단허다.. SELECT CASE WHEN RowNum > 1 THEN '' ELSE GameType END GameType , GameName , ConnCount FROM ( SELECT GameType , GameName , ConnCount , ROW_NUMBER() OVER(PARTITION BY GameType ORDER BY GameType, ConnCount DESC) RowNum FROM #temp ) T --옛날 홈페이지에 있는거 편집하려니까 졸라 빡시다... |
|