本文介绍了在SQL(MySQL)第2部分中基于今天的日期返回
查询结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我发布了另一个问题完美解决了问题,但是现在我需要将我获得的相同代码应用于另一段MySQL代码.
I posted another question which was resolved perfectly however I now need to apply the same code I was given to a different piece of MySQL code.
我拥有的是
SELECT value, COUNT(*) AS 'num'
FROM table_c
WHERE table_c_id IN (9, 17, 25)
GROUP BY value
我现在想做的是仅显示在当前日期输入的结果?
What I would like to do now is only show the results if they have been entered on the current date?
我用于检查当前日期的当前代码段非常有效(日期为unixtime格式)
The current code snippet I have for checking the current date, which works great is (the date is in unixtime format)
( xxxxxxxxxxxxxx and curdate() = date( from_unixtime( b.crm_date_time_column ) ) )
查询的问题是日期列位于完全不同的表table_a中.
The problem I have with query this is the date column is located in a totally different table, table_a.
如何编写MySQL以检查table_a的日期并应用现有的SQL日期?
How do I write the MySQL to check table_a for the date and apply the existing date SQL I have?
这是一个MySQL数据库.
This is a MySQL database.
任何帮助都会感激不尽!这就是我的头了!
Any help will be gratefully received! This is way over my head!
推荐答案
您首先需要使用相关列将
JOIN 另一个表放到第一个表上(我假设 id 与 table_c_id )有关.
You'll want to first JOIN the other table onto the first using related columns (I'm assuming id in the other table is related to table_c_id).
正如我在答案中对您之前的问题所述,您最好对以下问题进行比较空的datetime列,以便查询可保留(即能够利用索引):
And as I had stated in my answer to your previous question, you're better off making the comparison on the bare datetime column so that the query remains sargable(i.e. able to utilize indexes):
SELECT a.value
FROM table_c a
INNER JOIN table_a b ON a.table_c_id = b.id
WHERE a.table_c_id IN (9,17,25) AND
b.crm_date_time_column >= UNIX_TIMESTAMP(CURDATE())
GROUP BY a.value
假设 crm_date_time_column 绝不会包含将来的时间(例如,明天,下个月等),但如果可以,则只需添加:
This assumes the crm_date_time_column will never contain times which are in the future (e.g. tomorrow, next month, etc.), but if it can, you would just add:
AND b.crm_date_time_column < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)
作为 WHERE 子句中的另一个条件.
as another condition in the WHERE clause.
更多推荐
在SQL(MySQL)第2部分中基于今天的日期返回查询结果
本文发布于:2023-10-19 15:10:15,感谢您对本站的认可!
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:查询结果 日期 SQL MySQL
发布评论