当前位置：首页 > news >正文

写了几个难一点的sql

news 2025/8/22 6:08:33

写了几个难一点的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(
CASEWHEN 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(
CASEWHEN 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(
CASEWHEN 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(
CASEWHEN 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(
CASEWHEN 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(
CASEWHEN 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' THEN1 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(CASEWHEN 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' THEN1 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 FROMbasic_book_node bnLEFT JOIN basic_relation_document_book_node r ON bn.id = r.book_node_idLEFT JOIN basic_document d ON r.document_id = d.idLEFT JOIN basic_textbook t ON bn.textbook_id = t.idLEFT JOIN basic_version v ON t.version_id = v.idLEFT JOIN basic_subject s ON v.subject_id = s.idLEFT JOIN nrsc_document_publish_state p ON d.id = p.document_id WHEREd.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(SELECTa.id AS ab_point_id,b.id AS ab_type_id FROM( SELECT id FROM zj_basic_knowledge_point WHERE basic_subject_id = 27 ) aINNER JOIN ( SELECT id FROM zj_basic_question_type WHERE subject_id = 27 ) b ) AS abLEFT JOIN (SELECTCOUNT( 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 FROMzj_jkx_basic_relation_question_knowledge_point pLEFT JOIN zj_jkx_basic_question c ON p.question_id = c.idLEFT JOIN zj_basic_knowledge_point d ON p.knowledge_point_id = d.idLEFT JOIN zj_basic_question_type t ON c.question_type_id = t.id GROUP BYt.id,d.id ) AS cd ON ab.ab_point_id = cd.knowledge_point_id AND ab.ab_type_id = cd.type_idLEFT JOIN zj_basic_question_type ty ON ab.ab_type_id = ty.idLEFT JOIN zj_basic_knowledge_point kp ON ab.ab_point_id = kp.id