如何強制顯示第一個表中的數據

數據庫： SQL SEVER 2008

我正在加入多個表，我希望結果始終顯示“項目”表中的數據。在下面的範例中，我有一個客戶，其中計費和折扣數據位於單獨的表中，除了項目 ID 之外沒有太多共同點。我已經能夠接近我想要的，但是我無法從第一個表中獲取數據以顯示在結果行中。目前，我們從多個查詢中獲取這些數據，但我們正在嘗試將其合併為一個。

我嘗試更改聯接和聯合。如果可能的話，我還試圖避免使用 case 語句。

SQL Fiddle 上的範例數據 ： http ://sqlfiddle.com/#!18/ec1e7/8

詢問：

select * from project
   left join billable
       on project.pproject = billable.bproject
  full join discount 
       on billable.bindex = discount.dindex;

結果：

| pproject |    pcontact |                        pdesc | pclient | bindex | bproject | bbillhrs | bbillrt | bbillamt | binvoice | bbatch |  dindex | dproject |  drate | damount | dinvoice | dbatch |
|----------|-------------|------------------------------|---------|--------|----------|----------|---------|----------|----------|--------|---------|----------|--------|---------|----------|--------|
| 10-70005 | bill murray | this is the best description |      10 |   7319 | 10-70005 |      0.2 |      40 |        8 |      182 |   5704 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   7681 | 10-70005 |      1.1 |      50 |       55 |      182 |   7552 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3654 | 10-70005 |      0.3 |      40 |       12 |      182 |   9631 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3655 | 10-70005 |      0.5 |      40 |       20 |      182 |   9632 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |      0.2 |      50 |       10 |      182 |   2607 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4344762 | 10-70005 |      7 |      56 |      804 |   7875 |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4346345 | 10-70005 |      4 |      32 |      804 |   9633 |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4346678 | 10-70005 |      2 |      10 |      804 |   1450 |

這是我需要的結果：

| pproject |    pcontact |                        pdesc | pclient | bindex | bproject | bbillhrs | bbillrt | bbillamt | binvoice | bbatch |  dindex | dproject |  drate | damount | dinvoice | dbatch |
|----------|-------------|------------------------------|---------|--------|----------|----------|---------|----------|----------|--------|---------|----------|--------|---------|----------|--------|
| 10-70005 | bill murray | this is the best description |      10 |   7319 | 10-70005 |      0.2 |      40 |        8 |      182 |   5704 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   7681 | 10-70005 |      1.1 |      50 |       55 |      182 |   7552 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3654 | 10-70005 |      0.3 |      40 |       12 |      182 |   9631 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3655 | 10-70005 |      0.5 |      40 |       20 |      182 |   9632 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |      0.2 |      50 |       10 |      182 |   2607 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4344762 | 10-70005 |      7 |      56 |      804 |   7875 |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4346345 | 10-70005 |      4 |      32 |      804 |   9633 |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4346678 | 10-70005 |      2 |      10 |      804 |   1450 |

可以以這樣一種方式指定您的連接，即邏輯上連接billable和之間的連接discount首先發生。首先執行該連接將讓您將結果集外連接以project匹配後者pproject與bprojector dproject，以便project始終在輸出中填充行。

有兩種方法可以實現它。第一個選項使用嵌套連接語法：

SELECT
 *
FROM
 dbo.project AS p
 LEFT JOIN
   dbo.billable AS b
   FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
 ON p.pproject IN (b.bproject, d.dproject)
;

您可以選擇在完整連接周圍放置括號，以使讀者更清楚地了解意圖：

...
FROM
 dbo.project AS p
 LEFT JOIN
 (
   dbo.billable AS b
   FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
 )
 ON p.pproject IN (b.bproject, d.dproject)

然而，語法本身已經足夠明確了（無論如何對解析器而言）。

應該注意的是，嵌套連接語法並不是普遍流行的。如果您願意，還有一種不涉及嵌套的替代方法——右連接：

SELECT
 *
FROM
 dbo.billable AS b
 FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
 RIGHT JOIN dbo.project AS p ON p.pproject IN (b.bproject, d.dproject)
;

這實現了相同的連接邏輯順序：首先billable是與discount，然後是與 的連接結果project，這些project行位於連接的外側，即它們將始終出現在輸出中，與前面的查詢相同。

唯一的區別是，如果SELECT *在這兩種情況下都使用，輸出中列的順序將不一樣。列將根據查詢中提到的表的順序列出：project列、billable列、discount嵌套查詢輸出中的列，以及billable右連接一中的 , discount。project

但*無論如何，在生產中使用都不是一個好主意。因此，如果您明確指定輸出列，則這兩個選項之間應該沒有任何區別，特別是如果您考慮到右連接在開發人員中也不是很流行的話。

引用自：https://dba.stackexchange.com/questions/201816