在SQL(MySQL)第2部分中基于今天的日期返回查询结果 [英] Return results of query based on todays date in SQL (MySQL) Part 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 <另一个表中的/code>与 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部分中基于今天的日期返回查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

登录 关闭

扫码关注1秒登录

发送“验证码”获取 | 15天全站免登陆

友情链接： IT屋 Chrome插件 谷歌浏览器插件

IT屋 ©2016-2022 琼ICP备2021000895号-1 站点地图 站点标签 SiteMap <免责申明> 本站内容来源互联网,如果侵犯您的权益请联系我们删除.