Sql-Server

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

  • March 21, 2018

數據庫: 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匹配後者pprojectbprojector 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右連接一中的 , discountproject

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

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