问题:我要统计本公司每个月投标和中标的个数,而投标和中标的时间是单独维护的,就一直纠结怎么把月份统一到一起
思路:单独对投标和中标进行分组,然后使用sql的union操作符联合两个结果集
sql:
SELECT
SUM(num1),SUM(num2),M
FROM (SELECT
NVL(COUNT(*),0) AS num1,
0 AS NUM2,
TO_CHAR( pp1.TENDER_TIME, 'mm' ) AS m
FROM
P_PROJECT_ENGIN pp1
WHERE
pp1.TENDER_STATUS = 'TB_Y'
AND pp1.TENDER_TIME IS NOT NULL
GROUP BY
TO_CHAR( pp1.TENDER_TIME, 'mm' )
UNION ALL
SELECT
0 AS num1, COUNT(*) AS num2,
TO_CHAR( pp2.TENDER_REVIEW_STIME, 'mm' ) m
FROM
P_PROJECT_ENGIN pp2
WHERE
pp2.TENDER_REVIEW_STIME IS NOT NULL
GROUP BY
TO_CHAR( pp2.TENDER_REVIEW_STIME, 'mm' )) A
GROUP BY a.m