现有表A、B,
A[a1,a2,a3] a1列与a2列均为字符串类型,a3列为整型
B[b1,b2,b3] b1列与b2列均为字符串类型,b3列为整型
在下面的语句中,我欲按以下顺序将数据显示在DBGrid中
[a1,a2,sum(a3),sum(b3),sum(a3)-sum(b3)]
sum(b3)为a1,a2等于b1,b2时的数值,sum(a3)与sum(b3)均为参照下面语句得出的数值
请问该SQL语句怎么写?
with ADOQuery1 do
begin
Close;
SQL.Clear;
sSql := 'SELECT a1,a2,sum(a3) FROM A GROUP BY a1,a2';
SQL.Add(sSql);
Open
end;
with ADOQuery2 do
begin
Close;
SQL.Clear;
sSql := 'SELECT b1,b2,sum(b3) FROM B GROUP BY b1,b2';
SQL.Add(sSql);
Open
end;
A[a1,a2,a3] a1列与a2列均为字符串类型,a3列为整型
B[b1,b2,b3] b1列与b2列均为字符串类型,b3列为整型
在下面的语句中,我欲按以下顺序将数据显示在DBGrid中
[a1,a2,sum(a3),sum(b3),sum(a3)-sum(b3)]
sum(b3)为a1,a2等于b1,b2时的数值,sum(a3)与sum(b3)均为参照下面语句得出的数值
请问该SQL语句怎么写?
with ADOQuery1 do
begin
Close;
SQL.Clear;
sSql := 'SELECT a1,a2,sum(a3) FROM A GROUP BY a1,a2';
SQL.Add(sSql);
Open
end;
with ADOQuery2 do
begin
Close;
SQL.Clear;
sSql := 'SELECT b1,b2,sum(b3) FROM B GROUP BY b1,b2';
SQL.Add(sSql);
Open
end;
group by a.a1,a.a2
不过修改一下会更好
select a.a1,a,a2,sum(a,a3) as a3,sum(a.a3)-sum(b.b3) as fieldname where a.a1=b.b1 and a.a2=b.b2
group by a.a1,a.a2
SELECT A.a1,A,a2,sum(A.a3) AS suma3,sum(B.b3) AS sumb3,sum(A.a3)-sum(b.b3) AS subab FROM A,B where (A.a1=B.b1) and (A.a2=B.b2) group by A.a1,A.a2当A表或者B表中的a1,a2或者b1,b2有两行或者两行以上数据相同时,sum(a3)或者sum(b3)就会扩大两倍或者两倍以上flp(会说话的哑巴)和 czzw(山水)的方法中,缺了FROM A,B也不能正确运行我需要得到的结果:是分别从下面的表达式中得出的结果集中求出当a1,a2等b1,b2时将[a1,a2,sum(a3),sum(b3),sum(a3)-sum(b3)]置于同一行我前面没有把表结构写完整,完整得表结构是下面这样的
A[no,a1,a2,a3] no为主键,仅仅作为一个无重复序号,a1列与a2列均为字符串类型,a3列为整型
B[no,b1,b2,b3] no为主键,仅仅作为一个无重复序号,b1列与b2列均为字符串类型,b3列为整型
B是A的子集 with ADOQuery1 do
begin
Close;
SQL.Clear;
sSql := 'SELECT a1,a2,sum(a3) FROM A GROUP BY a1,a2';
SQL.Add(sSql);
Open
end;
with ADOQuery2 do
begin
Close;
SQL.Clear;
sSql := 'SELECT b1,b2,sum(b3) FROM B GROUP BY b1,b2';
SQL.Add(sSql);
Open
end;