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' )
;