Contents

1
2 ろ 一危 襷り鍵
3 SQL1: self join 伎 覦覯
4 SQL2: cross join螻 group by襯 伎 覦覯
5 企 觜訣 蟆..
6 蟆磯


1 #

SQL 燕 覲企 伎 Row 覦襦 れ Row襯 蠏 螳 . 襯朱れ, 蠍郁 覓企ゼ 危襴貅伎 襦蠏語, 襦蠏語 蠍磯 蠍企り 螳 覲伎. 企 れ螻 螳 蟲譟一 蟆企.

empid      log_dt                  log_type
---------- ----------------------- --------
yasicom    2007-05-30 22:11:33.570 1
mkex       2007-05-30 22:11:34.560 1
yasicom    2007-05-30 22:11:37.577 0
endless    2007-05-30 22:11:41.570 1
soma       2007-05-30 22:11:43.563 1
mkex       2007-05-30 22:11:48.560 0
yasicom    2007-05-30 22:11:59.557 1
soma       2007-05-30 22:12:03.563 0
endless    2007-05-30 22:12:04.553 0
endless    2007-05-30 22:23:17.363 1

襷 empid覲 ろ 螳 螻 矩る 企至 SQL 蟆瑚? 企 蟆曙 螳 覈 螳讌 襭 螳襦 蟆. (谿瑚: log_type = 1 企 襦蠏語, 0 企 襦蠏語)

2 ろ 一危 襷り鍵 #

if object_id('tempdb..#temp') is not null
        drop table #temp
 
create table #temp(
        empid varchar(10)
,       log_dt datetime
,       log_type bit
)

insert #temp values('yasicom', getdate(), 1)
waitfor delay '00:00:01'

insert #temp values('mkex', getdate(), 1)
waitfor delay '00:00:03'

insert #temp values('yasicom', getdate(), 0)
waitfor delay '00:00:04'

insert #temp values('endless', getdate(), 1)
waitfor delay '00:00:02'

insert #temp values('soma', getdate(), 1)
waitfor delay '00:00:05'

insert #temp values('mkex', getdate(), 0)
waitfor delay '00:00:11'

insert #temp values('yasicom', getdate(), 1)
waitfor delay '00:00:04'

insert #temp values('soma', getdate(), 0)
waitfor delay '00:00:01'

insert #temp values('endless', getdate(), 0)
waitfor delay '00:00:05'

insert #temp values('endless', getdate(), 1)
go

3 SQL1: self join 伎 覦覯 #

--1. 襾殊  企 l伎 朱覯碁ゼ 燕.  螳 襷 . 
--   2005 覯 蟆曙磯 row_number()襯 伎. 企 j鍵 蠏谿朱襦
--   row_number()襯殊伎蟆. 

set statistics io on
with tmp(rownum, empid, log_dt, log_type)
as
(
	select
		row_number() over(order by empid, log_dt, log_type desc) rownum
	,	empid
	,	log_dt
	,	log_type
	from #temp
)
select 
	a.empid
,	sum(datediff(ss, a.log_dt, b.log_dt)) usedtime
from tmp a inner join tmp b
on a.rownum = b.rownum - 1 -- 蠍郁 旧企.
and a.empid = b.empid
where cast(a.log_type as varchar) + cast(b.log_type as varchar) = '10'
group by a.empid
go

4 SQL2: cross join螻 group by襯 伎 覦覯 #

with tmp(rownum, empid, log_dt, log_type)
as
(
	select
		row_number() over(order by empid, log_dt, log_type desc) rownum
	,	empid
	,	log_dt
	,	log_type
	from #temp
)
select
	empid
,	sum(datediff(ss, login_dt, logout_dt)) usedtime
from (
	select 
		a.empid
	,	min(case when b.id = 1 and log_type = 1 then log_dt end) login_dt
	,	min(case when b.id = 2 and log_type = 0 then log_dt end) logout_dt
	from tmp a cross join (select 1 id union all select 2) b
	group by 
		a.empid
	,	case when b.id = 1 then rownum else rownum - 1 end --蠍郁 旧企.
) t
group by empid
go

5 企 觜訣 蟆.. #

--WITH Game
--AS
--(
--SELECT 'A' CharName, 1 No1, 1 Val01, 0 Val02, 1 Val03 UNION ALL
--SELECT 'B' CharName, 2 No1, 0 Val01, 0 Val02, 0 Val03 UNION ALL
--SELECT 'C' CharName, 3 No1, 0 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'D' CharName, 4 No1, 1 Val01, 1 Val02, 0 Val03 UNION ALL
--SELECT 'E' CharName, 5 No1, 2 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'F' CharName, 6 No1, 1 Val01, 0 Val02, 2 Val03 UNION ALL
--SELECT 'G' CharName, 7 No1, 0 Val01, 2 Val02, 0 Val03 UNION ALL
--SELECT 'H' CharName, 8 No1, 1 Val01, 0 Val02, 2 Val03 UNION ALL
--SELECT 'I' CharName, 9 No1, 0 Val01, 2 Val02, 2 Val03 UNION ALL
--SELECT 'J' CharName, 10 No1, 1 Val01, 0 Val02, 1 Val03 UNION ALL
--SELECT 'K' CharName, 11 No1, 0 Val01, 2 Val02, 2 Val03 UNION ALL
--SELECT 'L' CharName, 12 No1, 1 Val01, 2 Val02, 0 Val03 UNION ALL
--SELECT 'M' CharName, 13 No1, 0 Val01, 1 Val02, 1 Val03 UNION ALL
--SELECT 'N' CharName, 14 No1, 2 Val01, 0 Val02, 0 Val03 UNION ALL
--SELECT 'O' CharName, 15 No1, 1 Val01, 2 Val02, 1 Val03 UNION ALL
--SELECT 'P' CharName, 16 No1, 2 Val01, 1 Val02, 2 Val03
--)
--SELECT * INTO #Temp 
--FROM Game

SELECT
	GroupNo
,	MIN(CASE WHEN No1%2=1 THEN No1 END) NoA
,	MIN(CASE WHEN No1%2=1 THEN CharName END) A
,	MIN(CASE WHEN No1%2=1 THEN Val01 END) V1
,	MIN(CASE WHEN No1%2=1 THEN Val02 END) V2
,	MIN(CASE WHEN No1%2=1 THEN Val03 END) V3
,	MIN(CASE WHEN No1%2=0 THEN No1 END) NoB
,	MIN(CASE WHEN No1%2=0 THEN CharName END) B
,	MIN(CASE WHEN No1%2=0 THEN Val01 END) V4
,	MIN(CASE WHEN No1%2=0 THEN Val02 END) V5
,	MIN(CASE WHEN No1%2=0 THEN Val03 END) V6
FROM (
	SELECT 
		No1
	,	CharName
	,	Val01
	,	Val02
	,	Val03
	,	(Row_NUmBER() OVER(ORDER BY No1)+1)/2 GroupNo
	FROM #Temp
) T
GROUP BY
	GroupNo


6 蟆磯 #

貎朱Μ 觜 觜訣. 襦 蠏碁 2覯讌 貎朱Μ螳 觜 蟆 . 觜 觜訣螻, 焔レ 觜訣る 企 蟆 郁螳? ろ 襴れ 螳 る 豌 覯讌 貎朱Μ襯 蟠螻 矩. 覃 譬 危危蠍 所 覈蠍 覓語企. 蠏碁覩襦 讌覲伎 豸°伎 覲企 襴. 襷 蠏碁 企 2覯 暑 蟆 蠖蠖る 2覯讌 覦覯 蠍 覦.