好的 – 我看了看,发现了很多例子,但没有达到我的需要.也许我用了错误的词来搜索,但我可以使用你的帮助.我将提供尽可能详细的信息.
我需要生成一个报表,将两个表中的字段或视图和表合并到一个表中以用于报表.这是我正在使用的声明：
SELECT A.ConfInt,A.Conference,NULL as Ordered,NULL as Approved,NULL as PickedUp,SUM(dbo.Case_Visit_Payments.Qty) AS Qty
FROM dbo.Conferences as A INNER JOIN
dbo.Case_Table ON A.ConfInt = dbo.Case_Table.Conference_ID INNER JOIN
dbo.Case_Visit_Payments ON dbo.Case_Table.Case_ID = dbo.Case_Visit_Payments.Case_ID
WHERE (dbo.Case_Visit_Payments.Item_ID = 15 AND A.ProjectCool = 1)
GROUP BY A.Conference,A.ConfInt
UNION
SELECT B.ConfInt,B.Conference,SUM(dbo.Cool_Fan_Order.NumberOfFansRequested) AS Ordered,SUM(dbo.Cool_Fan_Order.Qty_Fans_Approved) AS Approved,SUM(dbo.Cool_Fan_Order.Qty_Fans_PickedUp) AS PickedUp,NULL AS Qty
FROM dbo.Conferences as B LEFT OUTER JOIN
dbo.Cool_Fan_Order ON B.ConfInt = dbo.Cool_Fan_Order.Conference_ID
where B.ProjectCool = 1
GROUP BY B.Conference,B.ConfInt
以下是结果：
4 Our Lady NULL NULL NULL 11 4 Our Lady 40 40 40 NULL 7 Holy Rosary 20 20 20 NULL 11 Little Flower NULL NULL NULL 21 11 Little Flower 5 5 20 NULL 19 Perpetual Help NULL NULL NULL 2 19 Perpetual Help 20 20 20 NULL
我更喜欢的是没有重复的行,例如：
4 Our Lady 40 40 40 11 7 Holy Rosary 20 20 20 NULL 11 Little Flower 5 5 20 21 19 Perpetual Help 20 20 20 2
我希望这个问题足够清楚.任何建议将不胜感激.而且我做了回答.
