dev/常用SQL语句/报表sql/platform_buisness_data_record.sql

    <select id="searchBuisnessDate" resultType="java.util.HashMap" parameterType="String">
        SELECT
            (SELECT COUNT(1)
             FROM base_project_info
             WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR(update_date >= #{startTime} AND update_date &lt; #{endTime})) AS '项目资源',

                ((SELECT COUNT(1)
                  FROM info_journal
                  WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date&lt; #{endTime}))
                    +
                 (SELECT COUNT(1)
                 FROM cms_information_info
                 WHERE business_type = "custom_service" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    +
                 (SELECT COUNT(1)
                 FROM cms_information_info
                 WHERE business_type = "help_center" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    +
                 (SELECT COUNT(1)
                 FROM cms_information_info
                 WHERE business_type = "information" AND create_date >= #{startTime} AND create_date &lt; #{endTime})) AS '资讯数据',

                ((
                    SELECT COUNT(1)
                    FROM base_country_info
                    WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                 (
                     SELECT COUNT(1)
                     FROM cms_information_info
                     WHERE business_type = "countryShow" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    ) AS '国别信息',

                (
                    (SELECT COUNT(1)
                    FROM base_unit_info
                    WHERE (industry_dict IN ('04','05','06','09','11','01','02')
                        AND create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (industry_dict IN ('04','05','06','09','11','01','02')
                        AND update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                    (SELECT COUNT(1)
                     FROM cms_information_info
                     WHERE business_type = "unitShow" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    ) AS '企业展示',

                (
                        (
                            SELECT COUNT(1)
                            FROM base_financial_institution_info
                            WHERE  (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_investment_guidelines_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_project_evaluation_report_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_research_result_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM cms_information_info
                            WHERE business_type = "financial_institutions" AND create_date >= #{startTime} AND create_date &lt; #{endTime}
                            )
                    ) AS '金融服务',

                (SELECT COUNT(1)
                 FROM base_unit_info
                 WHERE (industry_dict IN ('08')
                     AND create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (industry_dict IN ('08')
                     AND update_date >= #{startTime} AND update_date &lt; #{endTime})) AS '智库咨询',

                (
                        (SELECT COUNT(1)
                         FROM base_member_info
                         WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM base_league_member
                         WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM base_cooperation_unit
                         WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM coop_custom_service
                         WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_members" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_the_alliance" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_the_cooperative_unit" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    )AS '合作网络',
                (
                    SELECT COUNT(1)
                    FROM activity_info
                    WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})
                    )AS '合作交流',

                (
                        (

                            SELECT COUNT(1)
                            FROM committee_basis_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM committee_activity_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM cms_information_info
                            WHERE business_type = "organizational_structure" AND create_date >= #{startTime} AND create_date &lt; #{endTime})
                    ) AS '区域和国家委员会',
                (
                        (
                            SELECT COUNT(1)
                            FROM base_research_result_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM base_research_resources_info
                            WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM cms_information_info
                            WHERE business_type = "base_research_resource_info" AND create_date >= #{startTime} AND create_date &lt; #{endTime}
                            )
                    ) AS '资源共享',

                (
                    SELECT COUNT(1)
                    FROM meeting_basic_info
                    WHERE (create_date >= #{startTime} AND create_date &lt; #{endTime}) OR (update_date >= #{startTime} AND update_date &lt; #{endTime})) AS '会议信息'

        FROM DUAL;
    </select>
==========================================
    <select id="searchBuisnessDateYesterday" resultType="java.util.HashMap" parameterType="String">
        SELECT
            (SELECT COUNT(1)
             FROM base_project_info
             WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})) AS '项目资源',

                ((SELECT COUNT(1)
                  FROM info_journal
                  WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))
                    +
                 (SELECT COUNT(1)
                  FROM cms_information_info
                  WHERE business_type = "custom_service" AND create_date &lt; #{endTime})
                    +
                 (SELECT COUNT(1)
                  FROM cms_information_info
                  WHERE business_type = "help_center" AND create_date &lt; #{endTime})
                    +
                 (SELECT COUNT(1)
                 FROM cms_information_info
                 WHERE business_type = "information" AND create_date &lt; #{endTime})) AS '资讯数据',

                ((
                    SELECT COUNT(1)
                    FROM base_country_info
                    WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                    ) +
                (
                    SELECT COUNT(1)
                    FROM cms_information_info
                    WHERE business_type = "countryShow" AND create_date &lt; #{endTime}))AS '国别信息',

                (
                    (SELECT COUNT(1)
                    FROM base_unit_info
                    WHERE (industry_dict IN ('04','05','06','09','11','01','02')
                        AND create_date &lt; #{endTime}) OR (industry_dict IN ('04','05','06','09','11','01','02')
                        AND update_date &lt; #{endTime}))+
                    (SELECT COUNT(1)
                     FROM cms_information_info
                    WHERE business_type = "unitShow" AND create_date &lt; #{endTime})
                    ) AS '企业展示',

                (
                        (
                            SELECT COUNT(1)
                            FROM base_financial_institution_info
                            WHERE  (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_investment_guidelines_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_project_evaluation_report_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM base_research_result_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                        )+
                        (
                            SELECT COUNT(1)
                            FROM cms_information_info
                            WHERE business_type = "financial_institutions" AND create_date &lt; #{endTime}
                        )
                    ) AS '金融服务',

                (SELECT COUNT(1)
                 FROM base_unit_info
                 WHERE (industry_dict IN ('08')
                     AND create_date &lt; #{endTime}) OR (industry_dict IN ('08')
                     AND update_date &lt; #{endTime})) AS '智库咨询',

                (
                        (SELECT COUNT(1)
                         FROM base_member_info
                         WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM base_league_member
                         WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM base_cooperation_unit
                         WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM coop_custom_service
                         WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_members" AND create_date &lt; #{endTime})
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_the_alliance" AND create_date &lt; #{endTime})
                        +
                        (SELECT COUNT(1)
                         FROM cms_information_info
                         WHERE business_type = "introduction_to_the_cooperative_unit" AND create_date &lt; #{endTime}
                        )
                    )AS '合作网络',
                (
                    SELECT COUNT(1)
                    FROM activity_info
                    WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})
                    )AS '合作交流',

                (
                        (

                            SELECT COUNT(1)
                            FROM committee_basis_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM committee_activity_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM cms_information_info
                            WHERE business_type = "organizational_structure" AND create_date &lt; #{endTime})
                    ) AS '区域和国家委员会',
                (
                        (
                            SELECT COUNT(1)
                            FROM base_research_result_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))+
                        (
                            SELECT COUNT(1)
                            FROM base_research_resources_info
                            WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime}))+
                        (
                        SELECT COUNT(1)
                        FROM cms_information_info
                        WHERE business_type = "base_research_resource_info" AND create_date &lt; #{endTime}
                        )
                    ) AS '资源共享',

                (
                    SELECT COUNT(1)
                    FROM meeting_basic_info
                    WHERE (create_date &lt; #{endTime}) OR (update_date &lt; #{endTime})) AS '会议信息'

        FROM DUAL;

    </select>

=====================================
    <select id="searchYesterday" resultType="java.util.HashMap" parameterType="String">
        SELECT records_time ,update_number_all FROM platform_buisness_data_record WHERE records_time >= #{startTime} AND records_time &lt; #{endTime} GROUP BY STR_TO_DATE(records_time,'%Y-%m-%d');

    </select>
===========================================
  <select id="searchPlatformBuisnessData" resultMap="platformBuisnessDataRecord" parameterType="String">
        SELECT
        <include refid="Base_Column_List"/>
        FROM platform_buisness_data_record t WHERE t.records_time = #{recordTime} AND t.data_name = #{type};
  </select>