问个问题看能不能难倒大家。反正是把我难倒了!!!数据表 informaiton ID Model grade 1 243w A
2 223w B 3 221w C 4 243w S 5 193w A 6 273w C如何统计表中有多少个 A ,B ,C ,S ? 并分别输出到Edit.text1 /Edit.text2 Edit.text3 / Edit.text4 中。???
2 223w B 3 221w C 4 243w S 5 193w A 6 273w C如何统计表中有多少个 A ,B ,C ,S ? 并分别输出到Edit.text1 /Edit.text2 Edit.text3 / Edit.text4 中。???
(select count(*) as AA from informaiton as a where a.grade='A') as aaa,
(select count(*) as BB from informaiton as a where a.grade='B') as bbb,
(select count(*) as CC from informaiton as a where a.grade='C') as ccc,
(select count(*) as SS from informaiton as a where a.grade='S') as sss,
grade from informaiton as b where b.id = '1'; 'adoquery.sql.clear();
adoquery.sql.add(mysql);
adoquery.open;Edit.text1 := adoquery.fieldbyname('aaa').asstring;
Edit.text2 := adoquery.fieldbyname('bbb').asstring;
Edit.text3 := adoquery.fieldbyname('ccc').asstring;
Edit.text4 := adoquery.fieldbyname('sss').asstring;2.若是不支持支持嵌套查询,就将上边SQL,分成4次查询,即可
Edit2.text := adoquery.fieldbyname('bbb').asstring;
Edit3.text := adoquery.fieldbyname('ccc').asstring;
Edit4.text := adoquery.fieldbyname('sss').asstring;
Insert information select 1,'243w','A'
Union all select 2,'223w','B'
Union all select 3,'221w','C'
Union all select 4,'243w','S'
Union all select 5,'193w','A'
Union all select 6,'273w','C'
--SQL语句
SELECT Grade,Count(1) as counts
FROM @Information
group by Grade---Delphi中执行
With Adoquery1 Do
begin
close;
sql.clear;
sql.text:='SELECT Grade,Count(1) as counts FROM Information group by Grade';
Open;
end;
for i:=0 to adoquery1.recount-1 do
begin
Findcomponent('Edit'+inttostr(i)).text:=adoquery1.fieldbyname('counts').asstring;
adoquery1.next;
end;
实际上用一个SQL语句即可SELECT grade, count(*) FROM informaiton然后的事情就很简单了
liangpei2008(逍遥叹)谢谢你,你的这种方法,我去试试。
spirit_sheng(老盛) 也谢谢你,SQL语句我还是知道这么写,(分组统计嘛)。但是在Delphi 怎么实现还不是很熟悉。
To all 还有更加好方法吗?
spirit_sheng(老盛) 同志的这种分组统计,
SELECT grade, count(*) FROM informaiton GROUP BY grade
然后对分组结果进行处理
当然分组统计后,可以把这些统计结果insert into temp1,放在一个临时表里再处理大家可能更得心应手些
并且如果追加统计其他情况的话,程序修改起来也会很方便,只需在delphi中增加一个case就可以了
var i:string;With Adoquery1 Do
begin
close;
sql.clear;
sql.text:='SELECT Grade,Count(1) as counts FROM Information group by Grade';
Open;
end;
for i:=0 to adoquery1.recount-1 do
begin
Findcomponent('Edit'+inttostr(i)).text:=adoquery1.fieldbyname('counts').asstring;
adoquery1.next;
end;end.
编译不了~~~~~~~~~~~[Error] Unit9.pas(650): For loop control variable must have ordinal type
[Error] Unit9.pas(650): Undeclared identifier: 'recount'
[Error] Unit9.pas(652): There is no overloaded version of 'IntToStr' that can be called with these arguments
[Fatal Error] Project2.dpr(16): Could not compile used unit 'Unit9.pas'
var
i:integer;//数值型滴With Adoquery1 Do
begin
close;
sql.clear;
sql.text:='SELECT Grade,Count(1) as counts FROM Information group by Grade';
Open;
First;
end;
for i:=0 to adoquery1.recount-1 do
begin
TEdit(Findcomponent('Edit'+inttostr(i))).text:=adoquery1.fieldbyname('counts').asstring;
adoquery1.next;
end;end.
for i:=0 to adoquery1..RecordCount-1 do
var
i:integer;
begin
With Adoquery1 Do
begin
close;
sql.clear;
sql.text:='SELECT Grade,Count(1) as counts FROM Information group by Grade';
Open;
First;
end;
for i:=0 to adoquery1.RecordCount-1 do
begin
TEdit(Findcomponent('Edit'+inttostr(i))).text:=adoquery1.fieldbyname('counts').asstring;
adoquery1.next;
end;end;end.谢谢大家,问题这样可以解决。但是如何把对应的结果写如到对应的Edit.text中。
如:Edit1.text 为统计A的结果。
Edit2.text 为统计B的结果
Edit3.text 为统计C的结果
Edit4.text 为统计S的结果????
请大家指点哦~~~~~~~~~
哎,终于解决了。谢谢大家!!!!
Sum(Case When Grade = 'B' Then 1 Else 0 End) Sum_B,
Sum(Case When Grade = 'C' Then 1 Else 0 End) Sum_C,
Sum(Case When Grade = 'S' Then 1 Else 0 End) Sum_S
From Information
var s:string;
var mStr:string;
var low,high:string;
begin
mStr := 'select * from information where ';
s := ''; if Rzcombobox1.Text<>'' then
s:=s+'model='''+Rzcombobox1.Text+''' and '; if Edit1.Text<>'' then
s:=s+'sampleID='''+Edit1.Text+''' and '; if Rzcombobox2.Text<>'' then
s:=s+'shift='''+Rzcombobox2.Text+''' and '; if Rzcombobox3.Text<>'' then
s:=s+'line='''+Rzcombobox3.Text+''' and '; if Rzcombobox4.Text<>'' then
s:=s+'grade='''+Rzcombobox4.Text+''' and '; if Rzcombobox5.Text<>'' then
s:=s+'Inspector='''+Rzcombobox5.Text+''' and '; low:=formatdatetime('yyyy-mm-dd',DateTimePicker1.Date);
high:=formatdatetime('yyyy-mm-dd',DateTimePicker2.Date);
s:=mStr + s + 'workdate<=#'+high+'# and workdate >=#'+low+'#' + 'order by parts' ; ADOQuery1.Connection:=ADOConnection1;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(s);
ADOQuery1.Open;end;s:=mStr + s + 'workdate<=#'+high+'# and workdate >=#'+low+'#' + 'order by parts' ;
上面这条语句的为什么不能查询 具体某一天的查询。(比如 2006-11-19)