请大家帮忙，高分求助一个复杂的SQL统计语句怎么写呢？

2007-07-27 10:29:36 427 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

树莓派CM4 PCIe扩展方案与ASM1184e芯片应用 PCIe（Peripheral Component Interconnect Express）是计算机硬件中重要的高速串行总线标准，广泛应用于存储、网络等设备连接。其核心原理是通过差分信号实现高带宽传输，PCIe Gen2 x1的理论带宽可达5Gbps。在嵌入式开发中，PCIe扩展技术能有效解决单板计算机接口不足的问题，特别适合树莓派等微型平台。ASMedia ASM1184e作为PCIe交换芯片，可将单个接口扩展为多个，支持热插拔且免驱动，极大提升了工程灵活性。通过PCIe交换机方案，开发者能在树莓派CM 阅读详情

是关于刷卡，根据刷卡纪录求工时的统计语句：
表格如下：
sitename dept ssno r_date in_time out_time
All 85 00016 2006-06-27 2006-6-27 8:00
All 85 00016 2006-06-27 2006-6-27 8:01
All 85 00016 2006-06-27 2006-6-27 8:18
All 85 00016 2006-06-27 2006-6-27 8:19
All 85 00016 2006-06-27 2006-6-27 10:23
All 85 00016 2006-06-27 2006-6-27 10:31
All 85 00016 2006-06-27 2006-6-27 12:03
All 85 00016 2006-06-27 2006-6-27 12:06
All 85 00016 2006-06-27 2006-6-27 12:44
All 85 00016 2006-06-27 2006-6-27 12:54
All 85 00016 2006-06-27 2006-6-27 12:55
All 85 00016 2006-06-27 2006-6-27 13:44
All 85 00016 2006-06-27 2006-6-27 17:31
All 85 00016 2006-06-27 2006-6-27 17:34
All 85 00016 2006-06-30 2006-6-30 8:33
All 85 00016 2006-06-30 2006-6-30 9:33
All 85 00016 2006-06-30 2006-6-30 10:43
All 85 00016 2006-06-30 2006-6-30 11:54
All 85 00016 2006-06-30 2006-6-30 12:53
All 85 00016 2006-06-30 2006-6-30 15:03
All 85 00016 2006-06-30 2006-6-30 16:58
All 85 00016 2006-06-30 2006-6-30 17:25
All 85 00016 2006-07-07 2006-7-7 8:13
All 85 00016 2006-07-07 2006-7-7 10:33
All 85 00016 2006-07-07 2006-7-7 11:51
All 85 00016 2006-07-07 2006-7-7 13:27
All 85 00016 2006-07-07 2006-7-7 13:27
All 85 00016 2006-07-07 2006-7-7 13:28
All 85 00016 2006-07-07 2006-7-7 16:31
All 85 00016 2006-07-07 2006-7-7 16:59
All 85 00016 2006-07-07 2006-7-7 17:17

现在想统计对于同一天的，同一场区内的员工的刷卡纪录，
首先去除，只有刷进入和只有刷出去的纪录。
对于有连续时间刷进入的纪录，取最小的进入时间，
对于有连续时间刷出去的纪录，取最大的出去时间，取出这个样子的时间后，把数据合并到一行里面去
而并不是要取一天之内的最小的进入时间和最大的出去时间
这个要得到这个样子的结果：
sitename dept ssno r_date in_time out_time
All 85 00016 2006-06-27 2006-6-27 8:00 2006-6-27 10:31
All 85 00016 2006-06-27 2006-6-27 12:03 2006-6-27 12:44
All 85 00016 2006-06-27 2006-6-27 12:54 2006-6-27 17:34
All 85 00016 2006-06-30 2006-6-30 8:33 2006-6-30 11:54
All 85 00016 2006-06-30 2006-6-30 12:53 2006-6-30 17:25
All 85 00016 2006-07-07 2006-7-7 8:13 2006-7-7 16:31
All 85 00016 2006-07-07 2006-7-7 16:59 2006-7-7 17:17

语句应该怎么写好呢？
时间很紧急，
请大家帮我想想办法好吗？谢谢

...全文

