写了几个难一点的sql

最新推荐文章于 2024-06-29 14:10:39 发布

、sakura

最新推荐文章于 2024-06-29 14:10:39 发布

阅读量2.6k

点赞数 9

文章标签： sql 数据库

本文链接：https://blog.csdn.net/sakuraSD/article/details/136839786

版权

文章详细地展示了使用SQL查询对教学资料（包括不同类型和修改时间范围）进行分类和计数的方法，涉及基本书籍节点、关联文档、版本、学科等多个表的联接操作。

摘要由CSDN通过智能技术生成

写了几个难一点的sql

SELECT
bn.id AS book_node_id,
t.version_id,
bn.textbook_id,
s.id AS subject_id,
s.stage_id,
COUNT( CASE WHEN d.document_type_id = 1 AND d.scope IS NULL AND p.document_id IS NOT NULL THEN 1 END ) AS type_1_count,
COUNT(
CASE

WHEN d.document_type_id = 1 
AND d.scope IS NULL 
AND p.document_id IS NOT NULL 
AND d.modified_time >= '2023-03-14' 
AND d.modified_time <= '2024-03-14' THEN
1 
END 
) AS type_1_count_year,
COUNT( CASE WHEN d.document_type_id = 2 AND d.scope IS NULL AND p.document_id IS NOT NULL THEN 1 END ) AS type_2_count,
COUNT(
CASE

WHEN d.document_type_id = 2 
AND d.scope IS NULL 
AND p.document_id IS NOT NULL 
AND d.modified_time >= '2023-03-14' 
AND d.modified_time <= '2024-03-14' THEN
1 
END 
) AS type_2_count_year,
COUNT( CASE WHEN d.document_type_id = 3 AND d.scope IS NULL AND p.document_id IS NOT NULL THEN 1 END ) AS type_3_count,
COUNT(
CASE

WHEN d.document_type_id = 3 
AND d.scope IS NULL 
AND p.document_id IS NOT NULL 
AND d.modified_time >= '2023-03-14' 
AND d.modified_time <= '2024-03-14' THEN
1 
END 
) AS type_3_count_year,
COUNT( CASE WHEN d.document_type_id = 4 AND d.scope IS NULL AND p.document_id IS NOT NULL THEN 1 END ) AS type_4_count,
COUNT(
CASE

WHEN d.document_type_id = 4 
AND d.scope IS NULL 
AND p.document_id IS NOT NULL 
AND d.modified_time >= '2023-03-14' 
AND d.modified_time <= '2024-03-14' THEN
1 
END 
) AS type_4_count_year,
COUNT(
CASE

WHEN d.document_type_id IN ( 5, 6 ) 
AND d.scope IS NULL 
AND p.document_id IS NOT NULL THEN
1 
END 
) AS type_5_count,
COUNT(
CASE
	
	WHEN d.document_type_id IN ( 5, 6 ) 
	AND d.scope IS NULL 
	AND p.document_id IS NOT NULL 
	AND d.modified_time >= '2023-03-14' 
	AND d.modified_time <= '2024-03-14' THEN
		1 
	END 
	) AS type_5_count_year,
	COUNT( CASE WHEN d.scope IS NOT NULL AND p.document_id IS NOT NULL THEN 1 END ) AS paper_count,
	COUNT(
	CASE
			
			WHEN d.scope IS NOT NULL 
			AND p.document_id IS NOT NULL 
			AND d.modified_time >= '2023-03-14' 
			AND d.modified_time <= '2024-03-14' THEN
				1 
			END 
			) AS paper_count_year,
			COUNT( CASE WHEN r.id IS NOT NULL AND p.document_id IS NOT NULL THEN 1 END ) AS all_count,
			COUNT( CASE WHEN d.modified_time >= '2023-03-14' AND d.modified_time <= '2024-03-14' AND p.document_id IS NOT NULL THEN 1 END ) AS all_count_year 
		FROM
			basic_book_node bn
			LEFT JOIN basic_relation_document_book_node r ON bn.id = r.book_node_id
			LEFT JOIN basic_document d ON r.document_id = d.id
			LEFT JOIN basic_textbook t ON bn.textbook_id = t.id
			LEFT JOIN basic_version v ON t.version_id = v.id
			LEFT JOIN basic_subject s ON v.subject_id = s.id
			LEFT JOIN nrsc_document_publish_state p ON d.id = p.document_id 
		WHERE
			d.document_source IS NULL 
			AND d.is_deleted = 0 
	GROUP BY
bn.id

SELECT COALESCE
	( cd.count, 0 ) AS count,
	kp.id,
	kp.NAME,
	ty.id,
	ty.NAME 
FROM
	(
	SELECT
		a.id AS ab_point_id,
		b.id AS ab_type_id 
	FROM
		( SELECT id FROM zj_basic_knowledge_point WHERE basic_subject_id = 27 ) a
		INNER JOIN ( SELECT id FROM zj_basic_question_type WHERE subject_id = 27 ) b 
	) AS ab
	LEFT JOIN (
	SELECT
		COUNT( c.id ) AS count,
		t.id AS type_id,
		t.NAME AS type_name,
		d.id AS knowledge_point_id,
		d.NAME AS knowledge_point_name 
	FROM
		zj_jkx_basic_relation_question_knowledge_point p
		LEFT JOIN zj_jkx_basic_question c ON p.question_id = c.id
		LEFT JOIN zj_basic_knowledge_point d ON p.knowledge_point_id = d.id
		LEFT JOIN zj_basic_question_type t ON c.question_type_id = t.id 
	GROUP BY
		t.id,
		d.id 
	) AS cd ON ab.ab_point_id = cd.knowledge_point_id 
	AND ab.ab_type_id = cd.type_id
	LEFT JOIN zj_basic_question_type ty ON ab.ab_type_id = ty.id
	LEFT JOIN zj_basic_knowledge_point kp ON ab.ab_point_id = kp.id