我有一表,结构如下:
Table1(Name, Year, Month, Float1, Float2)
我用了以下SQL语句输出结果:
Select Name, Year, Month, SUM(Float1) AS F1, SUM(Float2) AS F2
From Table1 Group By Name, Year, Month Order By Name, Year DESC
我现在需要在数据中增加一个新字段:F3,其值为F2/F1。我试过以下代码,但无论放在什么事件中均通不过:
var
NewField : TField;
begin
NewField := TField.Create(Self);
NewField.FieldName := 'F3';
NewField.SetFieldType := ftFloat;
NewField.DataSet := ADOQuery1;
ADOQuery1.Fields.Add(NewField);
end;有什么方法可以解决此问题?
Table1(Name, Year, Month, Float1, Float2)
我用了以下SQL语句输出结果:
Select Name, Year, Month, SUM(Float1) AS F1, SUM(Float2) AS F2
From Table1 Group By Name, Year, Month Order By Name, Year DESC
我现在需要在数据中增加一个新字段:F3,其值为F2/F1。我试过以下代码,但无论放在什么事件中均通不过:
var
NewField : TField;
begin
NewField := TField.Create(Self);
NewField.FieldName := 'F3';
NewField.SetFieldType := ftFloat;
NewField.DataSet := ADOQuery1;
ADOQuery1.Fields.Add(NewField);
end;有什么方法可以解决此问题?
From Table1 Group By Name, Year, Month Order By Name, Year DESC
SUM(Float1) /SUM(Float2) as F3
From Table1 Group By Name, Year, Month Order By Name, Year DESC
谢谢。我也用过您所示代码,但运行时显示语法错误,在“>”处。menggirl(看天上浮云,胜似闲庭信步) :
我现在用的就是您给出的代码,但我相信当F1为0时,肯定有问题。先谢谢两位。
其实我刚才写得也有些错误,只需判断sum(Float2)<>0就行了
就是死活不让过,比我顽固多了!! :)
谢谢。
(case when SUM(Float2)<>0 then SUM(Float1) /SUM(Float2) else 0 end) as F3
From Table1 Group By Name, Year, Month Order By Name, Year DESC
CONVERT(Float,F3)=SUM(Float2)/SUM(Float1))
1、如同在客户端为记录添加序号列一样,添加一个计算字段,也应该能解决问题。
2、采用SQL语句实现,就如上面几位所述。当然,采用2最简单。maimaizhi(mutiny)兄弟:
你的那一句应该为:
Select Name, Year, Month, SUM(Float1) AS F1, SUM(Float2) AS F2, CONVERT(Float, SUM(Float2)/SUM(Float1)) AS F3 From Table1 Group By Name, Year, Month Order By Name, Year DESCchenar(梧桐_WQ)兄弟:
我恨得你直咬牙——你把我的真名作为你的昵称!! : )结帖啦!谢谢各位!