两个表，找时间相近记录联合显示

2010-05-28 09:28:17 242 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

我的世界“一发出核心”是运气还是bug？概率与日志联合验证方法在《我的世界》Java版联机服务器中，稀有物品的掉落常被玩家归因于运气或bug。理解随机数生成与战利品表机制是判断异常概率的基础。通过二项分布计算出现概率，结合服务端日志和物品NBT进行工程化排查，可以从运气、方法、bug三个维度拆解“一发出核心”事件。这套通用验证流程适用于任何存档或服务器，涵盖战利品表配置检查、插件冲突分析、最小统计实验设计，帮助玩家和管理员在各自环境下定位真实原因，避免仅凭体感就下结论。真正可靠的分析，依赖概率统计、样本量、日志证据链和可复现实验，而非单纯的“一转定论”。阅读详情

TabA表
TabAID ID T Val
--------------------------------------------------------------------------------
7 1 2010-01-01 07:00:00.000 123
8 1 2010-01-01 08:00:00.000 456
9 1 2010-01-01 09:00:00.000 789
10 2 2010-01-01 10:00:00.000 a
11 2 2010-01-01 11:00:00.000 b

TabB表：
TabAID ID T Val
--------------------------------------------------------------------------------
9 1 2010-01-01 06:59:00.000 1
10 1 2010-01-01 07:01:30.000 2
11 1 2010-01-01 07:52:00.000 3
12 1 2010-01-01 08:10:00.000 4
13 1 2010-01-01 09:00:00.000 5
14 2 2010-01-01 11:00:00.000 x
15 2 2010-01-01 12:10:00.000 y
16 2 2010-01-01 12:00:00.000 z

对于TabA表，当ID=1,T=2010-01-01 07:00:00.000时，需要在B表中找到与此时间最接近的那个时间（2010-01-01 06:59:00.000），找到后连接在一起，对于A表中的每一行都需要这样做，那么最终结果应该是
TabAID ID T Val TabAID ID T Val
--------------------------------------------------------------------------------
7 1 2010-01-01 07:00:00.000 123 9 1 2010-01-01 06:59:00.000 1
8 1 2010-01-01 08:00:00.000 456 11 1 2010-01-01 07:52:00.000 3
9 1 2010-01-01 09:00:00.000 789 13 1 2010-01-01 09:00:00.000 5
10 2 2010-01-01 11:00:00.000 a 14 2 2010-01-01 11:00:00.000 x
11 2 2010-01-01 12:00:00.000 b 16 2 2010-01-01 12:00:00.000 z

...全文

51单片机仿真环境搭建：Keil+Proteus联合调试实战指南在嵌入式系统开发中，仿真技术是验证程序逻辑与硬件设计的关键环节，它通过软件模拟硬件运行环境，使开发者能在无物理设备的情况下进行调试。其核心原理在于利用编译工具链生成可执行文件，并在电路仿真软件中加载运行，从而实现对微控制器行为的可视化观察。这项技术的价值在于显著降低硬件损耗与学习成本，同时提升调试效率，允许进行单步执行、断点设置和信号分析。在单片机教学、产品原型验证以及算法逻辑测试等场景中，仿真环境尤为重要。本文聚焦于51单片机开发，以普中A2开发板为具体实例，详细讲解如何配置Keil编译环境与绘制Prot 阅读详情

wooden954

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

wooden954 2010-05-28

打赏
举报

[Quote=引用 8 楼 xys_777 的回复:]
换用秒

SQL code

select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and abs(datediff(ss,m.T,a.T))<abs(datediff(ss,b.T,a.T)) )

