xiaomeng 发表于 2007-8-29 16:07:59

请教sql语句嵌套

有一个公式，由下列4条sql语句计算得成：
--select count(ExamID) from MPPS where MatchTime >'2007-08-22 11:00:00.0' and MatchTime <'2007-08-23 11:00:00.0';
-- 13213
--select max(ExamID) from MPPS where MatchTime >'2007-08-22 11:00:00.0' and MatchTime <'2007-08-23 11:00:00.0';
-- 2155457
--select min(ExamID) from MPPS where MatchTime >'2007-08-22 11:00:00.0' and MatchTime <'2007-08-23 11:00:00.0';
-- 2133303
--select count(ExamID) from ExamInfo where ExamID > 2133303 and ExamID < 2155457;
-- 22153
auto match rate = 13213 / 22153 * 100% = 59.64%

请问如何将此4条sql语句嵌套成一条sql语句，且不需要输3遍日期？

kider 发表于 2007-8-29 16:12:02

当然可以，改写成：
mysql>
select @starttime:='2007-08-22 11:00';
select @endtime:='2007-08-23 11:00';
select @i_count:=count(ExamID) from MPPS where MatchTime >@starttime and MatchTime <@endtime;
select @i_max:=max(ExamID) from MPPS where MatchTime >@starttime and MatchTime <@endtime;
select @i_min:=min(ExamID) from MPPS where MatchTime >@starttime and MatchTime <@endtime;
select @ii_count:=count(ExamID) from ExamInfo where ExamID > @i_min and ExamID < @i_max;
select CONCAT(@i_count/@ii_count*100,'%') as "auto match rate" ;

一气呵成，直接执行，得出答案。
页: [1]
查看完整版本: 请教sql语句嵌套