使用SQL MAXDATE拖动行

Question

我试图从HR表中运行一个查询。我将引入所有共享相同“主键_值”的员工及其家属。我的声明有效，但我得到了重复，因为有些受抚养人有多个MED_COV_EFFECTIVE_DATEs。我只需要带来最新的或最大的日期。当我尝试使用MAX(MED_COV_EFFECTIVE_DATE)函数时，会出现错误。有人能帮帮我吗？

SELECT DISTINCT PRIMARY_KEY_VALUE, RECORD_ID, LAST_NAME, FIRST_NAME, DATE_OF_BIRTH, HIRE_DATE,
                RELATIONSHIP_CODE, MED_COV_EFFECTIVE_DATE, SOCIAL_SECURITY_NUMBER

FROM COVERAGE_TABLE T1
WHERE T1.PRIMARY_KEY_VALUE IN
(
  SELECT T2.PRIMARY_KEY_VALUE
  FROM COVERAGE_TABLE T2
   WHERE T2.HIRE_DATE IS NOT NULL 

)

ORDER BY PRIMARY_KEY_VALUE, RECORD_ID

Answer 1

Dang，没有考虑内部select只能返回1列之前。试着做这样的事情：

SELECT T2.PRIMARY_KEY_VALUE, MAX(T2.Med_Cov_Effective_Date)
INTO #MostRecentCoveredKeys
FROM COVERAGE_TABLE T2
WHERE T2.HIRE_DATE IS NOT NULL 
GROUP BY T2.Primary_Key_Value

这将为您提供一组独特的Primary_Key_Values。

或CTE版本：

; WITH MostRecentCoveredKeys
AS
SELECT T2.PRIMARY_KEY_VALUE, MAX(T2.Med_Cov_Effective_Date)
FROM COVERAGE_TABLE T2
WHERE T2.HIRE_DATE IS NOT NULL 
GROUP BY T2.Primary_Key_Value

然后加入原始表和cte (或临时表)，如下所示：

SELECT PRIMARY_KEY_VALUE, RECORD_ID, LAST_NAME, FIRST_NAME, DATE_OF_BIRTH, 
       HIRE_DATE, RELATIONSHIP_CODE, MED_COV_EFFECTIVE_DATE, 
       SOCIAL_SECURITY_NUMBER
FROM COVERAGE_TABLE T1
INNER JOIN MostRecentCoveredKeys mrck
  ON mrck.Primary_Key_Value = T1.Primary_Key_Value
ORDER BY T1.PRIMARY_KEY_VALUE, T1.RECORD_ID

--you need to include the '#' in front of table name 
--on join if using the temp table version
--DROP TABLE #MostRecentCoveredKeys

Answer 2

此查询将获取最新数据：

SELECT B.PRIMARY_KEY_VALUE, B.RECORD_ID, B.LAST_NAME, B.FIRST_NAME,   
  B.DATE_OF_BIRTH, B.HIRE_DATE, B.RELATIONSHIP_CODE, 
  B.MED_COV_EFFECTIVE_DATE, B.SOCIAL_SECURITY_NUMBER
FROM
(SELECT PRIMARY_KEY_VALUE, MAX(MED_COV_EFFECTIVE_DATE) MAX_DATE
 FROM COVERAGE_TABLE
 WHERE HIRE_DATE IS NOT NULL 
 GROUP BY PRIMARY_KEY_VALUE) A INNER JOIN
(SELECT * FROM COVERAGE_TABLE 
 WHERE HIRE_DATE IS NOT NULL) B
ON A.PRIMARY_KEY_VALUE=B.PRIMARY_KEY_VALUE AND A.MAX_DATE=B.MED_COV_EFFECTIVE_DATE;

Answer 3

;with a as
(
  select 
    *
    ,row_number() over(partition by PRIMARY_KEY_VALUE, order by Med_Cov_Effective_Date desc) rn
  FROM COVERAGE_TABLE T1
  WHERE HIRE_DATE IS NOT NULL 
)
select *
from a
where rn=1
ORDER BY PRIMARY_KEY_VALUE, RECORD_ID