SQL查询-join得到以下结果

问题描述：

我有2表，从该表2的结果，我需要得到第三个表，我的第一台查询结果如下SQL查询-join得到以下结果

select categeoryid,EmployeeId,SUM(Amount)as advance 
from ExpenditureDetails where Expenditurefor='Travel' and EmployeeId='TFIPL201145' 
group by categeoryid,EmployeeId 

categeoryid   EmployeeId   advance 
---------------------------------------------------- 
Local_IPL    TFIPL201145   1000 
Project_Audio release TFIPL201145   566565 
Project_Cricketscore TFIPL201145   441898 
Project_mayajal  TFIPL201145   1100 
Project_Tennis   TFIPL201145   8949

第二个表的结果遵循

select categeoryid,EmployeeId,SUM(Amount)as submitted_amt 
from ExpenditureTravelling where EmployeeId='TFIPL201145' 
group by categeoryid,EmployeeId 

categeoryid   EmployeeId  submitted_amt 
------------------------------------------------- 
Event_Golf   TFIPL201145 36456 
Events_Hokey   TFIPL201145 1303 
local    TFIPL201145 25000 
Local_IPL   TFIPL201145 1169488 
Project    TFIPL201145 16000 
Project_Cricketscore TFIPL201145 216661 
Project_harris  TFIPL201145 53453 
Project_mayajal  TFIPL201145 1500 
Project_Tennis  TFIPL201145 8949

我希望我的结果表应该是...加入2表

categeoryid    EmployeeId  advance  submitted_amt 
---------------------------------------------------------------------- 
Event_Golf    TFIPL201145  0   36456 
Events_Hokey    TFIPL201145  0   1303 
local      TFIPL201145  0   25000 
Local_IPL     TFIPL201145  1000   1169488 
Project     TFIPL201145  0   16000 
Project_Cricketscore  TFIPL201145  441898  216661 
Project_harris   TFIPL201145  0   53453 
Project_mayajal   TFIPL201145  1100   1500 
Project_Tennis   TFIPL201145  8949   8949

u能plz帮助此查询...获得结果...

你能否试着将问题的格式设置得更具可读性？现在要比构建SQL更有效地解决问题。 – 2012-03-24 07:07:35

嗨，joachim Isaksson，现在你可以阅读我的查询了吗？ – user1285783 2012-03-24 07:24:21

答

您必须使用它们之间的相对链接来加入两个查询。据我所知，EmployeeId是一个很好的候选人。类似于：

select categoryId, EmployeeId, SUM(Amount) as advance, Travelling.submitted_amt 
from ExpenditureDetails inner join 
(select CategoryId, EmployeeId, sum(Amount) as submitted_amt 
    from ExpenditureTravelling) as Travelling on 
ExpenditureDetails.EmployeeId = Travelling.EmployeeId 
where 
    (EmployeeId='TFIPL201145') group by....

希望这会有所帮助。

答

选择a.categoryId，a.EmployeeId，ISNULL（总和（a.Amount），0）作为submitted_amt， ISNULL（（选择从ExpenditureDetails b总和（b.amount）其中b.categoryId = a.categoryId和b.Expenditurefor = '旅行' 组由b.categeoryid，b.EmployeeId）），0）作为预先从ExpenditureDetails一个其中a.EmployeeId = 'TFIPL201145'
组由a.categeoryid，a.EmployeeId

SQL查询-join得到以下结果

相关推荐