一个sql查询多个结果、DISTINCT 去重计算总数、CONVERT,保留几位小数
(学习笔记仅供参考)
示例:
一条sql查询多个结果
SELECT
(SELECT COUNT(ITEM_NAME) FROM BIZ_PROJECT_FINISHED WHERE PLAN_YEAR = #{year}) projectFinishedNameTotal ,
(SELECT CONVERT(DECIMAL(18,0),(SUM(FUNDING_PLAN_SUM_TOTAL)/10000)) FROM BIZ_PROJECT_FINISHED WHERE PLAN_YEAR = #{year}) fundingPlanSumTotal ,
(SELECT COUNT(DISTINCT COMPANY_NAME) FROM BIZ_PROJECT_FINISHED WHERE PLAN_YEAR = #{year}) companyNameTotal
示例:
根据条件筛选计算总数后保留n位小数
SELECT CONVERT(DECIMAL(18,0),(SUM(FUNDING_PLAN_SUM_TOTAL)/10000)) FROM BIZ_PROJECT_FINISHED WHERE PLAN_YEAR = #{year}
示例:
根据某个字段去重然后计算总条数
SELECT COUNT(DISTINCT COMPANY_NAME) FROM BIZ_PROJECT_FINISHED WHERE PLAN_YEAR = #{year}