/*
TabAID ……
[/Quote]

正确！结帖！给分！
请xys_777 到
http://topic.csdn.net/u/20100528/09/b9537a4b-9fbc-46f5-a82a-a532c238a8d4.html
作个标记
谢谢！

wooden954 2010-05-28

打赏
举报

[Quote=引用 5 楼 wujinyuan 的回复:]
select * from taba a inner join tabb b on b.t=(select max(t.t) from tabb t where t.T<a.T)
[/Quote]
结果中
3 1 2010-01-01 09:00:00.000 789 4 1 2010-01-01 08:10:00.000 4
这个记录不正确

zhangsong841 2010-05-28

打赏
举报

[Quote=引用 13 楼 zhangsong841 的回复:]
引用 8 楼 xys_777 的回复:
换用秒

SQL code

select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and abs(datediff(ss,m.T,a.T))<abs(datediff(ss,b.T,a.T)) )
……
[/Quote]
向永生哥赔不是了，
小弟自己理解错了，
真不好意思

wooden954 2010-05-28

打赏
举报

[Quote=引用 3 楼 zhangsong841 的回复:]
select * from taba a,tabb
where a.id=tabb.id
and tabb.t=(select max(t) from tabb b where b.id=a.id and b.t<=a.t)
[/Quote]
这个结果明显记录条数不对，应该为5条记录

wooden954 2010-05-28

打赏
举报

声明：我所说的时间最近是无论早晚都算，比如7:00这个时刻，在6:59:00和7:00:01中，显然是7:00:01最近，而在6:59:59和7:01:00中，则是6:59:59最近

zhangsong841 2010-05-28

打赏
举报

[Quote=引用 8 楼 xys_777 的回复:]
换用秒

SQL code

select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and abs(datediff(ss,m.T,a.T))<abs(datediff(ss,b.T,a.T)) )

/*
TabAID ……
[/Quote]

没记错的话，abs是取绝对值，
永生哥取的是tabb离那个时间点最近的，不管是在时间上与tabA的早或晚，
与楼主的提的问题理解上有偏差？
永生哥看看，咱们一起讨论下

黄_瓜 2010-05-28

打赏
举报

--2000

select * from ta a

left join tb b

on a.id=b.id

and not exists(

    select 1 from tb where id=a.id and abs(datediff(ss,t,a.t))<abs(datediff(ss,b.t,a.t))

    )

黄_瓜 2010-05-28

打赏
举报

--2000

select * from ta a

left join tb b

on a.id=b.id

and not exists(

	select 1 from tb where id=a.id a

		nd abs(datediff(ss,t,a.t))<abs(datediff(ss,b.t,a.t))

	)

Mr_Nice 2010-05-28

打赏
举报

[Quote=引用 6 楼 beirut 的回复:]

SQL code
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(3))
insert [ta]
select 7,1,'2010-01-01 07:00:00.000','12……
[/Quote]

好思路，学习...

黄_瓜 2010-05-28

打赏
举报

--> 测试数据：[ta]

if object_id('[ta]') is not null drop table [ta]

go

create table [ta]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(3))

insert [ta]

select 7,1,'2010-01-01 07:00:00.000','123' union all

select 8,1,'2010-01-01 08:00:00.000','456' union all

select 9,1,'2010-01-01 09:00:00.000','789' union all

select 10,2,'2010-01-01 10:00:00.000','a' union all

select 11,2,'2010-01-01 11:00:00.000','b'



--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(1))

insert [tb]

select 9,1,'2010-01-01 06:59:00.000','1' union all

select 10,1,'2010-01-01 07:01:30.000','2' union all

select 11,1,'2010-01-01 07:52:00.000','3' union all

select 12,1,'2010-01-01 08:10:00.000','4' union all

select 13,1,'2010-01-01 09:00:00.000','5' union all

select 14,2,'2010-01-01 11:00:00.000','x' union all

select 15,2,'2010-01-01 12:10:00.000','y' union all

select 16,2,'2010-01-01 12:00:00.000','z'



--------------------------------查询开始------------------------------

;with t as

(

select a.[TabAID] as aTabAID ,a.[ID] as aid ,a.[T] as at ,a.[Val] as aVal,

b.[TabAID] as bTabAID ,b.[ID] as bid ,b.[T] as bt ,b.[Val] as bVal,

row=row_number() over(partition by a.t order by abs(datediff(mi,a.t,b.t)))

from ta a left join tb b 

on 

a.id=b.id 

)

select * from t where row=1

/*

aTabAID     aid         at                      aVal bTabAID     bid         bt                      bVal row

----------- ----------- ----------------------- ---- ----------- ----------- ----------------------- ---- --------------------

7           1           2010-01-01 07:00:00.000 123  9           1           2010-01-01 06:59:00.000 1    1

8           1           2010-01-01 08:00:00.000 456  11          1           2010-01-01 07:52:00.000 3    1

9           1           2010-01-01 09:00:00.000 789  13          1           2010-01-01 09:00:00.000 5    1

10          2           2010-01-01 10:00:00.000 a    14          2           2010-01-01 11:00:00.000 x    1

11          2           2010-01-01 11:00:00.000 b    14          2           2010-01-01 11:00:00.000 x    1



(5 行受影响)



*/

永生天地 2010-05-28

打赏
举报

换用秒



select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and  abs(datediff(ss,m.T,a.T))<abs(datediff(ss,b.T,a.T)) )



/*

TabAID               ID                   T                                                      Val                  TabBID               ID                   T                                                      Val                  

-------------------- -------------------- ------------------------------------------------------ -------------------- -------------------- -------------------- ------------------------------------------------------ -------------------- 

1                    1                    2010-01-01 07:00:00.000                                123                  1                    1                    2010-01-01 06:59:00.000                                1

2                    1                    2010-01-01 08:00:00.000                                456                  3                    1                    2010-01-01 07:52:00.000                                3

3                    1                    2010-01-01 09:00:00.000                                789                  5                    1                    2010-01-01 09:00:00.000                                5

4                    2                    2010-01-01 10:00:00.000                                a                    6                    2                    2010-01-01 11:00:00.000                                x

5                    2                    2010-01-01 11:00:00.000                                b                    6                    2                    2010-01-01 11:00:00.000                                x



（所影响的行数为 5 行）





*/

永生天地 2010-05-28

打赏
举报

select a.*,b.* from tabA a,tabB b where a.id=b.id and not exists(select 1 from tabB m where m.id=a.id and  abs(datediff(mi,m.T,a.T))<abs(datediff(mi,b.T,a.T)) )



