select 货品,sum(销售额) AS 销售额,sum(销售额)/sum(总销售额)*100% as 所占比率,sum(数量) AS 数量 ( SELECT l_goods.name as 货品, SUM(s_saledetail.price * s_saledetail.quantity) AS 销售额, (SELECT SUM(s_saledetail.price * s_saledetail.quantity) FROM s_sale INNER JOIN s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN l_goods ON s_saledetail.goodsid = l_goods.goodsid) AS 总销售额, SUM(s_saledetail.quantity) AS 数量 FROM s_sale INNER JOIN s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN l_goods ON s_saledetail.goodsid = l_goods.goodsid GROUP BY l_goods.name WITH cubeunionSELECT l_goods.name AS 货品, SUM(pos_saledetail.price * pos_saledetail.quantity) AS 销售额, (SELECT SUM(pos_saledetail.price * pos_saledetail.quantity) FROM pos_saledetail INNER JOIN l_goods ON pos_saledetail.goodsid = l_goods.goodsid) AS 总销售额, SUM(pos_saledetail.quantity) AS 数量, FROM pos_saledetail INNER JOIN l_goods ON pos_saledetail.goodsid = l_goods.goodsid GROUP BY l_goods.name WITH cube ) as 总表 group by 货品
不好意思,掉了一个"from":select 货品,sum(销售额) AS 销售额,sum(销售额)/sum(总销售额)*100% as 所占比率,sum(数量) AS 数量 from ( SELECT l_goods.name as 货品, SUM(s_saledetail.price * s_saledetail.quantity) AS 销售额, (SELECT SUM(s_saledetail.price * s_saledetail.quantity) FROM s_sale INNER JOIN s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN l_goods ON s_saledetail.goodsid = l_goods.goodsid) AS 总销售额, SUM(s_saledetail.quantity) AS 数量 FROM s_sale INNER JOIN s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN l_goods ON s_saledetail.goodsid = l_goods.goodsid GROUP BY l_goods.name WITH cubeunionSELECT l_goods.name AS 货品, SUM(pos_saledetail.price * pos_saledetail.quantity) AS 销售额, (SELECT SUM(pos_saledetail.price * pos_saledetail.quantity) FROM pos_saledetail INNER JOIN l_goods ON pos_saledetail.goodsid = l_goods.goodsid) AS 总销售额, SUM(pos_saledetail.quantity) AS 数量, FROM pos_saledetail INNER JOIN l_goods ON pos_saledetail.goodsid = l_goods.goodsid GROUP BY l_goods.name WITH cube ) as 总表 group by 货品
先把这两表用UNION ALL查询连起一个表再在这个表里查询结果按你上面的方法就行了
(
SELECT l_goods.name as 货品, SUM(s_saledetail.price * s_saledetail.quantity) AS 销售额,
(SELECT SUM(s_saledetail.price * s_saledetail.quantity)
FROM s_sale INNER JOIN
s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN
l_goods ON s_saledetail.goodsid = l_goods.goodsid) AS 总销售额,
SUM(s_saledetail.quantity) AS 数量
FROM s_sale INNER JOIN
s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN
l_goods ON s_saledetail.goodsid = l_goods.goodsid
GROUP BY l_goods.name WITH cubeunionSELECT l_goods.name AS 货品, SUM(pos_saledetail.price * pos_saledetail.quantity) AS 销售额,
(SELECT SUM(pos_saledetail.price * pos_saledetail.quantity)
FROM pos_saledetail INNER JOIN
l_goods ON pos_saledetail.goodsid = l_goods.goodsid) AS 总销售额,
SUM(pos_saledetail.quantity) AS 数量,
FROM pos_saledetail INNER JOIN
l_goods ON pos_saledetail.goodsid = l_goods.goodsid
GROUP BY l_goods.name WITH cube
) as 总表
group by 货品
from
(
SELECT l_goods.name as 货品, SUM(s_saledetail.price * s_saledetail.quantity) AS 销售额,
(SELECT SUM(s_saledetail.price * s_saledetail.quantity)
FROM s_sale INNER JOIN
s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN
l_goods ON s_saledetail.goodsid = l_goods.goodsid) AS 总销售额,
SUM(s_saledetail.quantity) AS 数量
FROM s_sale INNER JOIN
s_saledetail ON s_sale.billid = s_saledetail.billid INNER JOIN
l_goods ON s_saledetail.goodsid = l_goods.goodsid
GROUP BY l_goods.name WITH cubeunionSELECT l_goods.name AS 货品, SUM(pos_saledetail.price * pos_saledetail.quantity) AS 销售额,
(SELECT SUM(pos_saledetail.price * pos_saledetail.quantity)
FROM pos_saledetail INNER JOIN
l_goods ON pos_saledetail.goodsid = l_goods.goodsid) AS 总销售额,
SUM(pos_saledetail.quantity) AS 数量,
FROM pos_saledetail INNER JOIN
l_goods ON pos_saledetail.goodsid = l_goods.goodsid
GROUP BY l_goods.name WITH cube
) as 总表
group by 货品