我有7个具有以下结构的表:
tbl_Trucks | tbl_Driver | tbl_Clients
-----------------------|-----------------------------|--------------------
tr_ID - int | dr_ID - int | cl_ID - int
tr_Name - varchar(50) | dr_LName - varchar(50) | cl_Name - varchar(50)
| dr_FName - varchar(50) |
| dr_MName - varchar(50) |
tbl_ExpenseHead | tbl_ExpenseDiesels
---------------------------------|---------------------------
eh_ID - int | dsl_ID - int
eh_DateAdded - date | dsl_amt - float
eh_RouteStart - varchar(50) | dsl_Ltrs - float
eh_RouteEnd - varchar(50) | eh_ID - int
cl_ID - int |
dr_ID - int |
tr_ID - int |
eh_Status - varchar(50) |
eh_ERnumber - varchar(50) |
eh_InvoiceNumber - varchar(50) |
tbl_ExpenseTotal | tbl_Helpers
---------------------------------|---------------------------
tot_ID - int | help_ID - int
tot_OverallExpense- date | help_FName - varchar(50)
eh_ID - int | help_MName - varchar(50)
| help_LName - varchar(50)
| eh_ID - int
目前有这个查询
SELECT
h.eh_DateAdded as [TRIP_DATE],
t.tr_Name as [TRUCK_NAME],
d.dr_LName + ', ' + d.dr_FName + ' ' + d.dr_MName as DRIVER,
c.cl_Name as CLIENT,
h.eh_RouteStart + ' to ' + h.eh_RouteEnd as TRIP,
h.eh_InvoiceNumber as [INVOICE_NUMBER],
h.eh_ERnumber as [ER_NUMBER],
SUM(dsl.dsl_amt) as [DIESEL_AMOUNT],
SUM(dsl.dsl_Ltrs) as [DIESEL_LITERS],
tot.tot_OverallExpense as EXPENSE
FROM tbl_ExpenseHead h INNER JOIN
tbl_Trucks t ON h.tr_ID = t.tr_ID INNER JOIN
tbl_Driver d ON h.dr_ID = d.dr_ID INNER JOIN
tbl_Clients c ON h.cl_ID = c.cl_ID INNER JOIN
tbl_ExpDiesels dsl ON h.eh_ID = dsl.eh_ID INNER JOIN
tbl_ExpenseTotal tot ON h.eh_ID = tot.eh_ID
WHERE h.eh_Status = 'APPROVED'
GROUP BY
h.eh_DateAdded,
t.tr_Name,
d.dr_LName,
d.dr_FName,
d.dr_MName,
c.cl_Name,
h.eh_RouteStart,
h.eh_RouteEnd,
h.eh_InvoiceNumber,
h.eh_ERnumber,
dsl.dsl_amt,
dsl.dsl_Ltrs,
tot.tot_OverallExpense
输出这样的表
如您所见,助手不在表中。鉴于我有这样的值填充tbl_Helpers
我想要第一张桌子变成这样的东西
我想显示每行的帮助程序,如果碰巧少于2个帮助程序,则将其留为空白..或NULL。我在这里尝试一些代码,我想知道是否有解决此问题的快速方法。辅助对象不会超过2,因为我限制用户这样做。
我尝试在tbl_Helpers中使用DISTINCT来获取每个不同的帮助程序,并使用其eh_ID对其进行过滤,但我不知道如何将它们附加以使其像第三张表一样
您的第一个问题是您无法确定哪个助手应该是#1,哪个应该是#2。所以首先让我们给他们分配一个数字。接下来,让我们将它们加入您的ExpenseHead
:
WITH myHelpers AS (
SELECT eh_ID
, help_ID
, RANK() OVER (PARTITION BY eh_IDORDER BY help_ID) helperOrder
FROM tbl_Helpers
)
SELECT h.eh_ID
, hlp1.help_ID AS help1_ID
, hlp2.help_ID AS help2_ID
FROM tbl_ExpenseHead h LEFT JOIN
myHelpers hlp2 ON h.eh_ID = hlp2.eh_ID AND hlp2.helperOrder = 2 LEFT JOIN
myHelpers hlp1 ON h.eh_ID = hlp1.eh_ID AND hlp1.helperOrder = 1 AND hlp2.help_ID IS NOT NULL
可以将WITH
-statement视为查询期间存在的临时表,称为- CTE
。
注意使用,LEFT JOIN
因为我们不知道是否有2个助手。
请注意,在情况下hlp2.help_ID IS NOT NULL
,我仅选择hlp1 ,它是2个帮助器,或者都不是。
根据您的示例,我认为您可以将其应用于查询。祝你好运!
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句