dongzhong5833 2015-07-31 09:46 采纳率: 0%
浏览 99
已采纳

Mysql - 将数组元素与字段值进行比较

I have a table with structure

id | item | order_date | delivery_date

And I have an array of dates, for example

$dates = array('2015-06-01', '2015-06-11', '2015-06-26');

What I need to do is to find count of items which where ordered, but not delivered yet on each date. Because of my code complexity, it is impossible to foreach array and search for each date separately.

There is an example of what I think the query should look like, but I am starting wondering if it is even possible to make it work this way (without foreaching each date).

SELECT SUM(id) FROM orders WHERE order_date > IN({implode(",", $dates)}) AND delivery_date < IN ({implode(",", $dates)}) GROUP BY ???

Notice: I would like to GROUP BY each date given in an array.

  • 写回答

1条回答 默认 最新

  • dongyi1429 2015-07-31 10:14
    关注

    Use a subquery that returns all the dates in the array. Then you can join with that, and use its column in the GROUP BY.

    $subquery = implode(' UNION ', array_map(function($d) {
        return "SELECT '$d' AS date";
    }, $d));
    $query = "SELECT d.date, SUM(id)
              FROM orders
              JOIN ($subquery) AS d ON d.date BETWEEN order_date AND delivery_date
              GROUP BY d.date";
    

    Note that if an order matches multiple dates, it will be counted for each of them.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?