一些统计需求的sql语句

需求一：统计满足条件的各个阶段的行数
需求二：统计满足分类的前n条数据
需求三：关联表修改、删除数据

表结构

需求一：统计满足条件的各个阶段的行数

SELECT
	count( a1 ),
	count( a2 ),
	count( a3 ) 
FROM
	(
SELECT
	( CASE WHEN score > 60 AND score < 75 THEN id END ) AS a1,
	( CASE WHEN score >= 75 AND score < 80 THEN id END ) AS a2,
	( CASE WHEN score > 80 THEN id END ) AS a3 
FROM
	test_type_top 
	) a

统计结果

需求二：统计满足分类的前n条数据

SELECT
	* 
FROM
	test_type_top a 
WHERE
	( SELECT count( 1 ) FROM test_type_top b WHERE a.NAME = b.NAME 
        AND a.create_time < b.create_time ) < 2 
ORDER BY
	id;

统计结果

解析：

SELECT
	a.id,
	a.NAME,
	a.create_time,
	( SELECT count( 1 ) FROM test_type_top b WHERE a.NAME = b.NAME 
        AND a.create_time < b.create_time ) AS '有多少行数据处理时间比当前行大' 
FROM
	test_type_top a
ORDER BY
	a.id;

需求三：关联表修改数据

示例sql：

UPDATE 
	A a,B b 
SET a.user_id = b.id 
WHERE
	a.NAME = b.NAME

需求三：关联表删除数据

删除关联主表的所有表数据
示例sql：

DELETE t1,
t2,
t3 
FROM
	T1 t1
	LEFT JOIN T2 t2 ON t1.id = t2.pid
	LEFT JOIN T3 t3 ON t1.id = t3.pid 
WHERE
	t1.id = 1

---

未完待续…