基礎(chǔ)篇

//查詢時間，友好提示 $sql = 'select date_format(create_time, ’%Y-%m-%d’) as day from table_name';

//int 時間戳類型 $sql = 'select from_unixtime(create_time, ’%Y-%m-%d’) as day from table_name';

//一個sql返回多個總數(shù) $sql = 'select count(*) all, ' ;$sql .= ' count(case when status = 1 then status end) status_1_num, ';$sql .= ' count(case when status = 2 then status end) status_2_num ';$sql .= ' from table_name';

//Update Join / Delete Join $sql = 'update table_name_1 ';$sql .= ' inner join table_name_2 on table_name_1.id = table_name_2.uid ';$sql .= ' inner join table_name_3 on table_name_3.id = table_name_1.tid ';$sql .= ' set *** = *** ';$sql .= ' where *** '; //delete join 同上。

//替換某字段的內(nèi)容的語句 $sql = 'update table_name set content = REPLACE(content, ’aaa’, ’bbb’) ';$sql .= ' where (content like ’%aaa%’)';

//獲取表中某字段包含某字符串的數(shù)據(jù) $sql = 'SELECT * FROM `表名` WHERE LOCATE(’關(guān)鍵字’, 字段名) ';

//獲取字段中的前4位 $sql = 'SELECT SUBSTRING(字段名,1,4) FROM 表名 ';

//查找表中多余的重復(fù)記錄 //單個字段 $sql = 'select * from 表名 where 字段名 in ';$sql .= '(select 字段名 from 表名 group by 字段名 having count(字段名) > 1 )'; //多個字段 $sql = 'select * from 表名 別名 where (別名.字段1,別名.字段2) in ';$sql .= '(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1 )';

//刪除表中多余的重復(fù)記錄(留id最小) //單個字段 $sql = 'delete from 表名 where 字段名 in ';$sql .= '(select 字段名 from 表名 group by 字段名 having count(字段名) > 1) ';$sql .= 'and 主鍵ID not in ';$sql .= '(select min(主鍵ID) from 表名 group by 字段名 having count(字段名 )>1) '; //多個字段 $sql = 'delete from 表名 別名 where (別名.字段1,別名.字段2) in ';$sql .= '(select 字段1,字段2 from 表名 group by 字段1,字段2 having count(*) > 1) ';$sql .= 'and 主鍵ID not in ';$sql .= '(select min(主鍵ID) from 表名 group by 字段1,字段2 having count(*)>1) ';

業(yè)務(wù)篇

//創(chuàng)建測試表 CREATE TABLE `test_number` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `number` int(11) unsigned NOT NULL DEFAULT ’0’ COMMENT ’數(shù)字’, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

//創(chuàng)建測試數(shù)據(jù) insert into test_number values(1,1);insert into test_number values(2,2);insert into test_number values(3,3);insert into test_number values(4,5);insert into test_number values(5,7);insert into test_number values(6,8);insert into test_number values(7,10);insert into test_number values(8,11);

實驗?zāi)繕?biāo)：求數(shù)字的連續(xù)范圍。

根據(jù)上面的數(shù)據(jù)，應(yīng)該得到的范圍。

1-3 5-5 7-8 10-11

//執(zhí)行Sql select min(number) start_range,max(number) end_range from ( select number,rn,number-rn diff from (select number,@number:=@number+1 rn from test_number,(select @number:=0) as number ) b) c group by diff;

數(shù)字的連續(xù)范圍

//創(chuàng)建參考表(模擬數(shù)據(jù)需要用到) CREATE TABLE `test_nums` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’參考表’; //模擬數(shù)據(jù)，插入 1-10000 連續(xù)數(shù)據(jù).

//創(chuàng)建測試表 CREATE TABLE `test_sign_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned NOT NULL DEFAULT ’0’ COMMENT ’用戶ID’, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ’簽到時間’, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’簽到歷史表’;

//創(chuàng)建測試數(shù)據(jù) insert into test_sign_history(uid,create_time)select ceil(rand()*10000),str_to_date(’2016-12-11’,’%Y-%m-%d’)+interval ceil(rand()*10000) minute from test_nums where id<31;

//統(tǒng)計每天的每小時用戶簽到情況 select h, sum(case when create_time=’2016-12-11’ then c else 0 end) 11Sign, sum(case when create_time=’2016-12-12’ then c else 0 end) 12Sign, sum(case when create_time=’2016-12-13’ then c else 0 end) 13Sign, sum(case when create_time=’2016-12-14’ then c else 0 end) 14Sign, sum(case when create_time=’2016-12-15’ then c else 0 end) 15Sign, sum(case when create_time=’2016-12-16’ then c else 0 end) 16Sign, sum(case when create_time=’2016-12-17’ then c else 0 end) 17Sign from ( selectdate_format(create_time,’%Y-%m-%d’) create_time,hour(create_time) h,count(*) c from test_sign_history group bydate_format(create_time,’%Y-%m-%d’),hour(create_time)) agroup by h with rollup;

