Contents

1
2 ろ 一危
3 2000 覯 襭
4 2005 覯 襭


1 #

れ 企 蟆郁骸 螳 蠏碁9 譴 豌 覯讌 螳襷 願 伎企. 朱朱 覲願 煙 一碁.
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

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)

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
*/

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
-- 伎 蟇 ク讌る蟾 譟碁 觜′...