Basic article
Query time, friendly hint $sql = "Select Date_format (create_time, '%y-%m-%d ') as day from table_name";//int timestamp type $sql = "Select From_unix Time (Create_time, '%y-%m-%d ') as day from table_name ";//A SQL returns multiple totals $sql =" SELECT count (*) all, "; $sql. = "Count (case is status = 1 then status end) Status_1_num,"; $sql. = "Count (case where 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 Ibid. The statement that replaces the contents of a field $sql = "UPDATE table_name SET content = replace (content, ' AAA ', ' BBB ')"; $sql. = "WHERE (content like '%aaa% ')";//Gets the data of a field in a table containing a string $sql = "SELECT * from ' table name ' where LOCATE (' keyword ', field name)";//Gets the first 4 in the field Bit $sql = "Select SUBSTRING (field name, 1,4) from table name"//Find extra duplicate record in table///single Field $sql = "SELECT * from table name where field name in"; $sql. = "(select field name FROM table name Group By field name having count (field name) > 1) ";//multiple Fields $sql =" SELECT * from table name alias where (alias. Field 1, alias. Field 2) in "; $sql. = "(select field 1, Field 2 from Table name Group By field 1, Field 2 have count (*) > 1)";//delete redundant duplicate records in table (minimum of left ID)//single Field $sql = "Delete from Table name where field name in "; $sql. = "(select field name from Table name Group By field name having count (field name) > 1) "; $sql. = "and primary key ID not in"; $sql. = "(select min (primary key ID) from table name Group By field name having count (field name) >1)";//multiple fields $sql = "Delete from table name alias where (alias. Field 1, don't Name. Field 2) in "; $sql. = "(select field 1, Field 2 from Table name Group By field 1, Field 2 having count (*) > 1)"; $sql. = "and primary key ID not in"; $sql. = "(select min (primary key ID) from table name Group By field 1, Field 2 having count (*) >1)";
Business Chapter
Continuous range issues
Creating a test Table create table ' Test_number ' (' id ' int () unsigned not null auto_increment, ' number ' int (one) unsigned NOT null DE FAULT ' 0 ' COMMENT ' number ', PRIMARY KEY (' id ')) engine=innodb DEFAULT charset=utf8//Create test data insert into Test_number values ( ); 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);
Experimental objective: To find the continuous range of numbers.
According to the above data, the range should be obtained.
1-35-57-810-11//performs Sqlselect min (number) Start_range,max (number) End_rangefrom (select Number,rn,number-rn diff from (select number, @number: [email protected]+1 rn from Test_number, (select @number: =0) as number] b) C GROUP by D Iff
Continuous range of numbers
Sign in question
Create a reference table (required for simulation data) CREATE TABLE ' Test_nums ' ( ' id ' int (one) unsigned not NULL auto_increment, primary KEY (' id ') Engine=innodb DEFAULT Charset=utf8 comment= ' reference table ';//analog data, insert 1-10000 continuous data.//Create a Test table ' Test_sign_history ' (& nbsp; ' id ' int (ten) unsigned NOT NULL auto_increment, ' uid ' int (one) unsigned not null DEFAULT ' 0 ' COMMENT ' user ID ', &nbs p; ' Create_time ' timestamp not NULL DEFAULT current_timestamp COMMENT ' sign-in time ', primary KEY (' id ')) engine=innodb DEFA ULT Charset=utf8 comment= ' Registration History table ';//create test data 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) Minutefrom test_nums where id<31;// Statistics daily user Check-in status 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 create_time= ' 2016-12-1 3 ' then C else 0 end] 13Sign, sum (case Create_time= ' 2016-12-14 ' then C else 0 end) 14Sign, sum if 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) 17Signfrom ( select date_format (Create_time, ' %y-%m-%d ') Create_time, hour (create_time) H, count (*) c &NBSP ; from Test_sign_history group by date_format (create_time, '%y-%m-%d '), & nbsp hour (Create_time)) a group by H with Rollup;
Count the daily user sign-in per hour
Statistics per hour of daily user attendance (when there is no data for an hour, show 0) Select h, sum (case time 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 CR Eate_time= ' 2016-12-13 ' then C else 0 end] 13Sign, sum (case if create_time= ' 2016-12-14 ' then C else 0 end) 1 4Sign, sum (case is create_time= ' 2016-12-15 ' then C else 0 end) 15Sign, sum (case when Create_ti Me= ' 2016-12-16 ' then C else 0 end] 16Sign, sum (case when create_time= ' 2016-12-17 ' then C else 0 end) 17SIGNFR Om ( select b.h h,c.create_time,c.c from ( select id-1 h from Test_nu Ms where id<=24 ) b LEFT join ( select &NBS P Date_format (create_time, '%y-%m-%d ') create_time, hour (Create_time) H, &N Bsp CounT (*) c from test_sign_history group by D Ate_format (create_time, '%y-%m-%d '), hour (create_time) ) C on (b.h=c.h)) a GROUP by H with Rollup;
To count the daily user check-in status (0 if there is no data for an hour)
Statistics daily user check-in data and daily incremental data select type, sum (case when Create_time= ' 2016-12-11 ' then C else 0 end] 11Sign, sum (case if 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, &NBSP ; sum (case is create_time= ' 2016-12-14 ' then C else 0 end) 14Sign, sum (case when Create_ti Me= ' 2016-12-15 ' then C else 0 end) 15Sign, sum if create_time= ' 2016-12-16 ' then C else 0 End) 16Sign, sum (case is create_time= ' 2016-12-17 ' then C else 0 end) 17Signfrom ( &nbs P select B.create_time,ifnull (b.c-c.c,0) C, ' Increment ' type from ( &NB Sp select date_format (create_time, '%y-%m-%d ') create_time, &NB Sp &NBSP; COUNT (*) c from test_sign_history &NBSP ; group by date_format (create_time, '%y-%m-%d ') ) b left join ( select date_format (create_time, '%y-%m-%d ') create_time, &N Bsp COUNT (*) c from test_sign_history group By date_format (create_time, '%y-%m-%d ') ) C on (b.creat E_time=c.create_time+ Interval 1 day) union all select Date_format (create_time, '%y-%m-%d ') create_time, COUNT (*) C, ' Cur Rent ' &NBsp from test_sign_history group by Date_format (create_tim E, '%y-%m-%d ')) a group by type Order by case when type= ' current ' then 1 else 0 end desc;
Statistics daily user check-in data and daily incremental data
Simulate different users sign in for different days 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;//count the number of users in same number select s Um (case-Day=1 then CN-Else 0 end) 1Day, sum (case is day=2 then cn else 0 end) 2Day, sum (c ASE when Day=3 and cn else 0 end) 3Day, sum (case if Day=4 then CN else 0 end) 4Day, sum (case When Day=5 then CN else 0 end) 5Day, sum (case is day=6 then cn else 0 end) 6Day, sum Day=7 then CN-Else 0 end) 7Day, sum (case is day=8 then cn else 0 end) 8Day, sum =9 then CN-Else 0 end) 9Day, sum (case if day=10 then CN else 0 End) 10Dayfrom ( select c Day,co UNT (*) cn from ( select uid,count (*) c from test_sign_history Group B Y uid ) a group by c) b;
Count the number of users who have the same number of sign-in days
Count the consecutive check-in times for each user select * FROM ( select d.*, @ggid: = @cggid, @cggid: = D.uid, if (@ggid = @cggid, @grank: = @grank + 1, @grank: = 1) grank from ( & Nbsp;select uid,min (c.create_time) begin_date, Max (c.create_time) end_date,count (*) count from & nbsp ( select b.*, @gid: = @cgid, @cgid: = B.uid, &N Bsp if (@gid = @cgid, @rank: = @rank + 1, @rank: = 1) rank, &NBSP;[EMAIL&NBSP;PROTECTE D] Flag from ( select &NB Sp distinct uid, &NBSP ; 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 UI D,flag order by Uid,count (*) desc ) d, (SELECT @ggid: = 1, @cggid: = 1, @grank: = 1 ) as E) F where grank=1;
Count the consecutive sign-in times for each user
If you need to download the above related data sheet, to test.
You can get the data sheet by following the public number and replying to the "check-in Data Sheet".
Thanks ~
Mysql Common SQL statement Collection