避免groupby子句中的列

tzdcorbm  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(395)

在这里,我需要避免GROUPBY子句中的这两列,因为在GROUPBY子句中,与预期值相比,它返回的记录数更多。
实际上查询结果来自 st.cnt 返回3条记录,但当我们以 SP ,我得到了预期的两倍。
st\u cnt.count\u文件,
扫描次数,

SELECT table1.DA_SEC,
  table1.DA_REG,
  table1.PROJ,
  table1.SOL,
  table1.SCAN_YEAR_MON,
  to_char(TO_DATE(table1.SCAN_YEAR_MON, 'YYYY-MM'), 'YYYY'),
  to_char(TO_DATE(table1.SCAN_YEAR_MON, 'YYYY-MM'), 'MM'),
  COUNT(DISTINCT table1.ISSUE_ID),
  COUNT(DISTINCT table1.USERNAME),
  COUNT(DISTINCT table1.PATH),
  st_cnt.COUNT_FILES,
  st_cnt.COUNT_SCANS,
  sum(unq_Pro.HIGH_CNT),
  sum(unq_Pro.MEDIUM_CNT),
  sum(unq_Pro.LOW_CNT),
  sum(SUPPR_HIGH) - sum(UNSUPPR_HIGH),
  sum(SUPPR_MEDIUM) - sum(UNSUPPR_MEDIUM),
  sum(SUPPR_LOW) - sum(UNSUPPR_LOW),
  CURRENT_DATE
FROM table1
JOIN
  (SELECT PROJ, SOL, SCAN_YEAR, SCAN_MONTH, COUNT(DISTINCT PATH) COUNT_FILES, COUNT(DISTINCT SCAN_ID) COUNT_SCANS FROM table2 GROUP BY PROJ, SOL, SCAN_YEAR, SCAN_MONTH) st_cnt
ON st_cnt.PROJ = table1.PROJ--AND st_cnt.SOL = table1.SOL
GROUP BY table1.DA_SEC, table1.DA_REG, table1.PROJ, table1.SOL, table1.SCAN_YEAR_MON, st_cnt.COUNT_FILES, st_cnt.COUNT_SCAN;
qcuzuvrc

qcuzuvrc1#

在这种情况下,大量时间使用with子句非常有用。下面是ask-tom线程,它讨论with子句ask-tom链接也是一个关于子查询分解的有用链接,使用with子句here子查询分解
它还帮助我们知道表1的数据在本例中是什么样子的,这样我们就可以测试答案。但是你分享的信息可以帮助你

WITH TAB1_DATA AS (
SELECT table1.DA_SEC,
 table1.DA_REG,
 table1.PROJ,
 table1.SOL,
 table1.SCAN_YEAR_MON,
 to_char(TO_DATE(table1.SCAN_YEAR_MON, 'YYYY-MM'), 'YYYY'),
 to_char(TO_DATE(table1.SCAN_YEAR_MON, 'YYYY-MM'), 'MM'),
COUNT(DISTINCT table1.ISSUE_ID),
COUNT(DISTINCT table1.USERNAME),
COUNT(DISTINCT table1.PATH),
sum(unq_Pro.HIGH_CNT),
sum(unq_Pro.MEDIUM_CNT),
sum(unq_Pro.LOW_CNT),
sum(SUPPR_HIGH) - sum(UNSUPPR_HIGH),
sum(SUPPR_MEDIUM) - sum(UNSUPPR_MEDIUM),
sum(SUPPR_LOW) - sum(UNSUPPR_LOW),
CURRENT_DATE
  FROM table1
GROUP BY table1.DA_SEC, table1.DA_REG, table1.PROJ, table1.SOL, table1.SCAN_YEAR_MON
)
 SELECT TD.* ,  st_cnt.COUNT_FILES,st_cnt.COUNT_SCANS FROM TAB1_DATA TD ,
(SELECT PROJ, SOL, SCAN_YEAR, SCAN_MONTH, COUNT(DISTINCT PATH) COUNT_FILES, 
COUNT(DISTINCT SCAN_ID) COUNT_SCANS FROM table2 GROUP BY PROJ, SOL, SCAN_YEAR, 
SCAN_MONTH) st_cnt
WHERE st_cnt.PROJ = TD.PROJ;

相关问题