查询以在单个查询中获取max，min和max-1 [英] Query to get the max, min and max-1 in a single query

问题描述

尝试查询表以获取基于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-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！