写了几个难一点的sql

写了几个难一点的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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34