查询以在单个查询中获取max，min和max

本文介绍了查询以在单个查询中获取max，min和max-1的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

尝试查询表以获取基于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[^]