gg
当前位置: 首页 >> MSSqlServer >> Content

求给个sql解决方法,

相关文章

表结构就是这样:periodid 指的是年月期间,SFQYBJ 指的是对应当月是否全月病假,DCNO就是工号
periodid SFQYBJ DCNO
13101.00000118
13090.00000118
13081.00000118
13070.00000118
13060.00000118
13050.00000118
13040.00000118
13030.00000118
13020.00000118
13010.00000118

想要返回结果月数,要的是periodid年月期间连续的才能把SFQYBJ全月病假相加,
例如,也就是先看1310的对应SFQYBJ是1,再继续看1309是0,因为两个不连续所以
      返回0,以此往下,这张表最后返回的全月病假月数是0,因为它们的月数期间
      对应的SFQYBJ 都不连续,而如果把1309对应的SFQYBJ 换成1.00的话最后
      这张表最终返回的结果就是3+0=3

求方便的解决方法 求给个sql解决方法
------Solutions------


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

------Solutions------
引用 1 楼 ap0405140 的回复:

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


------Solutions------
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

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

------Solutions------
如果periodid是字符串的话 自己去修改一下关系。CAST periodid AS INT
------Solutions------
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,1.00,'000118' union all
select 1306,0.00,'000118' union all
select 1305,1.00,'000118' union all
select 1304,1.00,'000118' union all
select 1303,0.00,'000118' union all
select 1302,0.00,'000118' union all
select 1301,0.00,'000118'

这样的结果是不是 6?
------Solutions------
非常感谢大家的帮助

热门搜索 - TryCatch Copyright © 2012-2013 tc5u.com 版权所有