基本上,我希望查询1给我查询2的结果。我需要使用查询1 b / c。我还有其他子查询可添加要计算或获取最大日期的位置。
我ClientVisit.client_id
在查询1中尝试了“分组依据” ,但出现此错误'ClientVisit.clientvisit_id'
,因为选择列中的列未包含在聚合函数或GROUP BY子句中,所以该列无效。ClientVisit.clientvisit_id
不在选择列表中。
查询1
SELECT ClientVisit.client_id ,
( SELECT COUNT( ZFORM_group_note_28.clientvisit_id )
FROM ZFORM_group_note_28
WHERE ZFORM_group_note_28.successful_day_752410 = 'Yes'
AND ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id ) AS Successful_Day
FROM ClientVisit
WHERE ClientVisit.visittype_id = 16
AND ClientVisit.program_id = 5
AND ClientVisit.rev_timein >= @param1
AND ClientVisit.rev_timeout < DATEADD( DAY ,
1 ,
@param2 )
查询2
SELECT ClientVisit.client_id ,
COUNT( ZFORM_group_note_28.clientvisit_id ) AS Successful_Day
FROM ClientVisit
INNER JOIN ZFORM_group_note_28
ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
WHERE ClientVisit.visittype_id = 16
AND ClientVisit.program_id = 5
AND ZFORM_group_note_28.successful_day_752410 = 'Yes'
AND ClientVisit.rev_timein >= @param1
AND ClientVisit.rev_timeout < DATEADD( DAY ,
1 ,
@param2 )
GROUP BY ClientVisit.client_id
结果:查询1
client_id successful_day
1182 1
1182 1
1379 1
1379 1
1379 1
1379 1
查询2
client_id successful_day
1182 2
1379 9
1523 2
1757 1
也许这会有所帮助。(通过使用case语句修改了查询)
select ClientVisit.client_id, sum(Successful_Day) as Successful_Day,Sum(Unsuccessful_Day) as Unsuccessful_Day
from
(
SELECT ClientVisit.client_id ,
Case when ZFORM_group_note_28.successful_day_752410 = 'Yes' then COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Successful_Day,
Case when ZFORM_group_note_28.successful_day_752410 = 'No' then COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Unsuccessful_Day
FROM ClientVisit
INNER JOIN ZFORM_group_note_28
ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
WHERE ClientVisit.visittype_id = 16
AND ClientVisit.program_id = 5
AND ClientVisit.rev_timein >= @param1
AND ClientVisit.rev_timeout < DATEADD( DAY , 1 , param2 )
GROUP BY ClientVisit.client_id
)Clients
Group by ClientVisit.client_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句