Question

目前,我有一套储存的程序,可以恢复数据,并在我的报告观众中展示,但我的问题是,检查学生是否上课,以及是否上课。

COALESCE(A.Attended, 0)AS Attended

如果他们出席,如果他们不参加,我的报告仅显示1或0,即使他们能够参加过一次以上的会议。怎么能够把这一点推到一旁,以达到正确的目标。

感谢

WHOLE questRY:

SELECT 
    P.PartyId,
    COUNT(COALESCE(A.Attended, 0))AS Attended,
    COUNT(DISTINCT H.HearingId) AS Hearings,
    O.OfficeName As OfficeName,
    CO.Name,
    P.FirstName AS FirstName,
    P.LastName AS LastName,
    P.BirthDate AS DOB
FROM Activity A 
INNER JOIN ActivityType AT On A.ActivityTypeId = AT.ActivityTypeId
INNER JOIN ActivityEntry AE ON A.ActivityEntryId = AE.ActivityEntryId
INNER JOIN HearingEntry HE ON CAE.HearingEntryId = HE.HearingEntryId
INNER JOIN Hearing H ON HE.HearingEntryId = H.HearingEntryId
INNER JOIN [Case] C ON H.CaseId = C.CaseId
INNER JOIN CaseOffice CO ON C.CaseId = CO.CaseId AND AE.OfficeId = CO.OfficeId
INNER JOIN Office O ON CO.OfficeId = O.OfficeId
INNER JOIN Attended A ON H.HearingId = A.HearingId
INNER JOIN Party P ON A.PartyId = P.PartyId
WHERE HP.PartyId = P.PartyId AND AE.OfficeId = @OfficeId AND(H.HearingDate >= @BeginDate AND (H.HearingDate <= @EndDate OR H.HearingDate IS NULL)) AND HE.HearingEntryId = CAE.HearingEntryId
GROUP BY P.PartyId, A.Attended, O.OfficeName,CO.Name,P.FirstName, P.LastName,P.BirthDate

Answer 1

你们需要参加论坛。

SELECT 
    P.PartyId,
    (SUM(COALESCE(A.Attended, 0)))AS Attended,
    COUNT(DISTINCT H.HearingId) AS Hearings,
    O.OfficeName As OfficeName,
    CO.Name,
    P.FirstName AS FirstName,
    P.LastName AS LastName,
    P.BirthDate AS DOB
FROM Activity A 
INNER JOIN ActivityType AT On A.ActivityTypeId = AT.ActivityTypeId
INNER JOIN ActivityEntry AE ON A.ActivityEntryId = AE.ActivityEntryId
INNER JOIN HearingEntry HE ON CAE.HearingEntryId = HE.HearingEntryId
INNER JOIN Hearing H ON HE.HearingEntryId = H.HearingEntryId
INNER JOIN [Case] C ON H.CaseId = C.CaseId
INNER JOIN CaseOffice CO ON C.CaseId = CO.CaseId AND AE.OfficeId = CO.OfficeId
INNER JOIN Office O ON CO.OfficeId = O.OfficeId
INNER JOIN Attended A ON H.HearingId = A.HearingId
INNER JOIN Party P ON A.PartyId = P.PartyId
WHERE HP.PartyId = P.PartyId AND AE.OfficeId = @OfficeId AND(H.HearingDate >= @BeginDate AND (H.HearingDate <= @EndDate OR H.HearingDate IS NULL)) AND HE.HearingEntryId = CAE.HearingEntryId
GROUP BY P.PartyId, O.OfficeName,CO.Name,P.FirstName, P.LastName,P.BirthDate

Answer 2

我猜测你指的是A.Attented是一比,你希望它成为一纸空子,以便你能够随后集中起来?

你们可以这样说:

CAST(A.Attented AS INT)

或者在这种情况下:

COALESCE(CAST(A.Attended AS INT), 0) AS Attended

友情链接