尝试查询表以获取基于ID的max,min和max-1值。我需要从子查询b获取所有项目的详细信息,但是大多数列都获得空值 - A.SCAN_ID作为PRIOR_SCAN,不应该是。 我尝试过: 选择a.PROJECT, a.SOLUTION, a.first_scan, a.last_scan, b.PRIOR_SCAN 来自 ( SELECT PROJECT,SOLUTION,MIN(SCAN_ID)first_scan,MAX(SCAN_ID)last_scan 来自PORTAL_CSA.RPT_Scan_Summary GROUP BY PROJECT,SOLUTION)a LEFT OUTER JOIN ( SELECT A.SOA_SECTOR,A.PROJECT,A.SOLUTION,A.SCAN_ID AS PRIOR_SCAN FROM (SELECT DISTINCT SOA_SECTOR,PROJECT,SOLUTION,SCAN_ID , DENSE_RANK()OVER(按项目划分,按SCAN_ID DESC排序)作为PRIOR_SCAN_I D FROM PORTAL_CSA.RPT_Scan_Summary)A WHERE A.PRIOR_SCAN_ID = 2)b ON b.PROJECT = a.PROJECT AND b.SOLUTION = a.SOLUTION
Am trying to query a table to get max,min and max-1 values based on IDs.Here i need to get the details of all projects from the subquery 'b' but am getting null values for most of the columns -A.SCAN_ID AS PRIOR_SCAN which is not supposed to be. What I have tried: select a.PROJECT, a.SOLUTION, a.first_scan, a.last_scan, b.PRIOR_SCAN from ( SELECT PROJECT,SOLUTION,MIN(SCAN_ID)first_scan, MAX(SCAN_ID)last_scan FROM PORTAL_CSA.RPT_Scan_Summary GROUP BY PROJECT,SOLUTION)a LEFT OUTER JOIN ( SELECT A.SOA_SECTOR, A.PROJECT,A.SOLUTION, A.SCAN_ID AS PRIOR_SCAN FROM (SELECT DISTINCT SOA_SECTOR, PROJECT,SOLUTION, SCAN_ID, DENSE_RANK() OVER (PARTITION BY PROJECT,SOLUTION ORDER BY SCAN_ID DESC) AS PRIOR_SCAN_ID FROM PORTAL_CSA.RPT_Scan_Summary) A WHERE A.PRIOR_SCAN_ID = 2 )b ON b.PROJECT =a.PROJECT AND b.SOLUTION =a.SOLUTION
推荐答案选择没有子查询的SQL中每个组的最大值,最小值,最后一行 [ ^ ] 在一个SQL语句中选择最大行数和最小行数 [ ^ ] 选择min和最大值以及使用T-SQL的其他SQL Server列 [ ^ ] Select max, min, last row for each group in SQL without a subquery[^] Selecting Max and Min Rows in one SQL Statement[^] Select min and max values along with other SQL Server columns using T-SQL[^]
更多推荐
查询以在单个查询中获取max,min和max
发布评论