复杂语句整合:
1、创建临时表 2、批量更新临时表
create table temp_num as select t.website,count(t.website) as count from
( select website from app_coupon_api
where website in (select website from app_coupon_brand
) and status = 'public' and ( UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y'))
> (UNIX_TIMESTAMP(now())-0)
or UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) = 0
or end_date is null ) ) t GROUP BY t.website order by count(t.website) desc;
ALTER TABLE `temp_num` ADD INDEX `index_website` (`website`) USING BTREE ;
update app_rank a left join temp_num b on a.website = b.website set a.valid_coupon_num_day_0 = b.count
update app_rank set valid_coupon_num_day_0 = 0
drop table temp_num
select count(*) from
(
select * from app_coupon_api where website = '{$website}' and status = 'public' and UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) > (UNIX_TIMESTAMP(now())-$allDecrese)
union
select * from app_coupon_api where website = '{$website}' and status = 'public' and UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) = 0
union
select * from app_coupon_api where website = '{$website}' and status = 'public' and end_date is null
)t
create table temp_num as select t.website,count(t.website) as count from
( select website from app_coupon_api
where website in (select website from app_coupon_brand
) and status = 'public' and publictime < now()-86400)t GROUP BY t.website order by count(t.website) desc;
select * from app_coupon_api where website = 'na06.mypinpointe.com'
create table temp_num as select t.website,count(t.website) as count from
( select website from app_coupon_api
where website in (select website from app_coupon_brand
) and status = 'public'
and ( UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y'))
> (UNIX_TIMESTAMP(now())-0)
or UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) = 0
or end_date is null ) ) t GROUP BY t.website order by count(t.website) desc;
update app_rank t set test = (select count from temp_num t1 where t1.website=t.website )
select * from app_rank a,temp_num b where a.website = b.website
update app_rank a left join temp_num b on a.website = b.website
set a.test = b.count
update app_rank set test='';
select * from app_rank a left join temp_num b on a.website = b.website
select count(*) from ( select * from app_coupon_api where website = 'chinabuye.com' and status = 'public' and UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) > (UNIX_TIMESTAMP(now())-0) union select * from app_coupon_api where website = 'chinabuye.com' and status = 'public' and UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y')) = 0 union select * from app_coupon_api where website = 'chinabuye.com' and status = 'public' and end_date is null )t
select count(*) FROM
(
select * from app_coupon_api
where website = 'chinabuye.com' and status = 'public'
and UNIX_TIMESTAMP(STR_TO_DATE(left(end_date,10), '%m/%d/%Y'))
< (UNIX_TIMESTAMP(now())-0)
)t
SELECT pid from app_rank where pid in(select pid from app_rank) and pid != 0 group by pid HAVING count(pid)>99;
select website,id,pid,count(*) from app_rank where pid in (
SELECT pid from app_rank where pid in(select pid from app_rank) and pid != 0 group by pid HAVING count(pid)>99
) group by pid having count(*)>499
select DATE_FORMAT(created, '%Y-%m-%d') date, sum(1) total_count, sum(if(type_id=1,1,0)) rqt_count, sum(if(type_id = 3, 1, 0)) product_count from entity group by date ;