我发现无需使用group by就无法获得行数非常烦人 . 我只需要获得我的子查询返回的“总计数” .
这是我的子查询的样子：
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 子句，以便返回"lesser"行数，让我们调用 column y .
然后我要求将x的百分比取为y（即外部查询返回到子查询的行数）
我不想重复我的子查询只是为了计算行数 . 我知道吗？
这是我到目前为止所得到的：但当然这是错误的 . 我无法计算所有行，而不必排除选择列或在group by中使用它们 . 我该解决这个问题吗？
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
1 回答
您可以将子查询更改为SELECT INTO查询，并将数据放入临时表中，并在主查询中使用它，并单独选择该临时表的count（*） . 那几乎应该满足你的要求 .