有两条select语句,对应不同的表,查询不同的结果,分别显示在两个DBGrid中,现在我想将这两个结果显示在一个DBGrid中,大家有方法没?两语句分别如下:
procedure TForm1.Button4Click(Sender: TObject);
begin
with DM2.ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('Select Name as 库存名称,Sum(Num) as 入库数量 from Stocks');
SQL.Add('group by Name');
Open;
end;
end;
procedure TForm1.Button8Click(Sender: TObject);
begin
with DM2.ADOQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('Select Name as 出库名称,Sum(num) as 出库数量 from OutStocks');
SQL.Add('group by Name');
Open;
end;
end;想将这两条select语句的查询结果一起显示在一个DBGrid中~希望达人们帮帮忙~~(附这两表的结构: 库存表Stocks:
字段:ID(自动编号的,无需显示) InNo(库存编号) Name(库存名称) Num(库存数量) InDate(入库日期)
出库表OutStocks:
字段:ID(自动编号的,无需显示) In_No(对应的库存编号) Name(出库名称) Num(出库数量) OutDate(出库数量)
这两个表的InNo和In_No及Name和Name是对应的
)
procedure TForm1.Button4Click(Sender: TObject);
begin
with DM2.ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('Select Name as 库存名称,Sum(Num) as 入库数量 from Stocks');
SQL.Add('group by Name');
Open;
end;
end;
procedure TForm1.Button8Click(Sender: TObject);
begin
with DM2.ADOQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('Select Name as 出库名称,Sum(num) as 出库数量 from OutStocks');
SQL.Add('group by Name');
Open;
end;
end;想将这两条select语句的查询结果一起显示在一个DBGrid中~希望达人们帮帮忙~~(附这两表的结构: 库存表Stocks:
字段:ID(自动编号的,无需显示) InNo(库存编号) Name(库存名称) Num(库存数量) InDate(入库日期)
出库表OutStocks:
字段:ID(自动编号的,无需显示) In_No(对应的库存编号) Name(出库名称) Num(出库数量) OutDate(出库数量)
这两个表的InNo和In_No及Name和Name是对应的
)
union all
Select Name as 出库名称,Sum(num) as 出库数量 from OutStocks group by Name前提是字段数量要一致
begin
close;
sql.clear;
sql.add('select * from table1 where ....');
sql.add('union');
sql.add('select * from table2 where ....');
open;
end;
select 库存名称, sum(出库数量) as 出库总数, sum(入库数量) as 库存总数
from (Select Name as 库存名称, 0 as 出库数量, Sum(Num) as 入库数量
from Stocks
group by Name
union all
Select Name as 库存名称, Sum(num) as 出库数量, 0 as 入库数量
from OutStocks
group by Name) m
group by Name
from (Select Name as 库存名称, 0 as 出库数量, Sum(Num) as 入库数量
from Stocks
group by Name
union all
Select Name as 库存名称, Sum(num) as 出库数量, 0 as 入库数量
from OutStocks
group by Name) m
group by 库存名称
from Stocks a,OutStocks b
瞎写的
SELECT 库存名称 = Name,入库数量,出库数量
FROM
(Select Name, Sum(Num) as 入库数量 from Stocks group by Name) A
FULL JOIN (Select Name, Sum(num) as 出库数量 from OutStocks group by Name) B
ON A.NAME = B.NAME
UNION ALL
Select Name as 出库名称,0 as 入库数量,Sum(num) as 出库数量 from OutStocks group by Name
select * from
( Select Name as 库存名称,Sum(Num) as 入库数量 from Stocks group by Name) a,
( Select Name as 出库名称,Sum(num) as 出库数量 from OutStocks group by Name) b
where a.库存名称 = b.出库名称
“使用了union all后显示的时候字段的长度会被拉长”的问题,有解决的方法没?
如果能解决的话,明天加分后结贴,感谢这么多热心人!!
--
--如下,修改AA表col1字段的长度为10
ALTER TABLE AA ALTER COLUMN col1 varchar(10)
from (Select Name as 库存名称, 0 as 出库数量, Num as 入库数量
from Stocks
union all
Select Name as 库存名称, num as 出库数量, 0 as 入库数量
from OutStocks) m
group by 库存名称
SELECT 库存名称 = Name,入库数量,出库数量
FROM
(Select Name, Sum(Num) as 入库数量 from Stocks group by Name) A
FULL JOIN (Select Name, Sum(num) as 出库数量 from OutStocks group by Name) B
ON A.NAME = B.NAME
库存名称 出库总数 库存总数
库存1 33 400
库存2 20 800 select a.库存名称,b.出库数量 as 出库总数,a.入库数量 as 库存总数 from
( Select Name as 库存名称,Sum(Num) as 入库数量 from Stocks group by Name) a,
( Select Name as 出库名称,Sum(num) as 出库数量 from OutStocks group by Name) b
where a.库存名称 = b.出库名称
fieldname属性输入"库存总数"(注:输入时不包含双引号)每列的TITLE--->CAPTION设置显示的标题.每死的WIDTH设置长度.