需要知道 Between 和 like 是否可以一起使用或者在 sql 中更好

发布于 2024-10-03 17:02:43 字数 983 浏览 0 评论 0原文

这是我的查询：

SELECT count(*) 
  FROM table_testcase_execution 
 WHERE campaign_session_id = any(SELECT campaign_session_id 
                                   FROM table_campaign_session 
                                  WHERE campaign_session_name = 'sitename') 
   AND timestamp BETWEEN "1288929643485" AND "1289010305536"

这工作得很好，问题是我必须对这些使用 LIKE 因为它们包含 3 个额外的数字（所以这是一个时间戳加 3 个数字）。

因此，我在 php 中使用 strtotime 并尝试在数据库中匹配这些，但它们包含额外的 3 位数字。有没有办法可以重做此 sql 或为每个时间戳添加 LIKE 子句？

      "1288929643485" AND "1289010305536"

像这样的东西

 SELECT count(*) 
   FROM table_testcase_execution 
  WHERE campaign_session_id = any(SELECT campaign_session_id 
                                    FROM table_campaign_session 
                                   WHERE campaign_session_name = 'sitename') 
    AND timestamp between LIKE "1288929643%" AND "1289010305%"

原文

Here is my query:

SELECT count(*) 
  FROM table_testcase_execution 
 WHERE campaign_session_id = any(SELECT campaign_session_id 
                                   FROM table_campaign_session 
                                  WHERE campaign_session_name = 'sitename') 
   AND timestamp BETWEEN "1288929643485" AND "1289010305536"

This works just fine, the problem is that I have to use a LIKE on these because they contain 3 extra digits (so this is a timestamp plus 3 digits).

Therefore I am using a strtotime in php and trying to match these in the database, but they contain the extra 3 digits. Is there a way I can redo this sql or add a LIKE clause for each of these timestamps?

      "1288929643485" AND "1289010305536"

Something like

 SELECT count(*) 
   FROM table_testcase_execution 
  WHERE campaign_session_id = any(SELECT campaign_session_id 
                                    FROM table_campaign_session 
                                   WHERE campaign_session_name = 'sitename') 
    AND timestamp between LIKE "1288929643%" AND "1289010305%"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぃ弥猫深巷。 2024-10-10 17:02:43

假设 3 个额外数字的范围是从 000 到 999，那么您可以执行以下操作：

select count(*) from table_testcase_execution
where campaign_session_id = any(
    SELECT campaign_session_id
    FROM table_campaign_session
    WHERE campaign_session_name = 'sitename')
AND timestamp >= "1288929643000" AND timestamp <= "1289010305999"

这与 timestamp 列中的索引一起应该会给您带来良好的性能。

Assuming that the 3 extra digits can range from 000 to 999 then you could do something like this:

select count(*) from table_testcase_execution
where campaign_session_id = any(
    SELECT campaign_session_id
    FROM table_campaign_session
    WHERE campaign_session_name = 'sitename')
AND timestamp >= "1288929643000" AND timestamp <= "1289010305999"

This along with an index in timestamp column should give you good performance.

回复收藏 0 原文

瞳孔里扚悲伤 2024-10-10 17:02:43

由于查询中的时间戳是字符串，因此您不能仅使用 SUBSTRING()？

select count(*) 
from table_testcase_execution 
where campaign_session_id = any(SELECT campaign_session_id FROM table_campaign_session WHERE campaign_session_name = 'sitename') 
AND timestamp between SUBSTRING(@timestamp1, -3) AND SUBSTRING(@timestamp2, -3)

（假设@timestamp1和@timestamp2是提供给查询的各自时间戳值...）

Since the timestamps in your query are strings, can you not just truncate the unwanted three digits with SUBSTRING()?

select count(*) 
from table_testcase_execution 
where campaign_session_id = any(SELECT campaign_session_id FROM table_campaign_session WHERE campaign_session_name = 'sitename') 
AND timestamp between SUBSTRING(@timestamp1, -3) AND SUBSTRING(@timestamp2, -3)

(assuming @timestamp1 and @timestamp2 are the respective timestamp values supplied to the query...)

回复收藏 0 原文

~没有更多了~