/*TabAID               ID                   T                                                      Val                  TabBID               ID                   T                                                      Val                  

-------------------- -------------------- ------------------------------------------------------ -------------------- -------------------- -------------------- ------------------------------------------------------ -------------------- 

1                    1                    2010-01-01 07:00:00.000                                123                  1                    1                    2010-01-01 06:59:00.000                                1

1                    1                    2010-01-01 07:00:00.000                                123                  2                    1                    2010-01-01 07:01:30.000                                2

2                    1                    2010-01-01 08:00:00.000                                456                  3                    1                    2010-01-01 07:52:00.000                                3

3                    1                    2010-01-01 09:00:00.000                                789                  5                    1                    2010-01-01 09:00:00.000                                5

4                    2                    2010-01-01 10:00:00.000                                a                    6                    2                    2010-01-01 11:00:00.000                                x

5                    2                    2010-01-01 11:00:00.000                                b                    6                    2                    2010-01-01 11:00:00.000                                x



（所影响的行数为 6 行）



*/

黄_瓜 2010-05-28

打赏
举报

--> 测试数据：[ta]

if object_id('[ta]') is not null drop table [ta]

go

create table [ta]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(3))

insert [ta]

select 7,1,'2010-01-01 07:00:00.000','123' union all

select 8,1,'2010-01-01 08:00:00.000','456' union all

select 9,1,'2010-01-01 09:00:00.000','789' union all

select 10,2,'2010-01-01 10:00:00.000','a' union all

select 11,2,'2010-01-01 11:00:00.000','b'



--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([TabAID] int,[ID] int,[T] datetime,[Val] varchar(1))

insert [tb]

select 9,1,'2010-01-01 06:59:00.000','1' union all

select 10,1,'2010-01-01 07:01:30.000','2' union all

select 11,1,'2010-01-01 07:52:00.000','3' union all

select 12,1,'2010-01-01 08:10:00.000','4' union all

select 13,1,'2010-01-01 09:00:00.000','5' union all

select 14,2,'2010-01-01 11:00:00.000','x' union all

select 15,2,'2010-01-01 12:10:00.000','y' union all

select 16,2,'2010-01-01 12:00:00.000','z'



--------------------------------查询开始------------------------------



select * from ta a left join tb b 

on 

a.id=b.id 

where

b.t=(select max(t) from tb where id=a.id and t<=a.t)

/*

TabAID      ID          T                       Val  TabAID      ID          T                       Val

----------- ----------- ----------------------- ---- ----------- ----------- ----------------------- ----

7           1           2010-01-01 07:00:00.000 123  9           1           2010-01-01 06:59:00.000 1

8           1           2010-01-01 08:00:00.000 456  11          1           2010-01-01 07:52:00.000 3

9           1           2010-01-01 09:00:00.000 789  13          1           2010-01-01 09:00:00.000 5

11          2           2010-01-01 11:00:00.000 b    14          2           2010-01-01 11:00:00.000 x



(4 行受影响)





*/

wujinyuan 2010-05-28

打赏
举报

select * from taba a inner join tabb b on b.t=(select max(t.t) from tabb t where t.T<a.T)

zhangsong841 2010-05-28

打赏
举报

另外对于这两张表，并不需要自动增长的tabid

zhangsong841 2010-05-28

打赏
举报

select * from taba a,tabb
where a.id=tabb.id
and tabb.t=(select max(t) from tabb b where b.id=a.id and b.t<=a.t)

wooden954 2010-05-28

打赏
举报

忘记加主键关键字了，实际上列TabAID和TabBID为自动增长的主键

wooden954 2010-05-28

打赏
举报

创建表以及插入测试数据的语句为



Create Table TabA(

  TabAID bigint identity(1,1),

  ID bigint,

  T  DateTime,

  Val varchar(20)

)

Create Table TabB(

  TabBID bigint identity(1,1),

  ID bigint,

  T  DateTime,

  Val varchar(20)

)

Delete from TabA



Insert into TabA(ID,T,Val) Values (1,'2010-01-01 07:00:00','123')

Insert into TabA(ID,T,Val) Values (1,'2010-01-01 08:00:00','456')

Insert into TabA(ID,T,Val) Values (1,'2010-01-01 09:00:00','789')

Insert into TabA(ID,T,Val) Values (2,'2010-01-01 10:00:00','a')

Insert into TabA(ID,T,Val) Values (2,'2010-01-01 11:00:00','b')



Delete From TabB

Insert into TabB(ID,T,Val) Values (1,'2010-01-01 06:59:00','1')

Insert into TabB(ID,T,Val) Values (1,'2010-01-01 07:01:30','2')

Insert into TabB(ID,T,Val) Values (1,'2010-01-01 07:52:00','3')

Insert into TabB(ID,T,Val) Values (1,'2010-01-01 08:10:00','4')

Insert into TabB(ID,T,Val) Values (1,'2010-01-01 09:00:00','5')

Insert into TabB(ID,T,Val) Values (2,'2010-01-01 11:00:00','x')

Insert into TabB(ID,T,Val) Values (2,'2010-01-01 12:10:00','y')

Insert into TabB(ID,T,Val) Values (2,'2010-01-01 12:00:00','z')

两个表，找时间相近记录联合显示

相关推荐