MySQL社区

标题: 请教sql语句嵌套 [打印本页]

作者: xiaomeng 时间: 2007-8-29 16:07
标题: 请教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
当然可以，改写成：

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" ;

一气呵成，直接执行，得出答案。

欢迎光临 MySQL社区 (http://www.mysqlpub.com/)