select * from tb where datepart(wk,日期字段)=datepart(wk,getdate()) and datepart(yy,日期字段)=datepart(yy,getdate()) order by 点击量字段
先 谢谢 wzy_love_sly 的回答~ 报了一条这样的 错误~ Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\AppServ\www\djt\index.php on line 172我的时间 储存是这样种 样式的 2008-5-18 08:05:26
select * from 表 where now()-时间字段<1000000*7 order by 点击量字段 Mysql的计算时间差方法是这样的: 差一分是100; 差一秒是1; 差一时是10000; 差一天是1000000; 差一月是100*一天; 差一年是100*一月;
排行榜之MySQL实现法delimiter $$ drop procedure if exists sp_Order_Place$$ create procedure sp_Order_Place ( in s_Type char(1), in d_Date date ) begin declare begin_date,p_begin_date date; if instr('DWMQY',s_Type)=0 or s_Type='' then set s_Type='D'; end if;
if d_Date is null then set d_Date=current_date; end if; if s_Type='D' then set begin_date=d_Date; set d_Date=date_add(begin_date,interval 1 day); set p_begin_date=date_add(d_Date,interval -1 day); elseif s_Type='W' then set begin_date=date_add(d_Date,interval -7 day); set d_Date=date_add(begin_date,interval 7 day); set p_begin_date=date_add(begin_date,interval -7 day); elseif s_type='M' then set begin_date=date_add(d_Date,interval -dayofmonth(d_Date)+1 day); set d_Date=date_add(begin_date,interval 1 month); set p_begin_date=date_add(begin_date,interval -1 month); elseif s_type='Q' then set begin_date=date_add(date_add(d_Date,interval -(quarter(d_Date)*3-month(d_Date)-2) month), interval -dayofmonth(d_Date)+1 day); set d_Date=date_add(begin_date,interval 3 month); set p_begin_date=date_add(begin_date,interval -3 month); else set begin_date=date_add(d_Date,interval -dayofyear(d_Date)+1 day); set d_Date=date_add(begin_date,interval 1 year); set p_begin_date=date_add(begin_date,interval -1 year); end if; create temporary table t1(OrderID int,Quantity int); insert into t1 select OrderID,sum(Quantity) Quantity from Orders where OrderDate>=begin_date and OrderDate<d_date group by OrderID order by Quantity desc; create temporary table t2(OrderID int,Quantity int); insert into t2 select OrderID,sum(Quantity) Quantity from Orders where OrderDate>=p_begin_date and OrderDate<begin_date group by OrderID order by Quantity desc; create temporary table t3 select * from t1; create temporary table t4 select * from t2; select a.OrderID, case when b.OrderID is null then '新上榜' when a.Place=b.Place then '平稳' when a.Place>b.Place then concat('下降',a.Place-b.Place,'位') else concat('上升',b.Place-a.Place,'位') end as description from ( select OrderID,Quantity,(select count(*) from t3 where Quantity>aa.Quantity)+1 as Place from t1 aa limit 10 ) a left join ( select OrderID,Quantity,(select count(*) from t4 where Quantity>bb.Quantity)+1 as Place from t2 bb limit 10 ) b on a.OrderID=b.OrderID; drop table t1,t2,t3,t4; end$$ delimiter ;
where datepart(wk,日期字段)=datepart(wk,getdate())
and datepart(yy,日期字段)=datepart(yy,getdate())
order by 点击量字段
报了一条这样的 错误~
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\AppServ\www\djt\index.php on line 172我的时间 储存是这样种 样式的 2008-5-18 08:05:26
select *
from 表
where now()-时间字段<1000000*7
order by 点击量字段
Mysql的计算时间差方法是这样的:
差一分是100;
差一秒是1;
差一时是10000;
差一天是1000000;
差一月是100*一天;
差一年是100*一月;
drop procedure if exists sp_Order_Place$$
create procedure sp_Order_Place
(
in s_Type char(1),
in d_Date date
)
begin
declare begin_date,p_begin_date date;
if instr('DWMQY',s_Type)=0 or s_Type='' then
set s_Type='D';
end if;
if d_Date is null then
set d_Date=current_date;
end if;
if s_Type='D' then
set begin_date=d_Date;
set d_Date=date_add(begin_date,interval 1 day);
set p_begin_date=date_add(d_Date,interval -1 day);
elseif s_Type='W' then
set begin_date=date_add(d_Date,interval -7 day);
set d_Date=date_add(begin_date,interval 7 day);
set p_begin_date=date_add(begin_date,interval -7 day);
elseif s_type='M' then
set begin_date=date_add(d_Date,interval -dayofmonth(d_Date)+1 day);
set d_Date=date_add(begin_date,interval 1 month);
set p_begin_date=date_add(begin_date,interval -1 month);
elseif s_type='Q' then
set begin_date=date_add(date_add(d_Date,interval -(quarter(d_Date)*3-month(d_Date)-2) month),
interval -dayofmonth(d_Date)+1 day);
set d_Date=date_add(begin_date,interval 3 month);
set p_begin_date=date_add(begin_date,interval -3 month);
else
set begin_date=date_add(d_Date,interval -dayofyear(d_Date)+1 day);
set d_Date=date_add(begin_date,interval 1 year);
set p_begin_date=date_add(begin_date,interval -1 year);
end if;
create temporary table t1(OrderID int,Quantity int);
insert into t1
select OrderID,sum(Quantity) Quantity
from Orders
where OrderDate>=begin_date and OrderDate<d_date
group by OrderID
order by Quantity desc;
create temporary table t2(OrderID int,Quantity int);
insert into t2
select OrderID,sum(Quantity) Quantity
from Orders
where OrderDate>=p_begin_date and OrderDate<begin_date
group by OrderID
order by Quantity desc;
create temporary table t3 select * from t1;
create temporary table t4 select * from t2;
select a.OrderID,
case when b.OrderID is null then '新上榜'
when a.Place=b.Place then '平稳'
when a.Place>b.Place then concat('下降',a.Place-b.Place,'位')
else concat('上升',b.Place-a.Place,'位')
end as description
from
(
select OrderID,Quantity,(select count(*) from t3 where Quantity>aa.Quantity)+1 as Place
from t1 aa limit 10
) a
left join
(
select OrderID,Quantity,(select count(*) from t4 where Quantity>bb.Quantity)+1 as Place
from t2 bb limit 10
) b
on a.OrderID=b.OrderID;
drop table t1,t2,t3,t4;
end$$
delimiter ;
谢谢 在次谢谢~