Access数据库,有二个表:表A、表B
--------------------------------------
表A是记录了一个类别,表A结构:
id clsname
数值 字符串表B结构:(其中clsid是类别,与表A中的ID相关联)
clsid title inout money
数值型 字符串 逻辑 货币
--------------------------------------
现在要产生一个新的查询,得到下列字段:
clsid,clsname,intotal,outtotal
就是对表B中按类别clsid进行分组,并把inout为true时的money加到intotal中,inout为false为false时的money加到outtotal
举例如下:
表A:
1 方便面
2 可乐
表B:
clsid title inout money
1 XX true 15.2
2 XX false 2.6
1 XX false 3.5
2 XX false 6.1
1 XX true 4.0
那么结果就得到:
clsid,clsname,intotal,outtotal
1 方便面 19.2 3.5
2 可乐 0 8.7
是否可以通过一个SQL语句来实现,或者多条,但效率优先。
--------------------------------------
表A是记录了一个类别,表A结构:
id clsname
数值 字符串表B结构:(其中clsid是类别,与表A中的ID相关联)
clsid title inout money
数值型 字符串 逻辑 货币
--------------------------------------
现在要产生一个新的查询,得到下列字段:
clsid,clsname,intotal,outtotal
就是对表B中按类别clsid进行分组,并把inout为true时的money加到intotal中,inout为false为false时的money加到outtotal
举例如下:
表A:
1 方便面
2 可乐
表B:
clsid title inout money
1 XX true 15.2
2 XX false 2.6
1 XX false 3.5
2 XX false 6.1
1 XX true 4.0
那么结果就得到:
clsid,clsname,intotal,outtotal
1 方便面 19.2 3.5
2 可乐 0 8.7
是否可以通过一个SQL语句来实现,或者多条,但效率优先。
FROM A
JOIN (SELECT SUM(money) AS tal,clsid,inout FROM B WHERE inout = 1 GROUP BY clsid) C
ON A.ID = C.CLSID
JOIN (SELECT SUM(money) AS tal,clsid,inout FROM B WHERE inout = 0 GROUP BY clsid) D
ON A.ID = D.CLSID
SELECT C.clsid,clsname,C.tal AS intotal,D.tal AS outtotal
FROM A
JOIN (SELECT SUM(money) AS tal,clsid FROM B WHERE inout = 1 GROUP BY clsid) C
ON A.ID = C.CLSID
JOIN (SELECT SUM(money) AS tal,clsid FROM B WHERE inout = 0 GROUP BY clsid) D
ON A.ID = D.CLSID
(select isnull(sum(money),0) where B.clsid = A.ID and inout = true) as intotal,
(select isnull(sum(money),0) where B.clsid = A.ID and inout = False) as outtotal
from A
select clsid,clsname,
(select isnull(sum(money),0) from B where B.clsid = A.ID and inout = true) as intotal,
(select isnull(sum(money),0) from B where B.clsid = A.ID and inout = False) as outtotal
from A
2 可乐 0 8.7
中的一个0处理不好,就是在表B中如果没有可乐的inout为真的值,这里应该有一个0.
FROM A;
(select nz(sum(money),0) from B where B.clsid = A.ID and inout = true) AS intotal,
(select nz(sum(money),0) from B where B.clsid = A.ID and inout = False) AS outtotal
FROM A;