当前位置：首页 - 正文

sql server 按照时间段查询的诡异问题

发布网友发布时间：2022-04-08 02:34

共2个回答

懂视网时间：2022-04-08 06:56

统计连续时间段数据

if OBJECT_ID(N'Test',N'U') is not null
	drop table Test
go 

create table Test(
	pscode decimal(15),
	outputcode int,
	monitortime datetime
)

insert into Test
select 4100000406,1,convert(datetime,'2015-04-01 00:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 01:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 02:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 03:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 04:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 05:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 06:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 07:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 08:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 09:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 10:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 11:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 13:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 14:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 15:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 16:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 17:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 18:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 19:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 20:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 22:00') union all
select 4100000406,1,convert(datetime,'2015-04-01 23:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 01:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 02:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 03:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 04:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 05:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 06:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 07:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 08:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 09:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 11:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 12:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 13:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 14:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 15:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 16:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 17:00') union all
select 4100000405,2,convert(datetime,'2015-04-01 18:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 00:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 01:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 02:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 03:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 04:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 05:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 06:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 07:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 08:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 09:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 11:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 12:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 13:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 14:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 15:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 16:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 17:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 18:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 19:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 20:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 22:00') union all
select 4100000402,1,convert(datetime,'2015-04-01 23:00')


select pscode,outputcode,
	 convert(varchar(16),MIN(monitortime),120)+'～'+convert(varchar(16),MAX(monitortime),120) fw,
 COUNT(1) num
 from (select x.pscode,x.outputcode,x.monitortime,dateadd(HOUR,-x.orderby,x.monitortime) diff
		 from (select pscode,outputcode,monitortime,
					 ROW_NUMBER() over(partition by pscode,outputcode order by pscode,outputcode,monitortime) orderby 
				 from Test) x)y 
 group by y.pscode,y.outputcode,y.diff

SQLServer时间分段查询

标签：

热心网友时间：2022-04-08 04:04

中国*境内的一般sqlserver格式化格式是用120，不用126

select * from tablename where CONVERT(VARCHAR(10), CREATEDATE, 120) <= '2018-01-31'
and CONVERT(VARCHAR(10), CREATEDATE, 120) >= '2018-01-01' ORDER BY CREATEDATE desc;

而且用不着substring，直接在convert后边的varchar(10)定义截取长度就行，还有你之前为什么用11,2018-01-01本来也就只有10位长度。

sql语句中如何一个字段对查询出的多个值添加判断条件

4行变成1行？如果用 sql server 的话，可以用一种很诡异的方法：select distinct ','+ sale_item from sale_report for xml path('')楼主可以测试一下，最后用一个 for xml path('')就好。我上面的 sql 执行结果为：,c,a,b

sqlserver中xp_cmdshell映射网络路径的问题

我的比你的复杂：exec master..xp_cmdshell 'net use j: \\192.168.1.100\c$ "##abc$窗前明月光，我是郭德纲！" /user:192.168.1.120\sqa'exec master..xp_cmdshell 'net use j: /del'--- 命令成功完成。NULL NULL --- 所以呀！你先看看你的用户能否修改密码，把那个checkbox挑了...

python3连接MSSQL数据库中文乱码怎么解决

虽然摆脱了问号和乱码的困扰，但这仍不是我们想要的结果，但这个确实是正确的，因为结果是utf8编码。这个现象确实诡异，请教了许多高手，得知，最好的结果就是逐个字段查询，才能显示中文，整个查询的话，会以utf8的格式显示出来。上述代码中第14行data是整个查询的结果，如果指定某个具体的字段，如print ...

python3.6 连接MSSQL报错

（1）、首先，要保证文件的开头要加上编码设置来说明文件的编码复制代码代码如下:encoding=utf-8 （2）、然后，在连接数据的连接参数里加上字符集说明查询出的结果的编码，这个不加的后果可能是查询出的汉字字符都是问号复制代码代码如下:conn=pymssql.connect(server='.',user='', password='',da...

sql server和mysql sql server的主要功能 sql server有什么用 sqlserver怎么用在sql server sqlserver基本介绍 sql server登录 sqlserver官网 sqlserver数据库

声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。
E-MAIL:11247931@qq.com

焦点

sql server 按照时间段查询的诡异问题

最新推荐

猜你喜欢

热门推荐