如何在SQL销售报表中按ALU范围（4000000-4009999）过滤数据

方法一：在子查询的WHERE子句中添加过滤（推荐） #

将ALU的过滤条件添加到子查询的WHERE部分，能在聚合计算前就过滤掉无关数据，提升查询性能。只需在现有WHERE条件后新增一行：

and i.ALU between 4000000 and 4009999

完整修改后的代码如下：

select 
       --r.sbs_name as "Subsidiary",
       r.store_code as "Store",
       r.ALU as "ALU",
       --r.dcs_code as "DCS Code",
       --r.vend_code as "Vendor Code",
       r.description1 as "Description1",
       --r.attribute as "Attribute",
       --r.item_size as "Size",
       r.qty_sold as "Qty Sold",
       round(
            case when r.ext_cost = 0
                then 0
                else (r.ext_cost / r.qty_sold)
            end, 2) as "Item Cost",
       r.ext_cost as "Total Cost",
       r.ext_price as "Total Retail"
from
(
    select s.sbs_name,
           St.Store_code,
           d.dcs_code,
           v.vend_code,
           i.description1,
           i.attribute,
           i.item_size,
           i.ALU,
           sum(decode(di.item_type, 2, di.qty * -1, di.qty)) as qty_sold,
           sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.cost)), 2)) as ext_cost,
           sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.price)), 2)) as ext_price
      from rps.document do
     inner join rps.document_item di
        on do.sid = di.doc_sid
     inner join rps.subsidiary s
        on do.sbs_no = s.sbs_no
       and di.sbs_no = s.sbs_no
     inner join rps.dcs d
        on s.sid = d.sbs_sid
       and di.dcs_code = d.dcs_code
     inner join rps.store st
        on do.store_sid = st.sid
     inner join rps.invn_sbs_item i
        on di.invn_sbs_item_sid = i.sid
       and s.sid = i.sbs_sid
     inner join rps.vendor v
        on i.vend_sid = v.sid
     where do.receipt_type in (0, 1)
       and di.item_type in (1, 2)
       and do.status = 4
       and =-=do.sbs_no=-=
       and =-=st.store_no=-=
       and =-=st.store_code=-=
       and =-=trunc(do.created_datetime)=-=
       and i.ALU between 4000000 and 4009999 -- 新增ALU过滤条件
    group by s.sbs_name,
           St.Store_code,
           d.dcs_code,
           v.vend_code,
           i.description1,
           i.attribute,
           i.item_size,
           i.ALU
) r     

方法二：在外层查询的WHERE子句中添加过滤 #

如果需要保留子查询的通用性，也可以在外层查询中添加过滤条件：

select 
       --r.sbs_name as "Subsidiary",
       r.store_code as "Store",
       r.ALU as "ALU",
       --r.dcs_code as "DCS Code",
       --r.vend_code as "Vendor Code",
       r.description1 as "Description1",
       --r.attribute as "Attribute",
       --r.item_size as "Size",
       r.qty_sold as "Qty Sold",
       round(
            case when r.ext_cost = 0
                then 0
                else (r.ext_cost / r.qty_sold)
            end, 2) as "Item Cost",
       r.ext_cost as "Total Cost",
       r.ext_price as "Total Retail"
from
(
    select s.sbs_name,
           St.Store_code,
           d.dcs_code,
           v.vend_code,
           i.description1,
           i.attribute,
           i.item_size,
           i.ALU,
           sum(decode(di.item_type, 2, di.qty * -1, di.qty)) as qty_sold,
           sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.cost)), 2)) as ext_cost,
           sum(round((decode(di.item_type, 2, di.qty * -1, di.qty) * (di.price)), 2)) as ext_price
      from rps.document do
     inner join rps.document_item di
        on do.sid = di.doc_sid
     inner join rps.subsidiary s
        on do.sbs_no = s.sbs_no
       and di.sbs_no = s.sbs_no
     inner join rps.dcs d
        on s.sid = d.sbs_sid
       and di.dcs_code = d.dcs_code
     inner join rps.store st
        on do.store_sid = st.sid
     inner join rps.invn_sbs_item i
        on di.invn_sbs_item_sid = i.sid
       and s.sid = i.sbs_sid
     inner join rps.vendor v
        on i.vend_sid = v.sid
     where do.receipt_type in (0, 1)
       and di.item_type in (1, 2)
       and do.status = 4
       and =-=do.sbs_no=-=
       and =-=st.store_no=-=
       and =-=st.store_code=-=
       and =-=trunc(do.created_datetime)=-=
    group by s.sbs_name,
           St.Store_code,
           d.dcs_code,
           v.vend_code,
           i.description1,
           i.attribute,
           i.item_size,
           i.ALU
) r
where r.ALU between 4000000 and 4009999 -- 外层添加过滤条件

语法说明 #

BETWEEN a AND b 是SQL标准的范围筛选语法，会包含边界值，等价于 ALU >= 4000000 AND ALU <= 4009999，完全匹配你需要的4000000至4009999的范围要求。

内容的提问来源于stack exchange，提问作者Paul Bieschke Jr