插入新行，合并相似的行

Question

我需要完成的是:

通过合并同一表(t_order)中的两行或多行，在t_order中插入新行，其中所有值都是相同的，除了order_id (Identity)和order_number

新行将表示一个合并的订单。

到同一地址的两个订单合并为一个

插入

前的表示例

order_id   order_number   ship_addrs1    ship_to_zip
--------   ------------   -----------    -----------
   1       ABC001         111 1st St     11111
   2       ABC002         123 Main St    12345  <--- Source Row 
   3       ABC003         123 Main St    12345  <--- Source Row
   4       ABC004         111 2nd St     11111

插入后的结果(源顺序必须保留)

order_id   order_number   ship_addrs1    ship_to_zip
--------   ------------   -----------    -----------
   1       ABC001         111 1st St     11111
   2       ABC002         123 Main St    12345
   3       ABC003         123 Main St    12345 
   4       ABC004         111 2nd St     11111
   5       ABC005         123 Main St    12345  <--- New Row

我考虑使用下面的代码来完成这一点，但不确定我需要做什么来合并这三行。

SELECT * INTO    tmp_order_cosolidation 
  FROM           t_order 
  WHERE          order_id = 1 AND order_number = ABC002
ALTER TABLE      tmp_order_cosolidation 
  DROP COLUMN    order_id, ordern_number
INSERT INTO      t_order 
  SELECT             * 
  FROM           tmp_order_cosolidation;
DROP TABLE       tmp_order_cosolidation ;

提前感谢您的回答

Answer 1

您的订单表应该有更多的列来显示订单是合并、符合合并条件还是已经合并。下面是我提出的解决方案，它增加了列。所有推断的列都是大写的

--VIEW ROWS TO INSERT
select count(order_id),ship_addrs1,ship_to_zip2
from t_order
where CONSOL_ELIGIBLE = 'Y' and CONSOL_COMPLETED = 'N'
group by ship_addrs1,ship_to_zip2
having count(order_id) > 1
--TEMP TABLE FOR INSERT
declare @tmptable TABLE (total_orders int,ship_addrs1 nvarchar(50),ship_to_zip2 nvarchar(50),CONSOL_ELIGIBLE nvarchar(1))
insert into @tmptable (total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE)
(select count(order_id),ship_addrs1,ship_to_zip2,'N'
from t_order
where CONSOL_ELIGIBLE = 'Y' and CONSOL_COMPLETED = 'N'
group by ship_addrs1,ship_to_zip2
having count(order_id) > 1)
--INSERT FROM TEMP TABLE
insert into ORDER_TABLE (total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE)
(select total_orders, ship_addrs1,ship_to_zip2,CONSOL_ELIGIBLE
from @tmptable)