多表联查统计条数的SQL如何写？

Question

举例如下，查询一个关联A,B,C三个表的数据，统计满足下面条件的总和（比如 count,sum），如何写？

SELECT 
A.dateNo + A.szNo AS SZId,
'1' AS difCode,
B.userId + B.shop AS userInfo,
C.total * 10 / 100 AS Account
FROM rj_Table A INNER JOIN sz_Table B ON A.dateNo = B.dateNo AND A.szId = B.zsId
INNER JOIN ms_Table C ON A.dateNo = C.dateNo AND a.szId = C.szId.
WHERE data >= '2024-1-1'

Answer 1

SELECT 
    A.dateNo + A.szNo AS SZId,
    '1' AS difCode,
    B.userId + B.shop AS userInfo,
    (C.total * 10 / 100) AS Account,
    COUNT(*) AS totalCount,
    SUM(C.total) AS totalSum
FROM 
    rj_Table A 
INNER JOIN 
    sz_Table B ON A.dateNo = B.dateNo AND A.szId = B.zsId
INNER JOIN 
    ms_Table C ON A.dateNo = C.dateNo AND A.szId = C.szId
WHERE 
    A.dateNo >= '2024-01-01'
GROUP BY 
    SZId, difCode, userInfo, Account;