我有兩張表tbl_Enquiry_Items，tbl_Estimation_Items分別包含查詢和估計
我正在Tbl_Estimation_Items使用查詢獲取資料
Select * from tbl_Estimation_Items Where Estimation_Id= 1
回傳資料集 5 條記錄。我怎樣才能在tbl_Enquiry沒有重復的情況下獲得第 6 行。
他們之間有任何外鍵..有其中包含的另一個表Enquiry_id即Tbl_Estimation
我試過的查詢是
DECLare @EnquiryId int
Select @EnquiryId= Enquiry_Id from tbl_Estimation where Estimation_Id= 1
Select Estimation_Id, Rfx_Item_No , Material_No ,RTRIM( LTRIM(Description)) as Description , Quantity,Principal_Unit_Price, Total,Li_Discount_Percent,
Li_Discount_Amount ,Li_Principal_Total,Li_Principal_Currency,Li_Petromar_Unit_Price, Li_Petromar_Total,Li_Exchange_Currency,Li_Exchange_Rate,
Li_Petromar_Unit_Price_Fc,Li_Petromar_Total_Fc
into #temp from(
Select a.Estimation_Id, a.Rfx_Item_No , a.Material_No ,a.Description , a.Quantity,a.Principal_Unit_Price, a.Total,a.Li_Discount_Percent,
a.Li_Discount_Amount ,a.Li_Principal_Total,a.Li_Principal_Currency,a.Li_Petromar_Unit_Price, a.Li_Petromar_Total,a.Li_Exchange_Currency,a.Li_Exchange_Rate,
a.Li_Petromar_Unit_Price_Fc,a.Li_Petromar_Total_Fc
from tbl_Estimation_Items a
left outer join tbl_Estimation b on a.Estimation_Id = b.Estimation_Id
left outer join tbl_Enquiry c on b.Enquiry_Id = c.Enquiry_Id
where a.Estimation_Id= 1
Union
Select 1 as Estimation_Id, Rfx_Item_No , Material_No , Description ,
0.00 as Quantity, 0.00 as Principal_Unit_Price, 0.00 as Total, 0.00 as Li_Discount_Percent,
0.00 as Li_Discount_Amount ,0.00 as Li_Principal_Total, 0.00 as Li_Principal_Currency,0.00 as Li_Petromar_Unit_Price, 0.00 as Li_Petromar_Total,0.00 as Li_Exchange_Currency, 0.00 as Li_Exchange_Rate,
0.00 as Li_Petromar_Unit_Price_Fc,0.00 as Li_Petromar_Total_Fc
from
tbl_Enquiry_Items where Enquiry_Id =@EnquiryId
) tt
select *from #temp
drop table #temp
但給了我以下結果集
uj5u.com熱心網友回復：
您可以使用UNION，但如果您選擇 中的所有列tbl_Estimation_Items，則需要完成 中不存在的列的值tbl_Enquiry_Items。或者您也可以只選擇兩個表中存在的列。這里選擇所有列：
SELECT
*
FROM
tbl_Estimation_Items WHERE Estimation_Id=1
UNION ALL
SELECT
Enquiry_Item_Id AS Estimation_Item_id,
Estimation_Id,
Rfx_Item_No,
Material_No,
Description,
NULL as Quantity,
NULL as Principal_Unit_Price
-- Keep adding the rest of the columns with null or assign a value
FROM
tbl_Enquiry_Items WHERE Enquiry_Item_Id = 911
uj5u.com熱心網友回復：
您要實作的目標尚不完全清楚。
有問詢。一個查詢可以有許多估計。查詢可以涵蓋比其估計更多的專案。您的資料庫還允許估算包含不屬于查詢的專案。
您可能正在尋找的是外連接。下面顯示了兩個專案表中的所有行，無論它們在另一個表中是否匹配：
select *
from tbl_estimation est
join tbl_estimation_items esti on esti.estimation_id = est.estimation_id
full outer join tbl_enquiry_items enqi on enqi.enquiry_id = est.enquiry_i
and enqi.rfx_item_no = esti.rfx_item_no
如果您想要特定估計的資料，則更改
from tbl_estimation est
到
from (select * from tbl_estimation where estimation_id = 1) est
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/323665.html
標籤：sql sql-server