常见mysql sql 技巧一

alonli

　　1，数字辅助表
　　//创建表
　　create table test(id int unsigned not null primary key);
　　delimiter //
　　create procedure pnum(cnt int unsigned)
　　begin
　　declare i int unsigned default 1;
　　insert into num  select i;
　　while i*2 < cnt do
　　insert into num select i+id from num ;
　　set i=i*2;
　　end while;
　　end
　　//
　　delimiter ;
　　#####列值不连续问题：
　　表a中id值为1，2,3,100,101,110，111
　　set @q=0;

　　select>　　#####对不连续的进行分组
　　set @a=0;
　　select min(id) as start_v,max(id) as end_v from (

　　select>
　　select>　　#####对不连续的值填充
　　use test;
　　DROP TABLE if EXISTS pincer;
　　create table pincer(a int UNSIGNED);
　　insert into pincer values(1),(2),(5),(100),(101),(103),(104),(105);
　　select a+1 as start ,(select min(a)-1 from pincer as ww where ww.a>qq.a) as end from pincer as qq where
　　not exists (select * from pincer as pp where qq.a+1=pp.a)
　　and a　　################
　　2,生日问题
　　select  name,birthday,if(cur>today,cur,next) as birth_day
　　from(
　　select name,birthday,today,date_add(cur,interval if(day(birthday)=29 && day(cur)=28,1,0) day)as cur, date_ad(next,interval if(day(birthday)=29 && day(next)=28,1,0) day) as next
　　from(
　　select name,birthday,today,
　　date_add(birthday,interval diff year) as  cur,
　　date_add(birthday,interval diff+1 year) as next,
　　from(
　　select concat(laster_name,'',first_name) as name,
　　birth_date as birthday,
　　(year(now())-year(birth_date) )as diff,
　　now() as today
　　from employees) as a
　　) as b
　　) as c
　　3,日期问题----计算工作日
　　create table sals(id int ,date datetime ,cost int,primary key(id);
　　select date_add('1900-01-01',
　　interval floor(datediff(date,'1900-01-01')/7)*7 day)
　　as week_start,
　　date_add('1900-01-01',
　　interval floor(datediff(date,'1900-01-01')/7*7+6 day)
　　as week_end,
　　sum(cost) from sales;
　　计算工作日（指定2个日期段 有多少工作日）
　　create procedure pgetworkdays (s datetime,e datetime)
　　begin
　　select floor(days/7)*5+days%7
　　case when 6 between wd and wd+days%7-1 then 1 else 0 end
　　case then 7 between wd and wd+days%7-1 then 1 else 0 end
　　from
　　(select datediff(e,s)+1 as days,weekday(s)+1 as wd) as a;
　　end;