基礎篇
//查詢時間，友好提示
$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返回多個總數
$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 同上。
複製程式碼//替換某欄位的內容的語句
$sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
$sql .= " where (content like '%aaa%')";
複製程式碼//獲取表中某欄位包含某字串的資料
$sql = "SELECT * FROM `表名` WHERE LOCATE('關鍵字', 欄位名) ";
複製程式碼//獲取欄位中的前4位
$sql = "SELECT SUBSTRING(欄位名,1,4) FROM 表名 ";
複製程式碼//查詢表中多餘的重複記錄
//單個欄位
$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 )";
複製程式碼//刪除表中多餘的重複記錄(留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) ";
複製程式碼業務篇
- 連續範圍問題
//建立測試表
CREATE TABLE `test_number` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`number` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '數字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
複製程式碼//建立測試資料
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);
複製程式碼實驗目標：求數字的連續範圍。
根據上面的資料，應該得到的範圍。
1-3
5-5
7-8
10-11
複製程式碼//執行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;
複製程式碼
- 簽到問題
//建立參考表(模擬資料需要用到)
CREATE TABLE `test_nums` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='參考表';
//模擬資料，插入 1-10000 連續資料.
複製程式碼//建立測試表
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='簽到歷史表';
複製程式碼//建立測試資料
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;
複製程式碼//統計每天的每小時使用者簽到情況
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
date_format(create_time,'%Y-%m-%d') create_time,
hour(create_time) h,
count(*) c
from test_sign_history
group by
date_format(create_time,'%Y-%m-%d'),
hour(create_time)
) a
group by h with rollup;
複製程式碼
//統計每天的每小時使用者簽到情況(當某個小時沒有資料時，顯示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_history
group by
date_format(create_time,'%Y-%m-%d'),
hour(create_time)
) c on (b.h=c.h)
) a
group by h with rollup;
複製程式碼
//統計每天的使用者簽到資料和每天的增量資料
select
type,
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')
) b
left 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 all
select
date_format(create_time,'%Y-%m-%d') create_time,
count(*) c,
'Current'
from test_sign_history
group by
date_format(create_time,'%Y-%m-%d')
) a
group by type
order by case when type='Current' then 1 else 0 end desc;
複製程式碼
//模擬不同的使用者簽到了不同的天數
insert into test_sign_history(uid,create_time)
select uid,create_time + interval ceil(rand()*10) day from test_sign_history,test_nums
where test_nums.id <10 order by rand() limit 150;
複製程式碼//統計簽到天數相同的使用者數量
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;
複製程式碼
//統計每個使用者的連續簽到時間
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 (
select
distinct
uid,
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,flag
order by uid,count(*) desc
) d,(SELECT @ggid := 1, @cggid := 1, @grank := 1) as e
)f
where grank=1;
複製程式碼
如果大家需要下載上述的相關資料表，進行測試。
可以關注微信公眾號，回覆 “簽到資料表”，即可獲取資料表。
Thanks ~
掘金技術徵文：gold.xitu.io/post/587f0e…