select CARDTYPE.F001 ,count(*),sum(MONEYS.F003)
from CUSTOM inner join MONEYS on CUSTOM.F001=MONEYS.F001
inner join CARDTYPE on Custom.F001=CARDTYPE.F001
Group by CARDTYPE.F001 select CARDTYPE.F001 ,CARDTYPE.F002,sum(MONEYS.F003)
from CUSTOM inner join MONEYS on CUSTOM.F001=MONEYS.F001
where MONEYS.F004='存'
Group by CARDTYPE.F001 ,CARDTYPE.F002
from CUSTOM inner join MONEYS on CUSTOM.F001=MONEYS.F001
inner join CARDTYPE on Custom.F001=CARDTYPE.F001
Group by CARDTYPE.F001 select CARDTYPE.F001 ,CARDTYPE.F002,sum(MONEYS.F003)
from CUSTOM inner join MONEYS on CUSTOM.F001=MONEYS.F001
where MONEYS.F004='存'
Group by CARDTYPE.F001 ,CARDTYPE.F002
SELECT CUSTOM.F003 AS 信用卡类型, Count(CUSTOM.F002) AS 用户数, Sum(MONEYS.F003 * iif(MONEYS.F004 = '存', 1, -1)) AS 累计金额
FROM CUSTOM INNER JOIN MONEYS ON CUSTOM.F001 = MONEYS.F001
GROUP BY CUSTOM.F003;
第二个要求与数据举例不符,按照数据举例查询
SELECT CUSTOM.F001 AS 卡号, CUSTOM.F002 AS 储户姓名, Sum(MONEYS.F003 * iif(MONEYS.F004 = '存', 1, -1)) AS 存款金额
FROM CUSTOM INNER JOIN MONEYS ON CUSTOM.F001 = MONEYS.F001
GROUP BY CUSTOM.F001, CUSTOM.F002;
这条语句出错,提示: 实时错误'3122' 试图执行的查询中不包含作为合计函数一部分的特定表达式'储户姓名'
应该是CUSTOM.F002 AS 储户姓名 这部分有错误,找不出什么原因.
但第一条
SELECT CUSTOM.F003 AS 信用卡类型, Count(CUSTOM.F002) AS 用户数, Sum(MONEYS.F003 * iif(MONEYS.F004 = '存', 1, -1)) AS 累计金额
FROM CUSTOM INNER JOIN MONEYS ON CUSTOM.F001 = MONEYS.F001
GROUP BY CUSTOM.F003;
会出现这样的统计错误:
Count(CUSTOM.F002) AS 用户数 这个统计出来的是MONEYS的存取记录数,例如A类型的信用卡,有一个用户,该用户"存"了500,"取"了300,但统计出来的"用户数"=2 正确的应该是"用户数"=1
FROM (SELECT CUSTOM.F003 AS 信用卡类型, Count(CUSTOM.F002) AS 用户数
FROM CUSTOM
GROUP BY CUSTOM.F003
) A
LEFT JOIN
(SELECT CUSTOM.F003 AS 信用卡类型, Sum(MONEYS.F003*IIf(MONEYS.F004='存',1,-1)) AS 累计金额
FROM CUSTOM
INNER JOIN MONEYS ON CUSTOM.F001=MONEYS.F001
GROUP BY CUSTOM.F003
) B ON B.信用卡类型 = A.信用卡类型;
初学 SQL 还是建议用 SQL Server,T-SQL 的语法帮助文档中可以查到,Access 的帮助实在烂的可以。