SELECT person.a0101, person.dept_id, AVG(person.true_cent * stardard.per)
AS average_cent
FROM stardard INNER JOIN
person ON stardard.dept_id = person.dept_id AND
stardard.standard_name = person.standard_name
GROUP BY person.a0101, person.dept_id
AS average_cent
FROM stardard INNER JOIN
person ON stardard.dept_id = person.dept_id AND
stardard.standard_name = person.standard_name
GROUP BY person.a0101, person.dept_id
person ON stardard.dept_id = person.dept_id AND
stardard.standard_name = person.standard_name条件在这里设了
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('SELECT person.a0101, person.dept_id, AVG(person.true_cent * standrd.per) AS average_cent FROM standrd INNER JOIN person ON standrd.dept_id = person.dept_id AND standrd.standard_name = person.standard_name GROUP BY person.a0101, person.dept_id');
query4.Open;
close;
end;对于这样的保存数据我没有遇到过,望高手指点!
字段:A0101 dept_id average_cent
VALUES (99)
SELECT person.a0101,
person.dept_id,
AVG(person.true_cent
* stardard.per)
AS average_cent
FROM stardard INNER JOIN
person ON
stardard.dept_id =
person.dept_id AND
stardard.standard_name
= person.standard_name
GROUP BY person.a0101,
person.dept_id
INSERT INTO A2
VALUES (99)
SELECT person.a0101,
person.dept_id,
AVG(person.true_cent
* stardard.per)
AS average_cent
FROM stardard INNER JOIN
person ON
stardard.dept_id =
person.dept_id AND
stardard.standard_name
= person.standard_name
GROUP BY person.a0101,
person.dept_id
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average VALUES (99)'SELECT person.a0101, person.dept_id, AVG(person.true_cent * standrd.per) AS average_cent FROM standrd INNER JOIN person ON standrd.dept_id = person.dept_id AND standrd.standard_name = person.standard_name GROUP BY person.a0101, person.dept_id');
query4.Open;
close;
end;
一个是char的长度,一个是我用的是int,不知道你是是real还是int.
你在调试一下吧。
insert average(
A0101,dept_id,average_cent)
select person.A0101, person.dept_id, sum(person.True_cent * standrd.per) from stardard, person where stardard.dept_id = person.dept_id and stardard.standard_name = person.standard_name
group by person.A0101, person.dept_id有重复的原因应该是有的姓名有不同的dept_id。
char int float
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average VALUES (A0101,dept_id,average_cent) SELECT person.a0101, person.dept_id, sum(person.true_cent * standrd.per) AS average_cent FROM standrd INNER JOIN person ON standrd.dept_id = person.dept_id AND standrd.standard_name = person.standard_name GROUP BY person.a0101, person.dept_id');
query4.Open;
close;
end;
你的公式要说清楚!!
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average (A0101,dept_id,average_cent) SELECT person.a0101, person.dept_id, sum(person.true_cent * standrd.per) AS average_cent FROM standrd INNER JOIN person ON standrd.dept_id = person.dept_id AND standrd.standard_name = person.standard_name GROUP BY person.a0101, person.dept_id');
query4.Open;
close;
end;
然后将对应的人名和部门编号存入表average
编译的时候出错:string literals may have at most 255 elements
现在只有分开作了,
第一步:CREATE TABLE aver_table(a0101 char(10), dept_id int, aver_num float)
第二步:INSERT INTO aver_table VALUES (99) SELECT person.a0101, person.dept_id, AVG(person.true_cent * stardard.per)
AS average_cent
FROM stardard INNER JOIN
person ON stardard.dept_id = person.dept_id AND
stardard.standard_name = person.standard_name
GROUP BY person.a0101, person.dept_id
(好象还不知道你用的数据库)
这个是我修改的代码:
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert to average values (99) SELECT person.a0101, person.dept_id, AVG(person.true_cent * standrd.per) AS average_cent FROM standrd INNER JOIN person ON standrd.dept_id = person.dept_id AND standrd.standard_name = person.standard_name GROUP BY person.a0101, person.dept_id');
query4.Open;
close;
end;还是老错误!
在sql里写存储过程
在delphi中用TStoredProc调用就可以了。
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, sum(a.true_cent*b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');
query4.Open;
close;
end;在255以下了!!
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average(A0101,dept_id,average_cent SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');
query4.Open;
close;
end;
修改成这样,说select附近有错,不知道在哪里?
procedure TForm2.Button2Click(Sender: TObject);
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');
query4.Open;
close;
end;
看了怎么还是AVG
回答他的问题好累呀shilydream(波波) 你要好好谢谢两位高手
看来你是刚做数据库的吧
但是我用AVG可以啊,他们的区别在哪里?
结果可以出来了,也可以保存,但是DBTables有错误,就是这一行:
raise ENoResultSet.Create(SHandleError);
是怎么回事?