V93000 SmarTest 8入门：从零搭建你的第一个半导体测试程序半导体测试是芯片从设计走向量产的关键环节，而ATE（自动化测试设备）正是支撑这一环节的核心工具。理解ATE测试程序开发的基本原理，不仅需要掌握测试机台的硬件架构，还要熟悉其软件环境中的管脚映射、电平时序、测试流程等底层逻辑。V93000作为爱德万的主力平台，其SmarTest 8环境通过对象化与流程化设计，将复杂的芯片测试任务拆解为可配置、可复用的模块，极大提升了并行测试效率与调试的便利性。无论是数字逻辑芯片的功能验证，还是模拟混合信号器件的参数测量，掌握SmarTest 8的核心操作都能帮助你快速搭建出可阅读详情

wscft

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

wscft 2007-07-30

打赏
举报

十分感谢楼上提供的算法，速度很快，效率也高谢谢

paoluo 2007-07-28

打赏
举报

--創建測試環境
Declare @T Table (
ssno varchar(10),
r_date datetime,
in_time datetime,
out_time datetime)
--插入數據
Insert @T Select
'00016','2006-06-27','2006-6-27 8:00',NULL
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 8:01'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 8:18'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 8:19'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 10:23'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 10:31'
Union All Select
'00016','2006-06-27','2006-6-27 12:03',NULL
Union All Select
'00016','2006-06-27','2006-6-27 12:06',NULL
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 12:44'
Union All Select
'00016','2006-06-27','2006-6-27 12:54',NULL
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 12:55'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 13:44'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 17:31'
Union All Select
'00016','2006-06-27',NULL,'2006-6-27 17:34'
Union All Select
'00016','2006-06-30','2006-6-30 8:33',NULL
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 9:33'
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 10:43'
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 11:54'
Union All Select
'00016','2006-06-30','2006-6-30 12:53',NULL
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 15:03'
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 16:58'
Union All Select
'00016','2006-06-30',NULL,'2006-6-30 17:25'
--測試
--將表中數據假如自增列導入到臨時表中
Select ID = Identity(Int, 1, 1), * Into #T From @T Order By r_date, IsNull(in_time, out_time)

--刪除重復數據
Delete A From #T A
Where (Exists(Select in_time From #T Where ID = A.ID - 1 And out_time Is Null)
And out_time Is Null)
Or (Exists(Select in_time From #T Where ID = A.ID + 1 And in_time Is Null)
And in_time Is Null)

--將刷卡數據一一對應
Select A.ssno, A.r_date, A.in_time, Min(B.out_time) As out_time
From #T A Inner Join #T B
On A.ID < B.ID
Where A.out_time Is Null
Group By A.ssno, A.r_date, A.in_time
--刪除臨時表
Drop Table #T
GO
--結果
/*
ssno r_date in_time out_time
00016 2006-06-27 00:00:00.000 2006-06-27 08:00:00.000 2006-06-27 10:31:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:03:00.000 2006-06-27 12:44:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:54:00.000 2006-06-27 17:34:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 11:54:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 17:25:00.000
*/

paoluo 2007-07-28

打赏
举报

借助Haiwer(海阔天空)的數據

Select ID = Identity(Int, 1, 1), * Into #T From @T Order By r_date, IsNull(in_time, out_time)

Delete A From #T A
Where (Exists(Select in_time From #T Where ID = A.ID - 1 And out_time Is Null)
And out_time Is Null)
Or (Exists(Select in_time From #T Where ID = A.ID + 1 And in_time Is Null)
And in_time Is Null)

Select A.ssno, A.r_date, A.in_time, Min(B.out_time) As out_time
From #T A Left Join #T B
On A.ID < B.ID
Where A.out_time Is Null
Group By A.ssno, A.r_date, A.in_time

wgqqgw 2007-07-27

打赏
举报

复杂了……
还是尽量少让这种事情发生是上策。

herozwh1979 2007-07-27

打赏
举报

^_^，有人帮我把问题解决了，回答你问题的是高手啊，你的也解决了。

昵称被占用了 2007-07-27

打赏
举报

out有错误，改下

--测试数据
declare @t table (
ssno varchar(10),
r_date datetime,
in_time datetime,
out_time datetime
)
insert @t select
'00016','2006-06-27','2006-6-27 8:00',null
union all select
'00016','2006-06-27',null,'2006-6-27 8:01'
union all select
'00016','2006-06-27',null,'2006-6-27 8:18'
union all select
'00016','2006-06-27',null,'2006-6-27 8:19'
union all select
'00016','2006-06-27',null,'2006-6-27 10:23'
union all select
'00016','2006-06-27',null,'2006-6-27 10:31'
union all select
'00016','2006-06-27','2006-6-27 12:03',null
union all select
'00016','2006-06-27','2006-6-27 12:06',null
union all select
'00016','2006-06-27',null,'2006-6-27 12:44'
union all select
'00016','2006-06-27','2006-6-27 12:54',null
union all select
'00016','2006-06-27',null,'2006-6-27 12:55'
union all select
'00016','2006-06-27',null,'2006-6-27 13:44'
union all select
'00016','2006-06-27',null,'2006-6-27 17:31'
union all select
'00016','2006-06-27',null,'2006-6-27 17:34'
union all select
'00016','2006-06-30','2006-6-30 8:33',null
union all select
'00016','2006-06-30',null,'2006-6-30 9:33'
union all select
'00016','2006-06-30',null,'2006-6-30 10:43'
union all select
'00016','2006-06-30',null,'2006-6-30 11:54'
union all select
'00016','2006-06-30','2006-6-30 12:53',null
union all select
'00016','2006-06-30',null,'2006-6-30 15:03'
union all select
'00016','2006-06-30',null,'2006-6-30 16:58'
union all select
'00016','2006-06-30',null,'2006-6-30 17:25'

--找出符合要求in数据到临时表
select *
into #i
from @t a
where in_time is not null
and out_time is null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is not null
and out_time is null
and in_time<a.in_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is null
and out_time is not null
and out_time<a.in_time
and out_time>b.in_time
)
)

--找出符合要求out数据到临时表
select *
into #o
from @t a
where in_time is null
and out_time is not null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is null
and out_time is not null
and out_time>a.out_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is not null
and out_time is null
and in_time>a.out_time
and in_time<b.out_time
)
)

