如何強制顯示第一個表中的數據
數據庫: 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
與bproject
ordproject
,以便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
但
*
無論如何,在生產中使用都不是一個好主意。因此,如果您明確指定輸出列,則這兩個選項之間應該沒有任何區別,特別是如果您考慮到右連接在開發人員中也不是很流行的話。