欢迎各种吐槽。
先讲问题:求今日营业额(营业额=当日餐饮收入+当日图书收入+当日商品收入)
首先,看看查询结果 。图书和商品在一张表,餐饮在别一张表,无关联关系。
图书和商品及餐饮一起查询:TO_DAYS(返回当前天数函数,从1970-01-01 00:00:00开始 NOW 函数返回当前的日期和时间)
SELECT
SUM (a.` paid `),
SUM (b.` totalmoney `) FROM mkt_order AS a,
mkt_caterersorder AS b
WHERE
TO_DAYS (a.createtime) = TO_DAYS (NOW())
AND TO_DAYS (b.` ctime `) = TO_DAYS (NOW())
此处(图书和商品)和餐饮的收入的结果是不对了。不知道哪位大神知道为什么,指点一二。
所以我分开查了一下:
图书和商品
SELECT
SUM (a.` paid `) AS paid
FROM
mkt_order a
WHERE
TO_DAYS (a.createtime) = TO_DAYS (NOW())
餐饮
SELECT
SUM(b.`totalmoney`) cateringIncome
FROM
mkt_caterersorder AS b WHERE TO_DAYS(b.ctime)=TO_DAYS(NOW())
但我为了方便放在一起:
(ROUND 函数用于把数值字段舍入为指定的小数位数。ROUND(column_name,decimals) )
SELECT
ROUND(SUM(a.` paid `), '2') AS a1
FROM
mkt_order a
WHERE
TO_DAYS (a.createtime) = TO_DAYS (NOW())
UNION ALL
SELECT
ROUND(SUM(b.` totalmoney `), '2') a1
FROM
mkt_caterersorder AS b
WHERE
TO_DAYS (b.ctime) = TO_DAYS (NOW())
把数据加到一起:
SELECT
SUM (c.a1) total
FROM
(
SELECT
ROUND(SUM(a.` paid `), '2') AS a1
FROM
mkt_order a
WHERE
TO_DAYS (a.createtime) = TO_DAYS (NOW())
UNION ALL
SELECT
ROUND(SUM(b.` totalmoney `), '2') a1
FROM
mkt_caterersorder AS b
WHERE
TO_DAYS (b.ctime) = TO_DAYS (NOW())
) c
total : 687.17
这次是对的。
注意:
ROUND(SUM(a.`paid`),'2') 查询时要做小数处理,不然会在显示的时候,小数位数会多出很多位
格式 :
ROUND(字段,'保留小数位数')
UNION 操作符
用于合并两个或多个 SELECT 语句的结果集。
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。