mysql - 获取子查询返回的行数以及子查询返回的列数

Question

我发现在不使用分组依据的情况下无法获取行数真的很烦人。我只需要获取子查询返回的“总计数”。

这是我的子查询的样子:

select sales_flat_order.increment_id, sales_flat_order.created_at, sales_flat_order.status, dispatch.dispatch_date, 
DATEDIFF(TO_DATE(dispatch.dispatch_date), TO_DATE(sales_flat_order.created_at)) as delay 
FROM 
magentodb.sales_flat_order 
LEFT OUTER JOIN erpdb.dispatch 
ON 
sales_flat_order.increment_id == dispatch.order_num 
where
TO_DATE(created_at) >= DATE_SUB(current_date(),6)
AND 
TO_DATE(created_at) <= DATE_SUB(current_date(), 3)
AND
sales_flat_order.status NOT IN ('canceled', 'exchange', 'rto', 'pending_auth', 'pending_payment' ,'partial_refund','refund', 'refund_cash', 'partial_refund_cash', 'holded')
)
AS TempFiltered

现在，我在外部查询中添加 1 个额外的 WHERE 子句，以便它返回“较少”的行数，我们将此称为 column y 。

然后我需要取 x 与 y 的百分比(即外部查询返回到子查询的行数)

我不想重复子查询只是为了获取行数。我怎样才能得到它？

这就是我到目前为止所得到的:但这当然是错误的。如果不必排除选择列或在分组依据中使用它们，我无法获得所有行的计数。我该如何解决这个问题？

SELECT tempfiltered.delay, count(*) as countOfOrders,(100*count(*))/tempfiltered.Total) over () as percentage
FROM
(
select count(*) as Total, sales_flat_order.increment_id, sales_flat_order.created_at, sales_flat_order.status, dispatch.dispatch_date, 
DATEDIFF(TO_DATE(dispatch.dispatch_date), TO_DATE(sales_flat_order.created_at)) as delay 
FROM 
magentodb.sales_flat_order 
LEFT OUTER JOIN erpdb.dispatch 
ON 
sales_flat_order.increment_id == dispatch.order_num 
where
TO_DATE(created_at) >= DATE_SUB(current_date(),6)
AND 
TO_DATE(created_at) <= DATE_SUB(current_date(), 3)
AND
sales_flat_order.status NOT IN ('canceled', 'exchange', 'rto', 'pending_auth', 'pending_payment' ,'partial_refund','refund', 'refund_cash', 'partial_refund_cash', 'holded')
)
AS TempFiltered
Where 
 DATEDIFF(TO_DATE(TempFiltered.dispatch_date), TO_DATE(TempFiltered.created_at)) > 1     
GROUP BY tempfiltered.delay
ORDER BY tempfiltered.delay

Answer 1

您可以将子查询更改为 SELECT INTO 查询，并将数据放入临时表中，并在主查询中使用它，然后单独选择该临时表的 count(*) 。这应该基本满足你的要求了。