Mysql Query：Where子句如果存在

I have a MySQL db and inside it a table called ProductMaster with 6 fields "Product_Id,Product_Name,Model,Opening_date,Closing_Date,Status". Opening_Date and Closing Date Can accept null values.So some records has values for this field

我有一个MySQL数据库,里面有一个名为ProductMaster的表,其中包含6个字段“Product_Id,Product_Name,Model,Opening_date,Closing_Date,Status”。 Opening_Date和Closing Date可以接受空值。因此,某些记录具有此字段的值

I have the below query to display records from this table.

我有以下查询来显示此表中的记录。

"select Product_Id,Product_Name,Model from ProductMaster  where Status=1"

Now I want to change the query to have a filter on the Opening_Date and Closing_Date if they are not null

现在我想更改查询,以便在Opening_Date和Closing_Date上有一个过滤器,如果它们不为null

Ex : If a Record is having Opening_Date as 05/01/2009 and Closing Date as 05/30/2009 Then i want to check whether today is a date in between these two dates and if Yes ,Returns the records

例如:如果一个记录的Opening_Date为05/01/2009,截止日期为05/30/2009那么我想检查今天是否是这两个日期之间的日期,如果是,则返回记录

If both field values are empty, Return Records

如果两个字段值都为空,则返回记录

Can any one help me to frame the query ? Thanks in advance

任何人都可以帮我构建查询框架吗?提前致谢

1 个解决方案

#1

The WHERE part of a query (the "WHERE clause") is just a long boolean expression. What does this mean? That MySQL just wants it to return either true or false, which it understands as "Yes. Include this row in the results." or "No. Don't include this row in the results."

查询的WHERE部分(“WHERE子句”)只是一个长布尔表达式。这是什么意思? MySQL只是希望它返回true或false,它理解为“是的。在结果中包含这一行。”或者“不。不要在结果中包含这一行。”

If I understand correctly, you want to do two things:

如果我理解正确,你想做两件事:

Check if the opening_date and closing_date are null

检查opening_date和closing_date是否为null

Check if today is between those two dates.

检查今天是否在这两个日期之间。

And you want #2 to only happen if #1 is true. Which could be expressed like:

如果#1为真,你只希望#2发生。可以表达如下:

#1 AND #2

Which would be evaluated as false if either #1 or #2 is false.

如果#1或#2为假,则将其评估为false。

That can be translated as:

这可以翻译为:

(opening_date IS NOT NULL) AND (closing_date IS NOT NULL)

(opening_date IS NOT NULL)AND(closing_date IS NOT NULL)

NOW() >= opening_date AND NOW <= closing_date

NOW()> = opening_date AND NOW <= closing_date

So if we treat those two like the #1 and #2 in the expression we said we were going to use (#1 AND #2) then we get:

因此,如果我们将表达式中的#1和#2视为我们将要使用的#1和#2,那么我们得到:

((opening_date IS NOT NULL) AND (closing_date IS NOT NULL)) 
AND (NOW() >= opening_date AND NOW <= closing_date`)

And that's the WHERE clause you need.

这就是你需要的WHERE子句。

#1