实用的mysql语句

1.查询在一个表中符合查询条件的数据大于1条的记录

select UserId,count(UserId) as count from signtables where creatime='2017-09-28' and platform=0 group by UserId having count(1)>1;

2.在一个表中按日期查询，每个不同的值有多少条记录

SELECT 
DATE_FORMAT(creatime,'%Y-%m-%d') days,
sum(case when os=1 then 1 else 0 end ) as IOS,
sum(case when os=0 then 1 else 0 end ) as Android
FROM newdevice group by days order by days DESC;

3.插入数据库中好几条同样的数据，可以用下面的sql语句，在插入时会检查的

insert into user (time,name,nickname,male) select '".$time."','".$name."','".$nickname."','".$male."' from dual where not exists (select * from user where name='".$name."') limit 1

4.每天的注册人数

SELECT DATE_FORMAT(creatime,'%Y-%m-%d') days,count(userid) as total FROM user where source=1 group by days order by days DESC;

5. 提取数据库中字符串的数字

select sum(-(-rewards)) sum from user where creatime >'2018-03-26' and -(-rewards)>40;

6.提取数据库中字符串的数字并相加

SELECT
sum((CASE WHEN -(-rewards)>40 THEN -(-rewards) ELSE 0 END)) AS score,
sum((CASE WHEN -(-rewards)>1 and -(-rewards)<40 THEN -(-rewards) ELSE 0 END)) AS goldcoin
FROM user WHERE creatime >'2018-03-26';

7.通过截取的文字来相加积分和金币

SELECT
sum((CASE WHEN right(rewards, 2)='积分' THEN -(-rewards) ELSE 0 END)) AS score,
sum((CASE WHEN right(rewards, 2)='金币' THEN -(-rewards) ELSE 0 END)) AS goldcoin
FROM user WHERE creatime >'2018-03-26';

8.laravel框架的通过截取的文字来相加积分和金币

SELECT
sum((CASE WHEN right(rewards, 2)='".iconv('utf-8','gbk','积分')."' THEN -(-rewards) ELSE 0 END)) AS score,
sum((CASE WHEN right(rewards, 2)='".iconv('utf-8','gbk','金币')."' THEN -(-rewards) ELSE 0 END)) AS goldcoin
FROM user WHERE DATEDIFF(creatime,NOW())=-1;

9.排名

SELECT @rownum:=@rownum+1 rownum,t.userid,t.nickname,t.score,t.total,t.Head FROM (SELECT   @rownum:=0,a1.userid,a2.nickname,a2.Head, a1.score,(a1.win + a1.lose +a1.draw)  total,a1.win, a1.lose,a1.draw,a1.escape,a1.creatime,a1.level FROM hourrecord a1 LEFT JOIN user a2 ON a1.userid=a2.userid WHERE a1.creatime>='".$time."' and (a1.win + a1.lose +a1.draw)>=10 and a1.type=".$MatchType." ORDER BY a1.score DESC,(a1.win + a1.lose +a1.draw) DESC,a1.win DESC,a1.gamelevel DESC,a1.creatime DESC LIMIT 0,20) t

10.查询指定某人的排名，如果分数相同则并列

SELECT total.* FROM(SELECT obj.*,CASE WHEN @rowtotal = obj.score THEN @rownum WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN
@rownum :=@rownum + 1 END AS rownum FROM(SELECT * FROM bill ORDER BY score DESC) AS obj,(SELECT @rownum := 0 ,@rowtotal := NULL) r) AS total WHERE total.userid = 200000;

11.查询每个班级中及格人数和不及格人数

select class,sum(if(score>=60,1,0) jige,sum(if(score<60,1,0)) bujige from student group by class;

12.查询后返回值如果是null则返回0

SELECT IF(SUM(money) IS NULL,0,money) as money FROM trades WHERE touserid IN(SELECT userid FROM users WHERE source=1 AND creatime >'2018-04-18' AND creatime<'2018-04-19')

13.查询渠道为25的2022年3月1日以后的每日日活人数 

 SELECT
 	count(DISTINCT(game_roomrecord.userid)) ,DATE_FORMAT( logintime, '%Y-%m-%d' )
 FROM
 	users join game_roomrecord on users.UserId=game_roomrecord.userid
 WHERE
 	source=25 and 
 	logintime>'2022-03-01' GROUP BY DATE_FORMAT( logintime, '%Y-%m-%d' )
;