62,615
社区成员
发帖
与我相关
我的任务
分享
SELECTt.cargoid,t.city,t.brand,t.cargoname,t.ctypeid,pub.cname,pub.logo,shp.name,shp.address,shp.account,t.photourl,ss.shoplogo from (select c.* from CUSSHOPHISTORY c where c.city in (select t.cityid from PUBSTATSNUM t where t.shpeffnum>0) ) t,PUBBRAND pub,SHPSHOP shp,SHPSPACE ss
where t.shopid=shp.code and t.brand=pub.brandcode and t.shopid=ss.shopcode
and t.buytime>date_format('2009-09-21 00:00:00','%Y-%m-%d %H:%i:%s')
and t.buytime<date_format('2009-09-28 23:59:59','%Y-%m-%d %H:%i:%s')
order by t.buyamount desc limit 0,10
SELECT c.cargoid,c.city,c.brand,c.cargoname,c.ctypeid,c.photourl,
pub.cname,pub.logo,
shp.name,shp.address,shp.account,
ss.shoplogo
from CUSSHOPHISTORY c
left outer join PUBSTATSNUM t on c.cityid=t.cityid and t.shpeffnum>0
left outer join PUBBRAND pub on c.brand=pub.brandcode
left outer join SHPSPACE ss on c.shopid=ss.shopcode
left outer join SHPSHOP shp on c.shopid=shp.code
where c.buytime between '2009-09-21 00:00:00' and '2009-09-28 23:59:59'
order by c.buyamount desc limit 0,10
##子查询里面,有个明显的错误。c表的city字段,应该是字符型;t表的cityid应该是的数字
select c.* from CUSSHOPHISTORY c where c.city in
(select t.cityid from PUBSTATSNUM t where t.shpeffnum>0)
select m.cargoid,
m.city,
m.brand,
m.cargoname,
m.ctypeid,
pub.cname,
pub.logo,
shp.name,
shp.address,
shp.account,
m.photourl,
ss.shoplogo
from ( select *
from( select c.*,
row_number() over(partition by t.city order by buyamount) rank
from CUSSHOPHISTORY c
where c.city in (select t.cityid from PUBSTATSNUM t where t.shpeffnum > 0)
and c.buytime > date_format('2009-09-21 00:00:00','%Y-%m-%d %H:%i:%s')
and c.buytime < date_format('2009-09-28 23:59:59','%Y-%m-%d %H:%i:%s'))
where rank <= 10
) m,
PUBBRAND pub,
SHPSHOP shp,
SHPSPACE ss
where m.shopid = shp.code
and m.brand = pub.brandcode
and m.shopid = ss.shopcode;
select m.cargoid,
m.city,
m.brand,
m.cargoname,
m.ctypeid,
pub.cname,
pub.logo,
shp.name,
shp.address,
shp.account,
m.photourl,
ss.shoplogo
from ( select r.cargoid,
r.city,
r.brand,
r.cargoname,
r.ctypeid,
r.photourl,
r.shopid
from (
select t.cargoid,
t.city,
t.brand,
t.cargoname,
t.ctypeid,
t.photourl,
t.shopid
@rownum:=@rownum + 1,
if(@pcity=t.city,@rank:=@rank+1,@rank:=1) as rank,
@pcity:=t.city
from( select c.*
from CUSSHOPHISTORY c
where c.city in (select t.cityid from PUBSTATSNUM t where t.shpeffnum > 0)
and c.buytime > date_format('2009-09-21 00:00:00','%Y-%m-%d %H:%i:%s')
and c.buytime < date_format('2009-09-28 23:59:59','%Y-%m-%d %H:%i:%s')
order by c.city asc,
c.buyamount desc
) t,
( select @rownum := 0,
@pcity := null,
@rank := 0
)
) r
where r.rank <= 10
) m,
PUBBRAND pub,
SHPSHOP shp,
SHPSPACE ss
where m.shopid = shp.code
and m.brand = pub.brandcode
and m.shopid = ss.shopcode;
SELECT t.cargoid,
t.city,
t.brand,
t.cargoname,
t.ctypeid,
pub.cname,
pub.logo,
shp.name,
shp.address,
shp.account,
t.photourl,
ss.shoplogo
from ( select c.*
from CUSSHOPHISTORY c
where c.city in (select t.cityid from PUBSTATSNUM t where t.shpeffnum > 0)
) t,
PUBBRAND pub,
SHPSHOP shp,
SHPSPACE ss
where t.shopid = shp.code
and t.brand = pub.brandcode
and t.shopid = ss.shopcode
and t.buytime > date_format('2009-09-21 00:00:00','%Y-%m-%d %H:%i:%s')
and t.buytime < date_format('2009-09-28 23:59:59','%Y-%m-%d %H:%i:%s')
order by t.buyamount desc limit 0,10