数据库ACCESS,数据表gugan,表中有字段birthday(出生日期),whcd(文化程度),Rzz_time(任正职时间)等
文化程度分为:小学、初中、高中、中专、大专、本科、硕士、博士,在查询界面上用ComboBox提供让用户选择,
birthday和Rzz_time字段在表中均为datetime类型。现在要求实现:
统计出年龄在40岁到50岁之间、文化程度中专到本科之间、任正职时间在3年到10年之间的所有人员,请问这样的SQL语句要如何编写?
文化程度分为:小学、初中、高中、中专、大专、本科、硕士、博士,在查询界面上用ComboBox提供让用户选择,
birthday和Rzz_time字段在表中均为datetime类型。现在要求实现:
统计出年龄在40岁到50岁之间、文化程度中专到本科之间、任正职时间在3年到10年之间的所有人员,请问这样的SQL语句要如何编写?
解决方案 »
- delphi 7 调试器 用不了啊
- 关于StringGrid 的问题!求助!
- 小女子问一个简单的问题,关于Delphi中枚举类型的。
- 怎样用ado联结带有密码的access数据库
- 自己开发控件,集合类型的属性的集合的子集如何随控件另一个属性的值改变?
- 在线等待:读取文件时怎样判断被读取的文件是否存在?
- 怎样把文本(string型)加到richedit的光标(当前输入位置)后?谢谢!!
- 紧急求助,我出现了:out of Memory,各位大虾帮帮忙
- 怎样创建一个表?
- 请问在delphi中有没有字符串反向查找的函数?
- 急急急!!!!!!请大家快来帮帮我 关于ADOCommand的插入问题
- 问一个很菜的问题 别笑!
var sj:string;
sj:=formatdatetime('yyyymmdd',now);
select * from gugan where (strtoint(sj)-strtoint(convert(birthday,112))) between 40 and 50 and whcd in ('中专','大专','本科') and Rzz_time between 3 and 10基本的语句就这样,但我不清楚access里面有没有convert函数转换时间,我用的sql数据库,你可以去自己查下看。
如果是查询界面,显然ComboBox不对,因为它是单选的。
你忘记交代文化程度数据类型了,假设是数字类型,0代表小学,1中学select * from gugan where now-birthday between 40 and 50 and now-Rzz_time between 3 and 8 and whcd between 3 and 5;
假设是字符串类型
select * from gugan where now-birthday between 40 and 50 and now-Rzz_time between 3 and 8 and whcd in ('中专','大专','本科');如果是单选的combobox1[字符串类型]
那么假设你用delphi
sql.text:='select * from gugan where now-birthday between 40 and 50 and now-Rzz_time between 3 and 8 and whcd='''+combobox1.text+''''如果是单选的combobox1[数字类型]
那么假设你用delphi
sql.text:='select * from gugan where now-birthday between 40 and 50 and now-Rzz_time between 3 and 8 and whcd='+inttostr(combobox1.itemindex);
.另外不知道虚岁,实岁,闰月有没有对x年产生影响?
虚岁
year(now)-year(birthday) between;
实际岁数很复杂 year(now-birthday)仅仅是近似
(now-birthday)/365.2425也仅仅是近似哈哈
年龄:_(1)_岁至_(2)__岁 文化程度:_(3)__ 至 __(4)__任正职时间:_(5)_年至 __(6)__年对以上这几个数字解释一下:
(1):nAgeFromEdit(文本框供用户输入)
(2):nAgeToEdit(文本框供用户输入)
(3):WhcdFRomComboBox(ComboBox供用户选择)
(4):WhcdFromComboBox(ComboBox供用户选择)
(5):RzzFromEdit(文本框供用户输入)
(6):RzzToEDit(文本框供用户输入)另外一点就是表中的Whcd(文化程度)为字符型(数据输入界面ComboBox将文化程度对应的字符填入该字段,但为了用户输入和查询数据的方便,将所有学历通过ComboBox提供出来供用户选择)。这样已经够清楚了吧?请回答问题的朋友仔细考虑下问题的需求,2楼的答案是无法满足需求的,因为文化程度从什么到什么是要用户选择后才能决定的。
(4):WhcdToComboBox(ComboBox供用户选择)
select * from gugan where now between dateadd('yyyy',40,birthday) and dateadd('yyyy',50,birthday) and now between dateadd('yyyy',3,birthday) and dateadd('yyyy',8,birthday) and whcd='本科';由于使用了dateadd函数,可以不考虑闰年了
i:integer;
whcds:string;
begin
whcds:='';
for i:=WhcdFRomComboBox.itemindex to WhcdFRomComboBox.itemindex do begin
whcds:=whcd+','''+WhcdFRomComboBox.items[i]+'''';
end;
delete(whcds,1,1);
whcds:='('+whcds+')';
query1.sql.clear; query.sql.add('select * from gugan where now between dateadd(''yyyy'','+nAgefromEdit.text+',birthday) and dateadd(''yyyy'','+nAgeToEdit.text+',birthday) and now between dateadd(''yyyy'','+RzzFromEdit.text+',birthday) and dateadd(''yyyy'','+RzztoEdit.text+',birthday) and and whcd in'+whcds);
....
end;
query.sql.add('select * from gugan where now between dateadd(''yyyy'','+nAgefromEdit.text+',birthday) and dateadd(''yyyy'','+nAgeToEdit.text+',birthday) and now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time) and whcd in'+whcds);
procedure TForm1.BitBtn4Click(Sender: TObject);var
i,AgeFrom,AgeTo,RzzFrom,RzzTo : integer;
WhcdFrom,WhcdTo,SqlStr,whcds : string;begin
SqlStr := '';
try
begin {年龄查询条件处理}
if (Trim(AgeFromEdit.Text)<>'') or (Trim(AgeToEdit.Text)<>'') then
begin
AgeFrom := StrToInt(trim(AgeFromEdit.Text));
AgeTo := StrToInt(trim(AgeToEdit.Text));
if AgeFrom>AgeTo then
begin
ShowMessage('年龄设置有问题,请重新检查');
AgeFromEdit.SetFocus;
end
else if SqlStr='' then
SqlStr := 'now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)'
else
SqlStr := SqlStr+'and now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)';
end; {任正职时间查询条件处理}
if (Trim(RzzFromEdit.Text)<>'') or (Trim(RzzToEdit.Text)<>'') then
begin
RzzFrom := StrToInt(trim(RzzFromEdit.Text));
RzzTo := StrToInt(trim(RzzToEdit.Text));
if RzzFrom>RzzTo then
begin
ShowMessage('查询条件<任职时间>设置有问题,请重新检查');
RzzFromEdit.SetFocus;
end
else
begin
if SqlStr='' then
SqlStr :='dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)'
else
SqlStr := Sqlstr+'and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)';
end;
end; {文化程度查询条件处理}
if (Trim(WhcdFromComboBox.Text)<>'') or (Trim(WhcdFromComboBox.Text)<>'') then
begin
WhcdFrom := trim(WhcdFromComboBox.Text);
WhcdTo := trim(WhcdToComboBox.Text);
if WhcdFromComboBox.ItemIndex>WhcdToComboBox.ItemIndex then
begin
ShowMessage('文化程度设置有问题,请重新检查');
WhcdFromComboBox.SetFocus;
end
else
begin
whcds := '';
for i:=WhcdFRomComboBox.itemindex to WhcdToComboBox.itemindex do begin
whcds:=whcds+','+''+WhcdFRomComboBox.items[i]+'''';
end;
Delete(whcds,1,1);
if SqlStr='' then
SqlStr :='whcd in+whcds'
else
SqlStr := Sqlstr+'and whcd in+whcds';
end;
end;
try
with DataModuleADO.ADOQuery1 do
begin
SQL.Clear;
sql.add(trim(SqlStr)); {cdsnghw提供的SQL语串: sql.add('select * from gugan where now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday) and now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time) and whcd in'+whcds);} open;
DataSource1.DataSet:=DataModuleADO.ADOQuery1;
DbGrid1.DataSource:=DataSource1;
end;
except
MessageDlg('年龄、文化程度或任正职时间查询有误!',mtError,[mbok],0);
end; end; //顶层try
except
MessageDlg('查询条件设置存在问题,请检查是否设置正确、完整!',mtError,[mbok],0);
end;
procedure TForm1.BitBtn4Click(Sender: TObject);var
i,AgeFrom,AgeTo,RzzFrom,RzzTo : integer;
WhcdFrom,WhcdTo,SqlStr,whcds : string;begin
SqlStr := '';
try
begin {年龄查询条件处理}
if (Trim(AgeFromEdit.Text)<>'') or (Trim(AgeToEdit.Text)<>'') then
begin
AgeFrom := StrToInt(trim(AgeFromEdit.Text));
AgeTo := StrToInt(trim(AgeToEdit.Text));
if AgeFrom>AgeTo then
begin
ShowMessage('年龄设置有问题,请重新检查');
AgeFromEdit.SetFocus;
end
else if SqlStr='' then
SqlStr := 'now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)'
else
SqlStr := SqlStr+'and now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)';
end; {任正职时间查询条件处理}
if (Trim(RzzFromEdit.Text)<>'') or (Trim(RzzToEdit.Text)<>'') then
begin
RzzFrom := StrToInt(trim(RzzFromEdit.Text));
RzzTo := StrToInt(trim(RzzToEdit.Text));
if RzzFrom>RzzTo then
begin
ShowMessage('查询条件<任职时间>设置有问题,请重新检查');
RzzFromEdit.SetFocus;
end
else
begin
if SqlStr='' then
SqlStr :='dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)'
else
SqlStr := Sqlstr+'and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)';
end;
end; {文化程度查询条件处理}
if (Trim(WhcdFromComboBox.Text)<>'') or (Trim(WhcdFromComboBox.Text)<>'') then
begin
WhcdFrom := trim(WhcdFromComboBox.Text);
WhcdTo := trim(WhcdToComboBox.Text);
if WhcdFromComboBox.ItemIndex>WhcdToComboBox.ItemIndex then
begin
ShowMessage('文化程度设置有问题,请重新检查');
WhcdFromComboBox.SetFocus;
end
else
begin
whcds := '';
for i:=WhcdFRomComboBox.itemindex to WhcdToComboBox.itemindex do begin
whcds:=whcds+','+''+WhcdFRomComboBox.items[i]+'''';
end;
Delete(whcds,1,1);
if SqlStr='' then
SqlStr :='whcd in+whcds'
else
SqlStr := Sqlstr+'and whcd in+whcds';
end;
end;
try
with DataModuleADO.ADOQuery1 do
begin
SQL.Clear;
sql.add(trim(SqlStr));{cdsnghw提供的SQL语串: sql.add('select * from gugan where now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday) and now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time) and whcd in'+whcds);} open;
DataSource1.DataSet:=DataModuleADO.ADOQuery1;
DbGrid1.DataSource:=DataSource1;
end;
except
MessageDlg('年龄、文化程度或任正职时间查询有误!',mtError,[mbok],0);
end; end; //顶层try
except
MessageDlg('查询条件设置存在问题,请检查是否设置正确、完整!',mtError,[mbok],0);
end;
sql.add(trim(SqlStr));之后你应该加上一句调试语句:
showmessage(sqlstr);
你的大量代码if SqlStr='' 没有意义,因为它应该初始化为
SqlStr := 'select * from gugan where ';
var
i,AgeFrom,AgeTo,RzzFrom,RzzTo : integer;
WhcdFrom,WhcdTo,SqlStr,whcds : string;begin
SqlStr := '';//select * from gugan where
try
begin
//逻辑上你的错误:从你的代码看出,如果条件不正确设置,则认为是不进行条件限制 ,这是不对的,应该添加退出语句exit
{年龄查询条件处理}
//if (Trim(AgeFromEdit.Text)<>'') or (Trim(AgeToEdit.Text)<>'') then
//条件出错,应该用 and 而不是用 or
if (Trim(AgeFromEdit.Text)<>'') and (Trim(AgeToEdit.Text)<>'') then
begin
AgeFrom := StrToInt(trim(AgeFromEdit.Text));
AgeTo := StrToInt(trim(AgeToEdit.Text));
if AgeFrom>AgeTo then
begin
ShowMessage('年龄设置有问题,请重新检查');
AgeFromEdit.SetFocus;
exit;//添加
end
else if SqlStr='' then
SqlStr := 'now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)'
else
SqlStr := SqlStr+' and now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday)';
end //应该加上else exit
; {任正职时间查询条件处理}
//if (Trim(RzzFromEdit.Text)<>'') or (Trim(RzzToEdit.Text)<>'') then
if (Trim(RzzFromEdit.Text)<>'') and (Trim(RzzToEdit.Text)<>'') then
//条件出错,应该用 and 而不是用 or
begin
RzzFrom := StrToInt(trim(RzzFromEdit.Text));
RzzTo := StrToInt(trim(RzzToEdit.Text));
if RzzFrom>RzzTo then
begin
ShowMessage('查询条件<任职时间>设置有问题,请重新检查');
RzzFromEdit.SetFocus;
exit;//添加
end
else
begin
if SqlStr='' then
//条件语句错误修改
SqlStr :='now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)'
else
//条件语句错误修改
SqlStr := Sqlstr+' and now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time)';
end;
end//应该加上else exit
; {文化程度查询条件处理}
if (Trim(WhcdFromComboBox.Text)<>'') or (Trim(WhcdFromComboBox.Text)<>'') then
begin
WhcdFrom := trim(WhcdFromComboBox.Text);
WhcdTo := trim(WhcdToComboBox.Text);
if WhcdFromComboBox.ItemIndex>WhcdToComboBox.ItemIndex then
begin
ShowMessage('文化程度设置有问题,请重新检查');
WhcdFromComboBox.SetFocus;
exit;//添加
end
else
begin
whcds := '';
for i:=WhcdFRomComboBox.itemindex to WhcdToComboBox.itemindex do begin
//你没有按照我的原句子,出错,whcds:=whcds+','+''+WhcdFRomComboBox.items[i]+'''';
whcds:=whcds+','''+WhcdFRomComboBox.items[i]+'''';
end;
Delete(whcds,1,1);
//你没有按照我的加上下面一句
whcds:='('+whcds+')';
if SqlStr='' then
//你又错了 SqlStr :='whcd in+whcds'
SqlStr :='whcd in'+whcds
else
//你又错了 SqlStr := Sqlstr+'and whcd in+whcds';
SqlStr := Sqlstr+' and whcd in'+whcds;
end;
end//应该加上else exit
;
try
with DataModuleADO.ADOQuery1 do
begin
SQL.Clear;
if not (MessageDlg('你的查询条件是"'+sqlstr+'"',mtConfirmation, [mbYes, mbNo], 0) = mrYes) then exit;
//下面最重要的东西你忘记了
if sqlstr='' then sqlstr:='select * from gugan' else
sqlstr:='select * from gugan where '+sqlstr; sql.add(trim(SqlStr));
//加上调试语句{cdsnghw提供的SQL语串: sql.add('select * from gugan where now between dateadd(''yyyy'','+AgefromEdit.text+',birthday) and dateadd(''yyyy'','+AgeToEdit.text+',birthday) and now between dateadd(''yyyy'','+RzzFromEdit.text+',Rzz_time) and dateadd(''yyyy'','+RzztoEdit.text+',Rzz_time) and whcd in'+whcds);} open;
DataSource1.DataSet:=DataModuleADO.ADOQuery1;
DbGrid1.DataSource:=DataSource1;
end;
except
MessageDlg('年龄、文化程度或任正职时间查询有误!',mtError,[mbok],0);
end; end; //顶层try
except
MessageDlg('查询条件设置存在问题,请检查是否设置正确、完整!',mtError,[mbok],0);
end;
end;
连OR和AND的关系都没说清楚。白痴一个。
非常感谢cdsnghw耐心的回答,本问题是我业余编程中碰到的一个问题,问题已经完全解决,结贴!