mysql - 如何根据第一个查询结果执行第二个查询

Question

我遇到一种情况，我需要根据第一个查询 结果执行 第二个 查询。

这是我的第一个查询结果:

id  current_date    starts_on   ends_on Product_id  days_remaining
1   2017-06-21T12:00:00 2017-06-20T12:00:00Z    2017-06-23T12:00:00Z    3   2
2   2017-06-21T12:00:00 2017-06-01T12:00:00Z    2017-06-03T12:00:00Z    4   0
3   2017-06-21T12:00:00 2017-06-01T12:00:00Z    2017-06-03T12:00:00Z    7   0
4   2017-06-21T12:00:00 2017-07-01T12:00:00Z    2017-07-03T12:00:00Z    5   12

在这里您可以看到 remaining_days 有 2 个 zeros(0) 作为发生的结果 我想从 获取所有 product_name产品表

My Both the Tables and Query for Above result is implemented but for getting product_name is not implemented. please help me here

这是我的 sqlfiddle: http://sqlfiddle.com/#!9/5bf34/1

我的问题:如果 days_remaining(见上面的结果)为 0，我想获取所有 product_name

in short get all product_name if days_remaining is 0

Answer 1

使用您自己的查询，我扩展了它:

#assume this is the date as NOW()
SET @mycurrentDate = '2017-06-21T12:00:00';
SELECT * FROM product WHERE product_id IN (
  SELECT product_id FROM (
    SELECT 
      id, 
      @mycurrentDate AS `current_date`,
      `tn`.`start` AS `starts_on`,
      `tn`.`end` AS `ends_on`,
      `tn`.`product_id` AS `Product_id`,
      IF(`tn`.`end` >= @mycurrentDate,
         DATEDIFF(`tn`.`end`, @mycurrentDate), #show days until event ends
         0 #the event has already passed
      ) AS `days_remaining`
    FROM `booking` AS `tn`
  ) tmp1
  WHERE `days_remaining` = 0
)