統(tǒng)計每天的每小時用戶簽到情況

//統(tǒng)計每天的每小時用戶簽到情況(當(dāng)某個小時沒有數(shù)據(jù)時，顯示0) select h , sum(case when create_time=’2016-12-11’ then c else 0 end) 11Sign, sum(case when create_time=’2016-12-12’ then c else 0 end) 12Sign, sum(case when create_time=’2016-12-13’ then c else 0 end) 13Sign, sum(case when create_time=’2016-12-14’ then c else 0 end) 14Sign, sum(case when create_time=’2016-12-15’ then c else 0 end) 15Sign, sum(case when create_time=’2016-12-16’ then c else 0 end) 16Sign, sum(case when create_time=’2016-12-17’ then c else 0 end) 17Sign from ( select b.h h,c.create_time,c.c from (select id-1 h from test_nums where id<=24 ) b left join (select date_format(create_time,’%Y-%m-%d’) create_time, hour(create_time) h, count(*) c from test_sign_historygroup by date_format(create_time,’%Y-%m-%d’), hour(create_time) ) c on (b.h=c.h)) agroup by h with rollup;統(tǒng)計每天的每小時用戶簽到情況(當(dāng)某個小時沒有數(shù)據(jù)時，顯示0)

//統(tǒng)計每天的用戶簽到數(shù)據(jù)和每天的增量數(shù)據(jù) selecttype,sum(case when create_time=’2016-12-11’ then c else 0 end) 11Sign,sum(case when create_time=’2016-12-12’ then c else 0 end) 12Sign,sum(case when create_time=’2016-12-13’ then c else 0 end) 13Sign,sum(case when create_time=’2016-12-14’ then c else 0 end) 14Sign,sum(case when create_time=’2016-12-15’ then c else 0 end) 15Sign,sum(case when create_time=’2016-12-16’ then c else 0 end) 16Sign,sum(case when create_time=’2016-12-17’ then c else 0 end) 17Sign from (select b.create_time,ifnull(b.c-c.c,0) c,’Increment’ type from ( select date_format(create_time,’%Y-%m-%d’) create_time, count(*) c from test_sign_history group by date_format(create_time,’%Y-%m-%d’)) bleft join( select date_format(create_time,’%Y-%m-%d’) create_time, count(*) c from test_sign_history group by date_format(create_time,’%Y-%m-%d’)) c on(b.create_time=c.create_time+ interval 1 day) union allselect date_format(create_time,’%Y-%m-%d’) create_time, count(*) c, ’Current’ from test_sign_historygroup by date_format(create_time,’%Y-%m-%d’)) agroup by typeorder by case when type=’Current’ then 1 else 0 end desc;統(tǒng)計每天的用戶簽到數(shù)據(jù)和每天的增量數(shù)據(jù)

//模擬不同的用戶簽到了不同的天數(shù) insert into test_sign_history(uid,create_time)select uid,create_time + interval ceil(rand()*10) day from test_sign_history,test_numswhere test_nums.id <10 order by rand() limit 150;

//統(tǒng)計簽到天數(shù)相同的用戶數(shù)量 select sum(case when day=1 then cn else 0 end) 1Day, sum(case when day=2 then cn else 0 end) 2Day, sum(case when day=3 then cn else 0 end) 3Day, sum(case when day=4 then cn else 0 end) 4Day, sum(case when day=5 then cn else 0 end) 5Day, sum(case when day=6 then cn else 0 end) 6Day, sum(case when day=7 then cn else 0 end) 7Day, sum(case when day=8 then cn else 0 end) 8Day, sum(case when day=9 then cn else 0 end) 9Day, sum(case when day=10 then cn else 0 end) 10Day from ( select c day,count(*) cn from (select uid,count(*) c from test_sign_history group by uid ) a group by c) b;統(tǒng)計簽到天數(shù)相同的用戶數(shù)量

//統(tǒng)計每個用戶的連續(xù)簽到時間 select * from ( select d.*, @ggid := @cggid, @cggid := d.uid, if(@ggid = @cggid, @grank := @grank + 1, @grank := 1) grank from (select uid,min(c.create_time) begin_date ,max(c.create_time) end_date,count(*) count from ( select b.*, @gid := @cgid, @cgid := b.uid, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) rank, b.diff-@rank flag from (selectdistinctuid,date_format(create_time,’%Y-%m-%d’) create_time,datediff(create_time,now()) diff from test_sign_history order by uid,create_time ) b, (SELECT @gid := 1, @cgid := 1, @rank := 1) as a) c group by uid,flagorder by uid,count(*) desc ) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e)fwhere grank=1;統(tǒng)計每個用戶的連續(xù)簽到時間