--查询结果
select i.ssno,i.r_date,i.in_time,o.out_time
from #i i,#o o
where i.ssno=o.ssno
and i.r_date=o.r_date
and i.in_time<o.out_time
and not exists (
select 1 from #i
where ssno=o.ssno
and r_date=o.r_date
and in_time<o.out_time
and in_time>i.in_time
)
and not exists (
select 1 from #o
where ssno=i.ssno
and r_date=i.r_date
and i.in_time<out_time
and out_time<o.out_time
)

--删除临时表
drop table #i,#o

--结果
ssno r_date in_time out_time
---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
00016 2006-06-27 00:00:00.000 2006-06-27 08:00:00.000 2006-06-27 10:31:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:03:00.000 2006-06-27 12:44:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:54:00.000 2006-06-27 17:34:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 11:54:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 17:25:00.000

（所影响的行数为 5 行）

昵称被占用了 2007-07-27

打赏
举报

去掉的字段加回来简单只要把形如
ssno=a.ssno
and
的语句改成
sitename=a.sitename
and dept=a.dept
and ssno=a.ssno
and

把形如
i.ssno=o.ssno
and
的语句改成
i.sitename=o.sitename
and i.dept=o.dept
and i.ssno=o.ssno
and

昵称被占用了 2007-07-27

打赏
举报

由于你的数据sitename dept ssno三字段全部相同,我测试作了简化,只使用了ssno一个字段,而且只取了6-27和6-30数据测试

--测试数据
declare @t table (
ssno varchar(10),
r_date datetime,
in_time datetime,
out_time datetime
)
insert @t select
'00016','2006-06-27','2006-6-27 8:00',null
union all select
'00016','2006-06-27',null,'2006-6-27 8:01'
union all select
'00016','2006-06-27',null,'2006-6-27 8:18'
union all select
'00016','2006-06-27',null,'2006-6-27 8:19'
union all select
'00016','2006-06-27',null,'2006-6-27 10:23'
union all select
'00016','2006-06-27',null,'2006-6-27 10:31'
union all select
'00016','2006-06-27','2006-6-27 12:03',null
union all select
'00016','2006-06-27','2006-6-27 12:06',null
union all select
'00016','2006-06-27',null,'2006-6-27 12:44'
union all select
'00016','2006-06-27','2006-6-27 12:54',null
union all select
'00016','2006-06-27',null,'2006-6-27 12:55'
union all select
'00016','2006-06-27',null,'2006-6-27 13:44'
union all select
'00016','2006-06-27',null,'2006-6-27 17:31'
union all select
'00016','2006-06-27',null,'2006-6-27 17:34'
union all select
'00016','2006-06-30','2006-6-30 8:33',null
union all select
'00016','2006-06-30',null,'2006-6-30 9:33'
union all select
'00016','2006-06-30',null,'2006-6-30 10:43'
union all select
'00016','2006-06-30',null,'2006-6-30 11:54'
union all select
'00016','2006-06-30','2006-6-30 12:53',null
union all select
'00016','2006-06-30',null,'2006-6-30 15:03'
union all select
'00016','2006-06-30',null,'2006-6-30 16:58'
union all select
'00016','2006-06-30',null,'2006-6-30 17:25'

