34,838
社区成员




----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-27 11:10:15
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([periodid] int,[SFQYBJ] numeric(3,2),[DCNO] varchar(6))
insert [tb]
select 1310,1.00,'000118' union all
select 1309,1.00,'000118' union all
select 1308,1.00,'000118' union all
select 1307,0.00,'000118' union all
select 1306,0.00,'000118' union all
select 1305,0.00,'000118' union all
select 1304,0.00,'000118' union all
select 1303,0.00,'000118' union all
select 1302,0.00,'000118' union all
select 1301,0.00,'000118'
--------------开始查询--------------------------
SELECT
ISNULL(SUM(SFQYBJ),0)
FROM
TB t
WHERE
SFQYBJ=1
AND
(EXISTS(SELECT 1 FROM TB WHERE DCNO=t.DCNO AND periodid=t.periodid-1 AND SFQYBJ=1)
OR
EXISTS(SELECT 1 FROM TB WHERE DCNO=t.DCNO AND periodid=t.periodid+1 AND SFQYBJ=1))
----------------结果----------------------------
/* 3.00
*/
SELECT SUM(CONVERT(INT, A.SFQYBJ) & CONVERT(INT, ISNULL(B.SFQYBJ, A.SFQYBJ)) --0,1的话&一下,呵呵
& CONVERT(INT, ISNULL(B.SFQYBJ, C.SFQYBJ)))
FROM dbo.TB A
LEFT JOIN TB B ON LEFT(CONVERT(VARCHAR, A.periodid), 2) = LEFT(CONVERT(VARCHAR, B.periodid),
2)
AND A.periodid + 1 = B.periodid
LEFT JOIN TB C ON LEFT(CONVERT(VARCHAR, A.periodid), 2) = LEFT(CONVERT(VARCHAR, C.periodid),
2)
AND A.periodid - 1 = C.periodid
create table u01
(periodid varchar(10),SFQYBJ decimal(5,2),DCNO varchar(10))
-- 测试1
truncate table u01
insert into u01
select '1310',1.00,'000118' union all
select '1309',0.00,'000118' union all
select '1308',1.00,'000118' union all
select '1307',0.00,'000118' union all
select '1306',0.00,'000118' union all
select '1305',0.00,'000118' union all
select '1304',0.00,'000118' union all
select '1303',0.00,'000118' union all
select '1302',0.00,'000118' union all
select '1301',0.00,'000118'
with t as
(select row_number() over(partition by DCNO order by periodid) 'rn',
SFQYBJ
from u01)
select sum(case when a.SFQYBJ=1 and (b.SFQYBJ=1 or c.SFQYBJ=1) then 1
else 0 end) '全月病假月数'
from t a
left join t b on a.rn=b.rn+1
left join t c on a.rn=c.rn-1
/*
全月病假月数
-----------
0
(1 row(s) affected)
*/
-- 测试2
truncate table u01
insert into u01
select '1310',1.00,'000118' union all
select '1309',1.00,'000118' union all --> 1309对应的SFQYBJ换成1.00
select '1308',1.00,'000118' union all
select '1307',0.00,'000118' union all
select '1306',0.00,'000118' union all
select '1305',0.00,'000118' union all
select '1304',0.00,'000118' union all
select '1303',0.00,'000118' union all
select '1302',0.00,'000118' union all
select '1301',0.00,'000118'
with t as
(select row_number() over(partition by DCNO order by periodid) 'rn',
SFQYBJ
from u01)
select sum(case when a.SFQYBJ=1 and (b.SFQYBJ=1 or c.SFQYBJ=1) then 1
else 0 end) '全月病假月数'
from t a
left join t b on a.rn=b.rn+1
left join t c on a.rn=c.rn-1
/*
全月病假月数
-----------
3
(1 row(s) affected)
*/