我用这个查询得到昨天的所有销售额。把这个出口到别的地方。
由于有很多被取消,待付款,处理等,我想只有出口的那些已经完成。
但不知怎么的,mysql忽略了第一个限制。
我总是能拿到所有的销售额。我写什么并不重要。
它只需要是昨天使用的状态。
$sql = '
SELECT
sales_flat_order_grid.entity_id,
sales_flat_order_grid.increment_id AS increment_id,
sales_flat_order_grid.quote_id AS quote_id,
sales_flat_order_grid.store_name AS store_name,
sales_flat_order_grid.created_at AS created_at,
sales_flat_order_grid.billing_name AS billing_name,
sales_flat_order_grid.payment_method AS payment_method,
sales_flat_order_grid.grand_total AS grand_total,
sales_order_status.label AS status,
sales_flat_order_item.sku AS sku,
sales_flat_order_item.name AS name,
sales_flat_order_item.price_incl_tax AS price_incl_tax,
sales_flat_order_item.product_options AS product_options,
sales_flat_order_payment.additional_information AS additional_information
FROM
sales_flat_order_grid,
sales_order_status,
sales_flat_order_item,
sales_flat_order_payment
WHERE
sales_flat_order_grid.status = "Complete"
AND sales_flat_order_grid.entity_id = sales_flat_order_item.order_id
AND sales_flat_order_grid.entity_id = sales_flat_order_payment.entity_id
AND sales_flat_order_grid.created_at >= "' . $date . ' 00:00:00"
AND sales_flat_order_grid.created_at <= "' . $date . ' 23:59:59"
ORDER BY
sales_flat_order_grid.entity_id DESC
';
最佳答案
当您编写这样的查询时,您将面临灾难:
FROM
sales_flat_order_grid,
sales_order_status,
sales_flat_order_item,
sales_flat_order_payment
WHERE
sales_flat_order_grid.status = "Complete"
AND sales_flat_order_grid.entity_id = sales_flat_order_item.order_id
AND sales_flat_order_grid.entity_id = sales_flat_order_payment.entity_id
AND sales_flat_order_grid.created_at >= "' . $date . ' 00:00:00"
AND sales_flat_order_grid.created_at <= "' . $date . ' 23:59:59"
为什么?因为它太容易错过一个
JOIN
的条件。相反,请使用以下语法:
FROM
sales_flat_order_grid
INNER JOIN sales_order_status ON ....
INNER JOIN sales_flat_order_item ON sales_flat_order_grid.entity_id = sales_flat_order_item.order_id
INNER JOIN sales_flat_order_payment ON sales_flat_order_grid.entity_id = sales_flat_order_payment.entity_id
WHERE
sales_flat_order_grid.status = "Complete"
AND sales_flat_order_grid.created_at >= "' . $date . ' 00:00:00"
AND sales_flat_order_grid.created_at <= "' . $date . ' 23:59:59"
看到那里的
....
了吗?它缺少连接条件,因此它选择笛卡尔积。添加缺少的连接条件,结果应该更符合您的期望。关于php - mysql忽略where语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/14506094/