各位高手,我写有如下一段sql
procedure TForm1.Button1Click(Sender: TObject);
begin
query1.SQL.Clear;
query1.SQL.Add('select trdate,dj_nmae,sum(b),aum(c) from wuri.db);
query1.sql.Add('where trdate<=:q1');
query1.sql.Add('group by dj_name');
query1.ParamByName('q1')=dbedit1.text;
query1.Open;
end;
我用的是paradox表
其中trdate是日期型;
我要按dj_name进行分组,但是按上面的写法必须把trdate字段也要加在group by
里面:
query1.sql.Add('group by trdate,dj_name');
但是这样又不是我需要的结果,我只要按dj_name字段进行分组
请问该怎样写?
procedure TForm1.Button1Click(Sender: TObject);
begin
query1.SQL.Clear;
query1.SQL.Add('select trdate,dj_nmae,sum(b),aum(c) from wuri.db);
query1.sql.Add('where trdate<=:q1');
query1.sql.Add('group by dj_name');
query1.ParamByName('q1')=dbedit1.text;
query1.Open;
end;
我用的是paradox表
其中trdate是日期型;
我要按dj_name进行分组,但是按上面的写法必须把trdate字段也要加在group by
里面:
query1.sql.Add('group by trdate,dj_name');
但是这样又不是我需要的结果,我只要按dj_name字段进行分组
请问该怎样写?
where trdate<=:q1
group by dj_name
query1.sql.Add('where trdate<=:q1');
query1.sql.Add('group by dj_name');
query1.ParamByName('q1')=dbedit1.text;
select trdate,dj_name,sum(b),aum(c) from wuri.db
group by trdate,dj_name
2:
select dj_name,sum(b),aum(c) from wuri.db
group by dj_name
3:不知道paradox是否可以如下,MSSQL是可以的
select a.trdate,tmp.*
from wuri.dbo a
join (
select dj_name,sum(b) as b,aum(c) as c from wuri.db
group by dj_name) tmp on a.dj_name = b.dj_name
select trdate,dj_name,sum(b),aum(c) from wuri.db
group by dj_name
trdate必须在select里面,而且trdate不能在group by 里面
begin
query1.SQL.Clear;
query1.SQL.Add('select dj_nmae,sum(b),aum(c) from wuri.db);
query1.sql.Add('where trdate<=:q1');
query1.sql.Add('group by dj_name');
query1.ParamByName('q1')=dbedit1.text;
query1.Open;
end;
from wuri.dbo a
join (
select dj_name,sum(b) as b,aum(c) as c from wuri.db
group by dj_name) tmp
on a.dj_name = b.dj_name
2.你不想按trdate分组,那么先要看trdate是不是唯一的值,若是你可以用Max或Min来写,若不是唯一的值那就必须写在group by语句中了,否则没法实现.