MySQL：在SELECT中添加条件

问题描述：

I am having a huge SELECT where I need to make some conditions.

Eg:

SELECT `t`.`title`, `t`.`price_paid`, `t`.`date_creation`, `t`.`date_upload` 
  FROM `table_name` AS `t`
 WHERE `t`.`date_creation` >= "'.$year.'-'.$month.'-01" 
   AND `t`.`date_creation` < "'.$year.'-'.($month+1).'-01"

I declare in PHP the values of $year and $month and I want the rest to be made in MYSQL (I have strong reasons I can detail if needed)

What I need is to make the date_upload empty (null or 0) and price also empty if the date_upload is not from the same interval as t.date_creation

答

The solutions so far seems to be:

-> MySQL Nested "If" in Select Queries

IF(condition, value if true, value if false)

Something like

SELECT t.title, if (t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_upload < "'.$year.'-'.($month+1).'-01" ,t.price_paid, '0') as price_paid, t.date_creation, if (t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_upload < "'.$year.'-'.($month+1).'-01" ,t.date_upload, '') as date_upload FROM table_name AS t WHERE t.date_creation >= "'.$year.'-'.$month.'-01" AND t.date_creation < "'.$year.'-'.($month+1).'-01"

and other solutions seems to be Control Flow Functions

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

Found on http://komlenic.com/254/mysql-nested-if-in-select-queries/ http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

答

Don't do date math like that. if you're december, you'll be asking for MySQL to look for 2013-13-01 on the high end. Use actual mysql date math:

SELECT ...
WHERE t_date_creation BETWEEN (now() - INTERVAL 1 MONTH) AND (now() + INTERVAL 1 MONTH)

AS for your query, it'd be an UPDATE query:

UPDATE yourtable
SET date_upload = NULL, price = NULL
WHERE date_restriction_logic_here.

MySQL：在SELECT中添加条件

相关推荐