根據一列中的值洗掉重復行

我希望第一個查詢的結果覆寫第二個查詢的結果：

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”列回傳空值，但 select 陳述句中的其他所有內容都將完全相同。在這種情況下，聯合將無法洗掉重復的行，因此同一 label_barcode 將有兩行資料。

期望的輸出：

所以問題是 - 如何從第二個查詢中洗掉結果，顯示 From_order_no 的空值并保留第一個查詢的結果？

uj5u.com熱心網友回復：

提供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'

uj5u.com熱心網友回復：

您可以創建一個臨時表。在下面的示例中，這稱為#newtable。標簽很重要，因為這實際上是使它成為“臨時”表的原因（不是每個人都解釋這一點）。

以下內容可能對其他人有用，因為它包含大多數示例在線沒有的 WHERE 條件：

-- 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 

標籤：sql 加入 选择 联盟 清楚的

上一篇：pyspark.sql.utils.AnalysisException：列不明確但沒有重復的列名

下一篇：為什么JSON.stringify()\u30FC要ー