取出sql表中第31到40的记录(以自动增长ID为主键)
sql server方案1:select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
sql server方案2:select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
mysql方案:select * from t order by id limit 30,10
oracle方案:select * from (select rownum r,* from t where r<=40) where r>30
相比以下效率高:select * from emp where rownum<=40 minus select * from emp where rounum<31;
select distinct name from score where name not in (select distinct name from score where score<=80)
注:mysql也有distinct关键字
select sales.year ,
(select t.amount from sales t where t.month='1' and t.year= sales.year) '1',
(select t.amount from sales t where t.month='1' and t.year= sales.year) '2',
(select t.amount from sales t where t.month='1' and t.year= sales.year) '3',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as '4'
from sales group by year;
结果:
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
var sUsrAgent=navigator.userAgent;
var isIE=sUsrAgent.indexOf("MSIE")!=-1;
var isIE6=isIE&&sUsrAgent.indexOf("MSIE 6.0")!=-1;
var isIE7=isIE&&sUsrAgent.indexOf("MSIE 7.0")!=-1;
var isFF=sUsrAgent.indexOf("Firefox")!=-1;
var isOP=sUsrAgent.indexOf("Opera")!=-1;
var isSF=sUsrAgent.indexOf("Safari")!=-1
function test(){
var start = Date.parse(document.getElementById("begin").value);//Date.parse("2010/4/09 02:03:10");
var end = Date.parse(document.getElementById("end").value);//Date.parse("2010/5/04 05:08:50");
document.writeln("最开始时间:" + start);
document.writeln("最后的时间" + end);
var value = end-start;
var flag = value > 31*24*60*60*1000;//时间段是否在一个月内( 以31天计算)
if(flag){
alert("时间段不能超过一个月");
return;
}
var timeStep = 12*60*60*1000;
var times = Math.ceil(value/timeStep);//计算总下发次数
document.writeln("下发次数:" + times);
var d = new Date();
var d2 = new Date();
var startDate;
var endDate = start;
for(var i = 0;i < times;i++){//把时间段以时间步长为12小时进行循环查询
startDate = endDate;
if(i == times-1){
endDate = end;
}else{
endDate += timeStep;
}
d.setTime(startDate);
d2.setTime(endDate);
document.writeln("开始时间:"+d.toLocaleString());
document.writeln("结束时间:"+d2.toLocaleString());
}