双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

求给个sql解决方法，

2013-11-27 10:56:32 210 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Landa_Peter 墨衍会员 · AI 创作全网分发关注 + 加入社区

锐捷交换机堆叠配置举例一、组网需求： 1）四台S2628G进行堆叠； 2）设备在堆叠前先设置好优先级； 3）使用光口堆叠的设备如RG-S2928G-E相关配置二、组网拓扑三、配置要点：普通堆叠设备不需要配置，只要把堆叠模块和线缆连接好即可堆叠模块不支持热插拔，即插在模块连上线缆后不能立即生效，需要重启设备才生效。如果拔掉堆叠插槽后，想再插上模块堆叠，那么必须重启设备才生效设备堆叠需要协商参数，时间会比较久，请耐心等待堆叠时设备建议是... 阅读详情

表结构就是这样：periodid 指的是年月期间，SFQYBJ 指的是对应当月是否全月病假，DCNO就是工号
periodid SFQYBJ DCNO
1310 1.00 000118
1309 0.00 000118
1308 1.00 000118
1307 0.00 000118
1306 0.00 000118
1305 0.00 000118
1304 0.00 000118
1303 0.00 000118
1302 0.00 000118
1301 0.00 000118

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

求方便的解决方法

...全文

Codex辅助小说创作：零成本AI写作指南与实战技巧自然语言处理技术通过深度学习模型理解语义上下文，其核心原理是基于大规模文本训练的语言模型预测能力。在创意写作领域，这项技术的价值在于突破传统写作工具的功能局限，实现从内容生成到风格模仿的智能化辅助。特别是提示词工程的应用，让创作者能够精准控制AI输出的内容和风格。在实际应用场景中，AI写作工具显著降低了小说创作门槛，适用于网络小说、奇幻科幻等多种文学类型。本文以OpenAI Codex为例，详细解析如何利用AI技术完成从故事大纲设计、章节生成到平台发布的全流程创作，其中提示词优化和内容质量把控是提升生成效果阅读详情

Landa_Peter

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

Landa_Peter 2013-11-27

打赏
举报

非常感谢大家的帮助

--小F-- 2013-11-27

打赏
举报

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？

--小F-- 2013-11-27

打赏
举报

如果periodid是字符串的话自己去修改一下关系。CAST periodid AS INT

--小F-- 2013-11-27

打赏
举报

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

Mr_Nice 2013-11-27

打赏
举报

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

开着拖拉机泡妞 2013-11-27

打赏
举报

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

唐诗三百首 2013-11-27

打赏
举报


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

✦双11狂欢大促✦

求给个sql解决方法，

相关推荐

双11狂欢大促