更新！我想在 table_20220121 中找到兩個變數與 table_20220121_b 匹配的行。此外，table_20220121 中可能有多個匹配項，但我只想要 [Time since 1970] 值最大的那個，也就是說，我希望輸出是 table_20220121 中編號為 88 和 1391 的行。謝謝！
drop table table_20220121
drop table table_20220121_b
drop table table_output
CREATE TABLE table_20220121
(
number float,
IP nvarchar(100),
[Time since 1970] float,
[Timestamp] nvarchar(100),
[Scroll Depth Threshold] float
);
INSERT INTO table_20220121
VALUES
(84,'999.999.99.111',1635332371761,'2021-10-27T12:59:31.761 02:00',0.7),
(85,'999.999.99.111',1635332377747,'2021-10-27T12:59:37.747 02:00',0.7),
(86,'999.999.99.111',1635332387744,'2021-10-27T12:59:47.744 02:00',0.7),
(87,'999.999.99.111',1635332388600,'2021-10-27T12:59:48.600 02:00',0.8),
(88,'999.999.99.111',1635332397761,'2021-10-27T12:59:57.761 02:00',0.8),
(1387,'999.999.99.999',1640983016936,'2021-12-31T21:36:56.936 01:00',0.4),
(1388,'999.999.99.999',1640983030370,'2021-12-31T21:37:10.370 01:00',0.5),
(1389,'999.999.99.999',1640983055463,'2021-12-31T21:37:35.463 01:00',0.5),
(1390,'999.999.99.999',1640983100401,'2021-12-31T21:38:20.401 01:00',0.6),
(1391,'999.999.99.999',1640983115461,'2021-12-31T21:38:35.460 01:00',0.6)
;
CREATE TABLE table_20220121_b
(
IP nvarchar(100),
[Scroll Depth Threshold] float
);
INSERT INTO table_20220121_b
VALUES
('999.999.99.999',0.6),
('999.999.99.111',0.8)
;
select * from table_20220121;
select * from table_20220121_b;
go
實際上，以下是我的問題的答案。為了清楚起見，我在問題部分中提供了它：
with matches as (
select *, Row_Number() over(partition by ip order by [Time since 1970] desc) rn
from table_20220121 ta
where exists (
select * from table_20220121_b tb
where ta.ip=tb.ip
and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
)
)
select number, ip, [Time since 1970], Timestamp, [Scroll Depth Threshold]
/* into table_output */
from matches
where rn = 1;
uj5u.com熱心網友回復：
你試圖使用的語法不是SQL Server支持（和你使用“字串文字”因此，如果語法被支持，它總是回傳true）; 但是，您可以使用多個條件加入表格并采用top n
select top(1) ta.*
/* into table_output */
from table_20220121 ta
join table_20220121_b tb on ta.ip = tb.ip
and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
order by [Time since 1970] desc;
或者，您可以使用row_number連接和過濾多行以過濾每個組的最大值：
with matches as (
select *, Row_Number() over(partition by ip order by [Time since 1970] desc) rn
from table_20220121 ta
where exists (
select * from table_20220121_b tb
where ta.ip=tb.ip
and ta.[Scroll Depth Threshold] = tb.[Scroll Depth Threshold]
)
)
select number, ip, [Time since 1970], Timestamp, [Scroll Depth Threshold]
/* into table_output */
from matches
where rn = 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/420174.html
標籤：
上一篇：將我所有物件的字串轉換為陣列
下一篇：查詢中的緯度/經度轉換性能緩慢