oracle sql-根据条件过滤出重复的条目

纯粹的圣

我有以下查询以按日期产生sale_price:

SELECT 
product_name, 
SUM(sale_price) top_sale_price, 
sale_date,
COUNT(*) count
FROM sales
WHERE sale_date IN
    (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7)
GROUP BY 
product_name, 
sale_date

我的结果看起来像这样:

product_name     top_sale_price     sale_date     count
shoes            10.00              01-JUL-14     2
hat              11.00              30-JUN-14     1
shirt            12.00              24-JUN-14     3
...

我希望根据某些逻辑从每个分组中仅选择一件衬衫或帽子。例如,假设有一个“ is_valid_purchase”行。如果分组中有一个或多个项目(例如“鞋子”和“衬衫”中的项目),我想选择将“ is_valid_purchase”设置为true的项目(请注意,在此示例中,只有一个项目group by具有'is_valid_purchase'设置为true)。如何修改我的sql以执行我描述的内容?

戈登·利诺夫(Gordon Linoff)

这是你想要的吗?

with t as (
      SELECT product_name, SUM(sale_price) top_sale_price, sale_date, COUNT(*) count
      FROM sales
      WHERE sale_date IN
          (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7) and
         is_valid_purchase = 1
      GROUP BY product_name, sale_date
     )
select *
from t;

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章