douchenbiao0916
2014-10-17 12:57
浏览 133
已采纳

在MySQL中使用条件语句定义变量

I have a table "products" with columns like:

| price | discount_price | start_discount | end_discount | ... |

*start_discount and end_discount is a UNIX timestamp

Current price of a product depends on time - if 'time()' is between 'start_discount' and 'end_discount' then the current price is 'discount_price', otherwise is 'price'

I am trying to return "current_price" variable. In PHP, I would just write:

$current_price = (time() > $start_discount && time() < $end_discount)? $discount_price : $price;

How can I achieve the same as above in MySQL without involving PHP?

I have this so far:

SELECT *, (**price OR discount_price**) as current_price FROM products WHERE active = 1 AND current_price BETWEEN '100' AND '200' ORDER BY current_price;

图片转代码服务由CSDN问答提供 功能建议

我有一个表“products”,其列包含:

  | 价格|  discount_price |  start_discount |  end_discount |  ... | 
   
 
 

* start_discount和end_discount是UNIX时间戳

产品的当前价格取决于时间 - 如果 '时间()'介于' start_discount '和' end_discount '之间,当前价格为' discount_price ',否则为'价格'

我正在尝试返回“ current_price ”变量。 在PHP中,我只想写:

  $ current_price =(time()&gt; $ start_discount&amp;&amp; time()&lt; $ end_discount)?  $ discount_price:$ price; 
   
 
 

如果不涉及PHP,我如何在MySQL中实现与上述相同的功能?

我有 到目前为止:

  SELECT *,(** price OR discount_price **)as current_price FROM products WHERE active = 1 AND current_price BETWEEN'100'AND'200'ORDER BY  current_price; 
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • donglin6659 2014-10-17 13:10
    已采纳

    Here is a way to do it, I am only selecting the price you can select whatever you want from the table

    select
    case when
     now() > start_discount AND now() < end_discount then discount_price
    else price end as current_price
    from 
    products 
    WHERE 
    active = 1 
    AND price BETWEEN '100' AND '200'
    ORDER BY current_price
    

    EDIT : Just noticed you are using current_price in the where condition, which you can not do since its an alias so you may need to use a table column in the where clause or use having clause as

    select
    case when
     now() > start_discount AND now() < end_discount then discount_price
    else price end as current_price
    from 
    products 
    WHERE 
    active = 1 
    having current_price BETWEEN '100' AND '200'
    ORDER BY current_price
    

    Here is a test I did

    mysql> create table products (price int, discount_price int ,start_discount date ,end_discount date);
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into products values (100,60,'2014-10-01','2014-11-01'),(200,160,'2014-10-01','2014-10-10'),(300,200,'2014-11-01','2014-11-15');
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select case when  now() > start_discount AND now() < end_discount then discount_price else price end as current_price from  products order by current_price;
    +---------------+
    | current_price |
    +---------------+
    |            60 |
    |           200 |
    |           300 |
    +---------------+
    3 rows in set (0.00 sec)
    
    已采纳该答案
    打赏 评论
  • dscjp19831212 2014-10-17 13:28

    You could do this with an IF statement in mySQL

    select id, name,
      if(startDiscount_at <= now() && endDiscount_at > now(), discountPrice, price) as current_price
    from products
      where active = 1
      having current_price between 100 and 200
      order by current_price;
    

    Link to SQLFiddle

    打赏 评论

相关推荐 更多相似问题