根据一列的值删除重复行

Question

我希望第一个查询的结果从第二查询中覆盖结果：

SELECT 
    CONVERT(DATE,a.ins_timestamp) AS 'Date',  
    a.Prod_code, 
    a.Curr_boxes, 
    a.Label_barcode, 
    b.From_ord_no, 
    NULL AS To_ord_no, 
    CASE
        WHEN a.From_batch >= a.To_batch THEN a.From_batch 
        WHEN a.To_batch >= a.From_batch THEN a.To_batch 
        ELSE a.From_batch
    END AS 'Batch',  
    a.Weight, 
    'IN' AS 'Direction' 
FROM 
    a 
JOIN 
    b ON a.Label_barcode = b.Label_barcode 
WHERE 
    (a.ins_timestamp BETWEEN ? AND ?) 
    AND (a.To_batch = ?) 
    AND (a.From_batch = 0) 
    AND (a.Type = 'Consumption') 
    AND (a.To_status <> 'STOCK') 
    AND (b.From_status = 'PORDER') 
GROUP BY 
    CONVERT(DATE,a.ins_timestamp),  
    a.Prod_code, 
    a.Curr_boxes, 
    a.Label_barcode, 
    b.From_ord_no, 
    a.From_batch, 
    a.To_batch, 
    a.Weight, 
    a.From_status,
    a.To_status

UNION 

SELECT 
    CONVERT(DATE,b.ins_timestamp) AS 'Date',  
    b.Prod_code, 
    b.Curr_boxes, 
    b.Label_barcode, 
    NULL AS From_ord_no, 
    NULL AS To_ord_no, 
    CASE
        WHEN b.From_batch >= b.To_batch THEN b.From_batch 
        WHEN b.To_batch >= b.From_batch THEN b.To_batch 
        ELSE                             b.From_batch
    END AS 'Batch',  
    b.Weight, 
    'IN' AS 'Direction' 
FROM 
    b 
WHERE 
    (b.From_batch = 0) 
    AND (b.Type = 'Consumption') 
    AND (b.ins_timestamp BETWEEN ? AND ?) 
    AND (b.To_batch = ?) 
    AND (b.To_status<>'STOCK') 
GROUP BY 
    CONVERT(DATE,b.ins_timestamp),  
    b.Prod_code, 
    b.Curr_boxes, 
    b.Label_barcode,  
    b.From_batch, 
    b.To_batch, 
    b.Weight, 
    b.From_status,
    b.To_status

请注意：问号是将在Excel中传递的参数。

当前输出：

第二查询的结果将返回'from_order_no'列的nulls，但是Select语句中的所有其他内容都将完全相同。在这种情况下，联盟将无法删除重复行，因此同一label_barcode将有两行数据。

所需的输出：

那么问题是 - 如何从第二个查询中删除结果，该查询显示了frof_order_no的nulls并将结果避免第一个查询？

Answer 1

提供from_ord_no是唯一要忽略的区别，您可以将查询结果分组

select 'Date',  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    max(From_ord_no) From_ord_no, 
    To_ord_no, 
    'Batch',  
    Weight, 
    'Direction' 
from (
  <your query>
) t
group by 'Date',  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    To_ord_no, 
    'Batch',  
    Weight, 
    'Direction'

Answer 2

您可以创建一个临时表。在下面的示例中，这称为#newtable。主题标签很重要，因为这实际上是使其成为“临时”表的原因（并非每个人都对此解释）。

以下可能对他人有用，因为它包括大多数示例没有在线的条件：

-- First create your temp table 
SELECT CONVERT(DATE,a.ins_timestamp) AS 'Date',  
a.Prod_code, 
a.Curr_boxes, 
a.Label_barcode, 
b.From_ord_no, 
NULL AS To_ord_no, 
CASE
    WHEN a.From_batch >= a.To_batch THEN a.From_batch 
    WHEN a.To_batch >= a.From_batch THEN a.To_batch 
    ELSE                             a.From_batch
END AS 'Batch',  
a.Weight, 
'IN' AS 'Direction' 

INTO #newtable 

FROM a 

JOIN b ON a.Label_barcode = b.Label_barcode 

WHERE (a.ins_timestamp Between ? And ?) AND (a.To_batch = ?) AND (a.From_batch = 0) AND (a.Type='Consumption') AND (a.To_status<>'STOCK') AND (b.From_status = 'PORDER') 

-- Now we insert the second query into the already created table
    INSERT INTO #newtable 

SELECT CONVERT(DATE,b.ins_timestamp) AS 'Date',  
b.Prod_code, 
b.Curr_boxes, 
b.Label_barcode, 
NULL AS From_ord_no, 
NULL AS To_ord_no, 
CASE
    WHEN b.From_batch >= b.To_batch THEN b.From_batch 
    WHEN b.To_batch >= b.From_batch THEN b.To_batch 
    ELSE                             b.From_batch
END AS 'Batch',  
b.Weight, 
'IN' AS 'Direction' 

FROM b 

WHERE (b.From_batch = 0) AND (b.Type='Consumption') AND (b.ins_timestamp Between ? And ?) AND (b.To_batch = ?) AND (b.To_status<>'STOCK') 

-- Now we can select whatever we want from our temp table
SELECT Date,  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    max(From_ord_no) From_ord_no, 
    To_ord_no, 
    Batch,  
    Weight, 
    Direction 

FROM #newtable 

GROUP BY Date,  
    Prod_code, 
    Curr_boxes, 
    Label_barcode, 
    To_ord_no, 
    Batch,  
    Weight, 
    Direction