常用mysql sql语句整理

1.查询近一个月,近一年的数据

//近一个月
SELECT * FROM test WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) < date(create_time)

//近一年
SELECT * FROM test WHERE DATE_SUB(CURDATE(), INTERVAL 1 YEAR) < date(create_time)

2.GROUP_CONCAT() 排序

SELECT GROUP_CONCAT(val ORDER BY val desc) vals FROM test

3.普通排名

SELECT
	t.*, @rank := @rank + 1 AS 排名
FROM
	(SELECT @rank := 0) r,
	test AS t
ORDER BY
	t.val DESC;

4.并列排名

	SELECT
	t.*,
	CASE
		WHEN @score = t.val THEN  @rank
		WHEN @score := t.val THEN @rank :=@rank + 1
	END AS 排名
FROM
	(
		SELECT
			*
		FROM
			test
		ORDER BY
			val DESC
	) AS t,
	(
		SELECT
			@rank := 0 ,@score := NULL
	) r

5.根据多个字段排序

//先按a排序 ,a一样的话再按b排序
SELECT * FROM test ORDER BY a DESC,b DESC

6.count()函数加条件

//count()函数中使用条件表达式加or null来实现，作用就是当条件不满足时，函数变成了count(null)不会统计数量
//不加 OR NULL 统计的是全部
SELECT COUNT(a=1 OR NULL) val FROM test

7.判断2个逗号分割的字符串是否有交集

select count(*) from test where concat(",", '1,2,3', ",") regexp concat(",", replace('3,4,5,6',',',',|,'),",")