如數據Table中有記錄數為1,1,2,3,3,4,5按sql語句統計,select count(xx) from table 統計得記錄數為7,select count(distinct xx) from table 統計得記錄數為5.在delphi中用select count(distinct xx) from table ,提示錯誤,用select distinct count( xx) from table 無效,統計得記錄數為7.請問如何解答?不希望先把記錄數插入新表再統計.....
调试欢乐多
这样
select count(b.*) from (select distinct xx from table) b
with dm.SumQry do
begin
close;
sql.Clear;
sql.Add('select z_bc.dates,count(zhusu.jh), sum(zhusu.scrs),sum(zhusu.scsl),sum(zhusu.scsj),sum(zhusu.bchg),avg(zhusu.wcl),sum(zhusu.fp),avg(zhusu.fpl),sum(zhusu.scqs) ,sum(zhusu.tjsj), sum(zhusu.sj2)from z_bc,zhusu');
sql.Add('where z_bc.dates >=:dates1 and dates<=:dates2 and z_bc.z_id=zhusu.z_id group by z_bc.dates,z_bc.z_id');
dm.SumQry.Parameters.ParamByName('dates1').Value:=dtker1.Text;
dm.SumQry.Parameters.ParamByName('dates2').Value:=dtker2.Text;
open;
應如何修改!!!關鍵是統計上面的count(zhusu.jh).
with dm.SumQry do
begin
close;
sql.Clear;
sql.Add('select z_bc.dates,count(zhusu.jh), sum(zhusu.scrs),sum(zhusu.scsl),sum(zhusu.scsj),sum(zhusu.bchg),avg(zhusu.wcl),sum(zhusu.fp),avg(zhusu.fpl),sum(zhusu.scqs) ,sum(zhusu.tjsj), sum(zhusu.sj2)from z_bc,zhusu');
sql.Add('where z_bc.dates >=:dates1 and dates<=:dates2 and z_bc.z_id=zhusu.z_id group by z_bc.dates,z_bc.z_id');
dm.SumQry.Parameters.ParamByName('dates1').Value:=dtker1.Text;
dm.SumQry.Parameters.ParamByName('dates2').Value:=dtker2.Text;
open;
應如何修改!!!關鍵是統計上面的count(zhusu.jh).
嗎???????????
(select z_bc.dates,count(zhusu.jh), sum(zhusu.scrs),sum(zhusu.scsl),sum(zhusu.scsj),sum(zhusu.bchg),avg(zhusu.wcl),sum(zhusu.fp),avg(zhusu.fpl),sum(zhusu.scqs) ,sum(zhusu.tjsj), sum(zhusu.sj2)from z_bc,zhusu where z_bc.dates >=:dates1 and dates<=:dates2 and z_bc.z_id=zhusu.z_id group by z_bc.dates,z_bc.z_id) a ,(select count(zhusu.jh) from zhusu where
select a.y,b.x,a.a,a.b,a.c,a.d,a.e,a.f,a.g,a.h,a.i,a.j from
(select z_bc.dates y, sum(zhusu.scrs) a,sum(zhusu.scsl) b,sum(zhusu.scsj) c,sum(zhusu.bchg) d,avg(zhusu.wcl) e,
sum(zhusu.fp) f,avg(zhusu.fpl) g,sum(zhusu.scqs) h,sum(zhusu.tjsj) i, sum(zhusu.sj2) j from z_bc,zhusu where z_bc.dates >=:dates1
and dates<=:dates2 and z_bc.z_id=zhusu.z_id group by z_bc.dates,z_bc.z_id) a ,
(select count(zhusu.jh) x from z_bc,zhusu where z_bc.dates >=:dates1 and dates<=:dates2 and z_bc.z_id=zhusu.z_id) b
把count单独分开