--找出符合要求in数据到临时表
select *
into #i
from @t a
where in_time is not null
and out_time is null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is not null
and out_time is null
and in_time<a.in_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is null
and out_time is not null
and out_time<a.in_time
and out_time>b.in_time
)
)

--找出符合要求out数据到临时表
select *
into #o
from @t a
where in_time is null
and out_time is not null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is null
and out_time is not null
and out_time<a.out_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is not null
and out_time is null
and in_time<a.out_time
and in_time>b.out_time
)
)

--查询结果
select i.ssno,i.r_date,i.in_time,o.out_time
from #i i,#o o
where i.ssno=o.ssno
and i.r_date=o.r_date
and i.in_time<o.out_time
and not exists (
select 1 from #i
where ssno=o.ssno
and r_date=o.r_date
and in_time<o.out_time
and in_time>i.in_time
)
and not exists (
select 1 from #o
where ssno=i.ssno
and r_date=i.r_date
and i.in_time<out_time
and out_time<o.out_time
)

--删除临时表
drop table #i,#o

--结果
ssno r_date in_time out_time
---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
00016 2006-06-27 00:00:00.000 2006-06-27 08:00:00.000 2006-06-27 08:01:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:03:00.000 2006-06-27 12:44:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:54:00.000 2006-06-27 12:55:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 09:33:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 15:03:00.000

（所影响的行数为 5 行）

wscft 2007-07-27

打赏
举报

to:herozwh1979() , 你的是什么问题呢？我也想很高效的查询出数据来啊
但是，象我这个问题，除了用游标一行一行的取出来，比较上下两行的值（这样速度太慢了啊）就没有什么更好的办法了吗？

herozwh1979 2007-07-27

打赏
举报

和我的问题差不多，我提的问题里有相似代码，但是我需要高效啊，，，，，怎么做。。。

昵称被占用了 2007-07-27

打赏
举报

有点太复杂

wscft 2007-07-27

打赏
举报

各位帮帮忙啊？

qqhmitzk 2007-07-27

打赏
举报

我晕,发错拉

qqhmitzk 2007-07-27

打赏
举报

第八问:生成18位的编号　改一下

CREATE VIEW v_GetDate
AS
SELECT dt=replace(left(Convert(varchar,GETDATE(),120),10),'-','')
GO

CREATE FUNCTION create_BH()
RETURNS char(18)
AS
BEGIN
DECLARE @dt CHAR(8)
SELECT @dt=dt FROM v_GetDate
RETURN(SELECT 'scrw'+@dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM SCRW WITH(XLOCK,PAGLOCK)
WHERE BH like 'scrw'+@dt+'%')
END
GO

CREATE TABLE SCRW (
BH char(18) PRIMARY KEY DEFAULT dbo.create_BH(),
li int)
go
INSERT SCRW (li) VALUES(1)
INSERT SCRW (li) VALUES(2)
INSERT SCRW (li) VALUES(3)
go
select * from SCRW
--结果

BH li
------------------ -----------
scrw20070727000001 1
scrw20070727000002 2
scrw20070727000003 3

（所影响的行数为 3 行）

wscft 2007-07-27

打赏
举报

还有没有更高效一点的算法呢，　楼上给出的这种算法，　效率也不是很高的　哦

请大家帮忙， 高分求助一个复杂的SQL统计语句怎么写呢？

相关推荐

请大家帮忙，高分求助一个复杂的SQL统计语句怎么写呢？