我正在尝试根据一些基本的 where 子句从表中获取行,现在我想在末尾包含一个"AND EXISTS"子句。我的代码如下:
$stmt = $mysqli->prepare("SELECT object_id FROM ".
$db_table_prefix."user_events
WHERE LPAD(start_timestamp,15,'0') < LPAD(?,15,'0')
AND event_id = ?
AND EXISTS (
SELECT * FROM ".$db_table_prefix."user_events
WHERE LPAD(start_timestamp,15,'0') > LPAD(?,15,'0')
AND event_id = ? )
");
我遇到的问题是我不知道如何从 AND EXISTS 子查询中的主查询中指定列值。
我正在寻找一种方法将这一点附加到子查询中:
AND object_id = **object_id from main query**
任何帮助表示赞赏
此外,在子查询的表中添加了别名以避免混淆
$stmt = $mysqli->prepare("SELECT object_id FROM ".
$db_table_prefix."user_events
WHERE LPAD(start_timestamp,15,'0') < LPAD(?,15,'0')
AND event_id = ?
AND EXISTS (
SELECT * FROM ".$db_table_prefix."user_events AS u
WHERE LPAD(u.start_timestamp,15,'0') > LPAD(?,15,'0')
AND u.object_id = " .$db_table_prefix.".object_id
AND u.event_id = ? )
");
与其
EXISTS
,你可能会发现这里的自连接语法更清晰,我无法从代码中准确推断出您想要的内容,但要为在特定时间之前开始的事件获取object_id
,并且稍后也会再次启动:
SELECT ue1.object_id
FROM user_events ue1 join user_events ue2
WHERE ue1.event_id = ue2.event_id AND
ue1.object_id = ue2.object_id AND
ue1.event_id = ? AND
LPAD(ue1.start_timestamp, 15, '0') < LPAD(ue2.start_timestamp, 15, '0') AND
LPAD(ue1.start_timestamp, 15, '0') < LPAD(?, 15, '0')
并object_id (object_id从 ...