如何合并SQL行--仅当所有行都符合条件时

Question

我有一个表(Oracle SQL(，其中包含每个商店位置的商品价格列表的详细信息。我想把几行合并为一行，但前提是一个项目的所有行都符合条件：该项目在所有位置的价格都相同。

数据表(简化(如下所示：

list_id, item_id, location_id, item_price
1        1        1            1.99
1        1        2            1.99
1        1        3            1.99
1        2        1            3.99
1        2        2            3.99
1        2        3            3.99
1        3        1            5.99
1        3        2            7.99
1        3        3            8.99

我想要这个：

list_id, item_id, location_id, item_price
1        1        0            1.99
1        2        0            3.99
1        3        1            5.99
1        3        2            7.99
1        3        3            8.99

项目1和2的行已分别合并为一行，位置设置为零(全部(。项目3的行保持不变，因为并非所有位置的价格都相同。

这个查询帮助我确定项目何时不需要合并(存在具有相同item_id的两行(：

select count(list_id), item_id, item_price 
from list_detail
group by item_id, item_price

但我无法理解它将如何融入更大的触发器、脚本或任何能够识别和组合行的东西。

注意：我无法更改表的结构，因为它被许多其他进程所依赖。

如何最好地识别并组合所有位置价格相同的行？脚本、触发器、调度控制台应用程序？

Answer 1

一个选项使用窗口函数，然后使用distinct:

select distinct list_id, item_id, location_id, item_price
from (
    select list_id, item_id, item_price,
        case when min(item_price) over(partition by list_id, item_id) = max(item_price) over(partition by list_id, item_id) 
                then 0
                else location_id
            end location_id
    from mytable t
) t

基本思想是比较具有相同list_id和item_id的组中的最小和最大价格。当它们相等时，我们知道组中只有一个不同的值，所以我们将location_id变成0，否则我们保持原样。剩下的就是保持不同的值。

Answer 2

由于必须在单个语句中更新某些行并删除其他行，因此最好使用merge语句，这正是出于此目的。

s(源(行集是聚合的结果，用于标识必须修改的(list_id, item_id)。

注意，我假设价格永远不是null；如果它可以是null，您必须解释应该如何处理它。

将提供使用分析函数的解决方案。如果效率(速度(很重要，下面的解决方案会更好；当两者都做相同的工作时，聚合比分析函数快得多。

merge into sample_data t
  using (
          select list_id, item_id, min(location_id) as min_loc_id
          from   sample_data
          group  by list_id, item_id
          having min(item_price) = max(item_price)
        ) s
    on (t.list_id = s.list_id and t.item_id = s.item_id)
when matched then
  update
    set   t.location_id = case when t.location_id = s.min_loc_id then 0 end
  delete
    where t.location_id is null
;

来自目标(即您的基表(的行只有在list_id, item_id与源匹配时才会受到影响；其他行将保持不变。(这些未更改的行是价格而不是在所有位置都相同的项目的行，因此相应的list_id, item_id不会出现在源中。(

CCD_ 13部分将第一个位置id更改为0，并将所有其他位置更改为CCD_。然后delete部分将删除位置id为null的所有行。在这个步骤中，位置id是在update部分完成其工作之后修改的。因此，除了受影响的location_id, item_id的一行外，所有行都将被delete步骤删除。

Answer 3

嗯。您可以使用窗口功能：

select list_id, item_id,
       (case when min_price = max_price then 0
             else location_id
        end) as location_id,
       price
from (select t.*,
             min(price) over (partition by list_id, item_id) as min_price,
             max(price) over (partition by list_id, item_id) as max_price
      from t
     ) t
group by list_id, item_id,
         (case when min_price = max_price then 0
               else location_id
          end), price;

另一种方法是使用exists和union all:

select list_id, item_id, location_id, price
from t
where exists (select 1
              from t t2
              where t2.list_id = t.list_id and
                    t2.item_id = t.item_id and
                    t2.price <> t.price
             )
union all
select list_id, item_id, 0, max(price)
from t
group by list_id, item_id
having min(price) = max(price);

Answer 4

您可以对UPDATE和DELETE使用MERGE语句，如果您使用分析函数来识别受影响的行，则可以使用ROWID伪列关联合并，该伪列可以执行自联接(比比较值更有效(：

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         rn
  FROM   (
    SELECT ROW_NUMBER() OVER ( PARTITION BY list_id, item_id ORDER BY location_id )
             AS rn,
           MIN( item_price ) OVER ( PARTITION BY list_id, item_id ) AS min_price,
           MAX( item_price ) OVER ( PARTITION BY list_id, item_id ) AS max_price
    FROM   table_name
  )
  WHERE min_price = max_price
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
  UPDATE SET location_id = 0
  DELETE WHERE src.rn > 1;

对于样本数据：

CREATE TABLE table_name ( list_id, item_id, location_id, item_price ) AS
SELECT 1, 1, 1, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 2, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 3, 1.99 FROM DUAL UNION ALL
SELECT 1, 2, 1, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 2, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 3, 3.99 FROM DUAL UNION ALL
SELECT 1, 3, 1, 5.99 FROM DUAL UNION ALL
SELECT 1, 3, 2, 7.99 FROM DUAL UNION ALL
SELECT 1, 3, 3, 8.99 FROM DUAL;

更新2行并删除4行，将表保留为：

LIST_ID|ITEM_ID|LOCATION_ID|ITEM_PRICE------：|--------：|---------：|-------：1|1|0|1.991|2|0|3.991|3|1|5.991|3|2|7.991|3|3|8.99

db<gt；小提琴这里

---

如果item_price可以有NULL值，则查询可以扩展为仅在行都是非NULL或都是NULL:时进行筛选

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         rn
  FROM   (
    SELECT ROW_NUMBER() OVER ( PARTITION BY list_id, item_id ORDER BY location_id )
             AS rn,
           MIN( item_price ) OVER ( PARTITION BY list_id, item_id ) AS min_price,
           MAX( item_price ) OVER ( PARTITION BY list_id, item_id ) AS max_price,
           COUNT(item_price)
             OVER ( PARTITION BY list_id, item_id ) AS num_non_null,
           COUNT(*)
             OVER ( PARTITION BY list_id, item_id ) AS num_locations
    FROM   table_name
  )
  WHERE ( min_price = max_price AND num_non_null = num_locations )
  OR    ( num_non_null = 0 )
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
  UPDATE SET location_id = 0
  DELETE WHERE src.rn > 1;

对于样本数据：

CREATE TABLE table_name ( list_id, item_id, location_id, item_price ) AS
SELECT 1, 1, 1, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 2, 1.99 FROM DUAL UNION ALL
SELECT 1, 1, 3, 1.99 FROM DUAL UNION ALL
SELECT 1, 2, 2, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 3, 3.99 FROM DUAL UNION ALL
SELECT 1, 2, 4, 3.99 FROM DUAL UNION ALL
SELECT 1, 3, 1, 5.99 FROM DUAL UNION ALL
SELECT 1, 3, 2, 7.99 FROM DUAL UNION ALL
SELECT 1, 3, 3, 8.99 FROM DUAL UNION ALL
SELECT 1, 4, 8, 1.99 FROM DUAL UNION ALL
SELECT 1, 4, 9, NULL FROM DUAL UNION ALL
SELECT 1, 5, 1, NULL FROM DUAL UNION ALL
SELECT 1, 5, 2, NULL FROM DUAL;

输出：

LIST_ID|ITEM_ID|LOCATION_ID|ITEM_PRICE------：|--------：|---------：|-------：1|1|0|1.991|2|0|3.991|3|1|5.991|3|2|7.991|3|3|8.991|4|8|1.991|4|9|null1|5|0|null

db<gt；小提琴这里