做一个视图: create view yourview as select name,oldcount,nowcount,gcount,why,id where why='购买' group by name union select name,oldcount,nowcount,0-gcount,why,id where why='送人' group by name ... 列出所有原因,然后查询这个VIEW。
做一个视图: create view yourview as select name,oldcount,nowcount,gcount,why,id where why='购买' group by name union select name,oldcount,nowcount,0-gcount,why,id where why='送人' group by name ... 列出所有原因,然后查询这个VIEW。
三层结构应该怎么做我倒不十分清楚 不过,就你这个问题如果是一般的c/s结构 解决如下: 1.query1.sql.text:='select distinct name from abd order by name';//如果有时间段就加条件 2.生成字段对象query1Name,然后在query1中加入五个计算字段yuanyou,xianzai,songren,goumai,rensong 3.在query1的OnCalcfields事件中写如下代码: query2.close; query2.sql.text:='select oldcount from abd where name="'+query1Name.asstring+'" and date>="'+datetime+'" order by date';//datetime表示起始时间,自己确定 query2.open; query1YUANYOU.value:=query2.fields[0].value; query2.close; query2.sql.text:='select newcount from abd where name="'+query1Name.asstring+'" and date<="'+datetime2+'" order by date desc';//datetime2表示终止时间,自己确定 query2.open; query1XIANZAI.value:=query2.fields[0].value; query2.close; query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="送人"'; query2.open; query1SONGREN.value:=query2.fields[0].value; query2.close; query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="购买"'; query2.open; query1GOUMAI.value:=query2.fields[0].value; query2.close; query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="别人送"'; query2.open; query1RENSONG.value:=query2.fields[0].value; query2.close;
create view yourview as
select name,oldcount,nowcount,gcount,why,id where why='购买' group by name
union
select name,oldcount,nowcount,0-gcount,why,id where why='送人' group by name
...
列出所有原因,然后查询这个VIEW。
create view yourview as
select name,oldcount,nowcount,gcount,why,id where why='购买' group by name
union
select name,oldcount,nowcount,0-gcount,why,id where why='送人' group by name
...
列出所有原因,然后查询这个VIEW。
不过,就你这个问题如果是一般的c/s结构
解决如下:
1.query1.sql.text:='select distinct name from abd order by name';//如果有时间段就加条件
2.生成字段对象query1Name,然后在query1中加入五个计算字段yuanyou,xianzai,songren,goumai,rensong
3.在query1的OnCalcfields事件中写如下代码:
query2.close;
query2.sql.text:='select oldcount from abd where name="'+query1Name.asstring+'" and date>="'+datetime+'" order by date';//datetime表示起始时间,自己确定
query2.open;
query1YUANYOU.value:=query2.fields[0].value;
query2.close;
query2.sql.text:='select newcount from abd where name="'+query1Name.asstring+'" and date<="'+datetime2+'" order by date desc';//datetime2表示终止时间,自己确定
query2.open;
query1XIANZAI.value:=query2.fields[0].value;
query2.close;
query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="送人"';
query2.open;
query1SONGREN.value:=query2.fields[0].value;
query2.close;
query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="购买"';
query2.open;
query1GOUMAI.value:=query2.fields[0].value;
query2.close;
query2.sql.text:='select sum(gcount) from abd where name="'+query1Name.asstring+'" and date>="'+datetime1+'" and date<="'+datetime2+'" and why="别人送"';
query2.open;
query1RENSONG.value:=query2.fields[0].value;
query2.close;