mysql - 如何使用 CROSS JOIN 显示总数的百分比？

Question

我有一个货车订单的数据集，我需要计算完成 1 个订单的客户的百分比。我正在使用交叉连接来获得总数。但是，它一直显示错误。有什么问题吗？第 1 部分都可以显示总分，第 2 部分可以显示所有总分。但是，它不能与 CROSS JOIN 组合在一起。谢谢！

SELECT 
    SUM(client_total_price) 
    FROM 
        (SELECT SUM(total_price) AS client_total_price,
        COUNT(idvanOrder) AS number_of_order
        FROM `vanorder` GROUP BY requestor_client_id
    WHERE number_of_order =1)
    CROSS JOIN (SELECT SUM(total_price) AS total FROM `vanorder`) t2

标签： mysql

Answer 1

我相信您可以使用公用表表达式加窗口函数：

WITH prices AS (
SELECT
  SUM(total_price) OVER (PARTITION BY requestor_client_id) AS priceOrder
FROM vanorder
WHERE requestor_client_id IN (
  SELECT requestor_client_id
  FROM vanorder 
  GROUP BY requestor_client_id 
  HAVING COUNT(*) = 1)
), 
total AS (SELECT SUM(total_price) AS totalPrice FROM vanorder)
SELECT priceOrder, totalPrice, 100 * priceOrder / totalPrice AS percentage
FROM prices,total