UsedID在多表组中的SQL计数记录

Question

我有一些表格。它包含不同的usedId作为外键。一个用户有多个表单。现在我想看到一个用户提交了多少个表单。

我要输出：

登录Id，FormName，NumberOfForms

沙克尔-> BurnOut -> 10

沙克尔-> CIP -> 8

沙克尔->加热-> 20

但一开始我需要输出。然后装饰。

我在尝试多种方法来解决这个问题。

select  COUNT(*) as NumberOfForms  from Client_BurnOuts  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
union all 

select COUNT(*) as NumberOfForms  from Client_EmergencyAssistances  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
union all 

select COUNT(*) as NumberOfForms  from Client_DukeEnergyFoundations  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
union all
 
select COUNT(*) as NumberOfForms  from Client_CIPEnergyCrisises  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
;

另一种方式

SELECT l.LoginID, 
COUNT(cb.PatientApplicationId) as BurnOuts  ,
COUNT(ea.PatientApplicationId) as EmergencyAssistances, 
COUNT(du.PatientApplicationId) as DukeEnergyFoundations, 
COUNT(cip.PatientApplicationId) as CIP
FROM PatientPortalLogins l

inner join Client_CIPEnergyCrisises cip ON cip.PatientApplicationId = l.PatientApplicationId
inner join Client_DukeEnergyFoundations du ON du.PatientApplicationId = l.PatientApplicationId 
inner join Client_EmergencyAssistances ea ON ea.PatientApplicationId = l.PatientApplicationId 
inner join Client_BurnOuts cb ON cb.PatientApplicationId = l.PatientApplicationId 
where l.PatientApplicationId = 10
GROUP BY l.LoginID, cip.PatientApplicationId, cb.PatientApplicationId;

但我无法得到正确的结果。我一共有五个表，PatientPortalLogins表只包含用户名，usedId.四个表包含不同的值，

我只想看看哪个用户提交了多少页。示例used1提交2个Client_CIPEnergyCrisises表单、1个EmergencyAssistances表单、5个DukeEnergyFoundations表单

Answer 1

最后，我可以解决我的问题。如果您有多个表，那么我们可以使用它们。

select l.LoginID, 'Client_BurnOuts' as Client_BurnOuts,  COUNT(*) as NumberOfForms  
from Client_BurnOuts  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
GROUP BY l.LoginID
union all 

select l.LoginID, 'Client_EmergencyAssistances' as Client_EmergencyAssistances,  COUNT(*) as NumberOfForms  
from Client_EmergencyAssistances  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
GROUP BY l.LoginID
union all 

select l.LoginID, 'Client_DukeEnergyFoundations' as Client_DukeEnergyFoundations, COUNT(*) as NumberOfForms  
from Client_DukeEnergyFoundations  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
GROUP BY l.LoginID
union all 

select l.LoginID, 'Client_CIPEnergyCrisises' as Client_CIPEnergyCrisises, COUNT(*) as NumberOfForms  
from Client_CIPEnergyCrisises  b
inner join PatientPortalLogins l on l.PatientApplicationId = b.PatientApplicationId
where l.PatientApplicationId = 10
GROUP BY l.LoginID
;