如何从SQL查询中的两个不同表中获取不同的数据？

Question

我有两个表，名为Soft和Web，表中包含多个数据，其中的数据与我想要的数据不同。代表：

在包含5个数据的软表中，即

​

​

也是在包含5个数据的Web表中。

​

​

现在我要输出。

​

​

我做过查询，但不幸的是没有成功，让我们看看我的查询。

SELECT DISTINCT soft.GSTNo AS SoftGST
    ,web.GSTNo AS WebGST
    ,soft.InvoiceNumber AS SoftInvoice
    ,web.InvoiceNumber AS WebInvoice
    ,soft.Rate AS SoftRate
    ,web.Rate AS WebRate
FROM soft
LEFT OUTER JOIN web ON web.GstNo = soft.GSTNo
    AND web.InvoiceNumber = soft.invoicenumber
    AND web.rate = soft.rate

此外，我申请内部加入同样的东西没有工作。

Answer 1

你可以通过

;WITH cte_soft AS
    (SELECT * FROM soft 
        EXCEPT 
        SELECT * FROM web) 
    ,cte_web AS
    (SELECT * FROM web
        EXCEPT 
        SELECT * FROM soft)
SELECT *
FROM
  (SELECT gst softgst, NULL webgst, invoice softinvoice, NULL webinvoice, rate softrate, NULL webrate
   FROM cte_soft
   UNION ALL 
   SELECT NULL, gst, NULL, invoice, NULL , rate
   FROM cte_web) tbl
ORDER BY coalesce(softgst, webgst),coalesce(softinvoice,webinvoice)

小提琴

Answer 2

您可以使用full join

SELECT s.gst as softgst, w.gst as webgst,
       s.invoice as softinvoice, w.invoice as webinvoice,
       s.rate as softrate, w.rate as webrate
FROM soft s FULL JOIN
     web w
     ON s.gst = w.gst AND s.invoice = w.invoice AND s.rate = w.rate
WHERE s.gst IS NULL OR w.gst IS NULL
ORDER BY COALESCE(s.gst, w.gst), COALESCE(s.invoice, w.invoice);

不需要子查询，需要CTE。这实际上只是查询的一个细微的变体。