27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-18 21:26:11
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([Inspect_name] varchar(2),[rank] varchar(1),[vdatetime2] datetime,[vtime] int)
insert #TB
select '王','A','2010-1-15 10:15',110 union all
select '王','A','2010-1-15 15:10',30 union all
select '张','A','2010-1-15 10:15',90 union all
select '张','A','2010-1-16 2:15',100 union all
select '刘','A','2010-1-16 3:15',100 union all
select '刘','A','2010-1-16 2:15',40
--------------开始查询--------------------------
SELECT
Inspect_name,
SUM(DISTINCT CASE WHEN rank='A' THEN 1 ELSE 0 END)A,
SUM(DISTINCT CASE WHEN 时段='夜间' THEN 1 ELSE 0 END)AS '夜间'
FROM
(SELECT Inspect_name,
rank ,
CASE WHEN CONVERT(VARCHAR(8),vdatetime2,108)>'05'
THEN CONVERT(VARCHAR(10),vdatetime2,120)
ELSE CONVERT(VARCHAR(10),vdatetime2-1,120) END AS vdatetime2,
vtime,
CASE WHEN vdatetime2
BETWEEN CONVERT(VARCHAR(10),vdatetime2-1,120)+' 23:59:59'
AND CONVERT(VARCHAR(10),vdatetime2,120)+' 05:00:00'
THEN '夜间' ELSE '白天' END AS 时段
from #TB)AS T GROUP BY Inspect_name
HAVING SUM(vtime)>120 OR SUM(CASE WHEN 时段='夜间' THEN vtime ELSE 0 END)>120
----------------结果----------------------------
/*
(所影响的行数为 6 行)
Inspect_name A 夜间
------------ ----------- -----------
刘 1 1
王 1 0
张 1 1
(所影响的行数为 3 行)
*/