sql - 具有 group by 和 having 的复杂 SQL 查询

Question

我有一个表订单

orders (
id  int unsigned not null,
fcr_date  TIMESTAMP,
completion_date TIMESTAMP,
factory_no  varchar(255),
vendor_no  varchar(255))

如有数据类型拼写错误，请忽略。

我想编写一个 sql 查询来帮助我过滤每个供应商工厂的数据。要获取的数据包括每个供应商工厂的订单数(一组唯一的 vendor_no、factory_no)、vendor_no、factory_no 以及 fcr_date 大于 completion_date 的订单百分比(因此百分比 = fcr_date 大于完成的订单数日期/订单数量)。之后我需要过滤百分比大于 20% 的数据。

我写了以下查询:

SELECT  vendor_no As vendor,
        factory_no As factory,
        COUNT(1) as count,
        SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders,
    ROUND(filter_orders / count * 100, 4) as  percent  
FROM    @orders
GROUP BY vendor_no,
        factory_no
HAVING percent>20

但是 postgresql 提示它需要在表中有一个名为 percent 的列来根据它过滤结果。感谢您的帮助。

谢谢。

Answer 1

将其更改为:

HAVING ROUND(filter_orders / count * 100, 4) > 20

因为 percent 不是实际的列，您需要为其提供计算以执行过滤器。

编辑

好的，进一步看一下，您至少有两种编写方法:我推荐的是第一种，它涉及包装在子查询中(正如有人已经建议的那样):

选项1

SELECT  vendor As vendor,        factory As factory,        [count],        ROUND(filter_orders / count * 100, 4) as  [percent]FROM(    SELECT  vendor_no As vendor,            factory_no As factory,            COUNT(1) as count,            SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders    FROM    @orders    GROUP BY vendor_no,            factory_no) AS aWHERE ROUND(filter_orders / count * 100, 4) > 20

选项 2

SELECT  vendor_no As vendor,        factory_no As factory,        COUNT(1) as count,        SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders,        ROUND(SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) / count(1) * 100, 4) as  [percent]FROM    @ordersGROUP BY vendor_no,        factory_noHAVING ROUND(SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) / count(1) * 100, 4) > 20