我正在使用Google Chart API构建产品销售报告的折线图,并使用UNION ALL将数据按天分离,基本上是为每一天运行相同的查询循环。例如,一整年的报告将重复查询,使用UNION ALL 365次。我猜这不是最佳实践的例子,如果有人能指点我如何优化这个查询,我将非常感激。
SELECT SUM(op.qty) AS qty
FROM uc_order_products op
LEFT JOIN uc_orders o ON o.order_id = op.order_id
LEFT JOIN node n ON n.nid = op.nid
LEFT JOIN node_type nt ON nt.type = n.type
WHERE (o.created > 1247695200) AND (o.created < 1247781600)
AND (o.order_status = 'completed')
AND (nt.type = 'book' OR nt.type = 'digital_movie')
UNION ALL
SELECT SUM(op.qty) AS qty
FROM uc_order_products op
LEFT JOIN uc_orders o ON o.order_id = op.order_id
LEFT JOIN node n ON n.nid = op.nid
LEFT JOIN node_type nt ON nt.type = n.type
WHERE (o.created > 1247781600) AND (o.created < 1247868000)
AND (o.order_status = 'completed')
AND (nt.type = 'book' OR nt.type = 'digital_movie')
UNION ALL
SELECT SUM(.......
感谢您的快速回复!根据jspcal提供的查询示例,我的查询最终变成了这样:
SELECT SUM(op.qty) AS qty, DATE_FORMAT(FROM_UNIXTIME(o.created),'%d-%m-%Y') AS day
FROM uc_order_products op
LEFT JOIN uc_orders o ON o.order_id = op.order_id
LEFT JOIN node n ON n.nid = op.nid
LEFT JOIN node_type nt ON nt.type = n.type
WHERE (o.created > 1247695200) AND (o.created < 1263596400)
AND (o.order_status = 'completed')
AND (nt.type = 'book' OR nt.type = 'digital_movie')
GROUP BY day
使用PHP,我将完整的日期范围数组与查询结果中的strtotime($data->day)
匹配的数组键组合起来,以获取没有销售的天数。