問題描述
在 SQL Server 中，我有一個目標表 STAGNG_PA_BK_FEED_REVNU_SUM，它有以下 7 列構成其唯一索引.我想使用主要來自 STAGNG_PA_BK_FEED_REVNU_DTL 表的數據填充此表.目標表中的數據包含一個金額字段 REVNU_AMT，該字段通過對所有其他選定/非聚合列進行分組來聚合.
In SQL Server, I have a target table STAGNG_PA_BK_FEED_REVNU_SUM, that has the following 7 columns that make up its unique index. I want to populate this table using data primarily from the STAGNG_PA_BK_FEED_REVNU_DTL table. The data in the target table includes an amount field, REVNU_AMT, that is aggregated by grouping on all of the other selected/non aggregated columns.
因為我們正在選擇添加屬性"不包含在目標表的唯一鍵中的列，如果我們為鍵分組內的屬性列獲得多個不同的值組合，則插入將失敗.發生這種情況時，我希望能夠識別共享相同主鍵但具有不同屬性值組合的所有源記錄.換句話說，我希望能夠生成完整源記錄的報告，以便業務用戶可以識別插入時導致唯一鍵沖突的違規記錄.
Because we are selecting addition "property" columns that are not included in the unique key of the target table, the insert will fail if we get more than one distinct combination of values for the property columns within a key grouping. When this happens, I want to be able to identify the all of the source records that share the same primary key but have different property value combinations. In other words, I want to be able to produce a report of the complete source records so that the business users can identify the offending records that cause the unique key violation when inserting.
INSERT INTO dbo.STAGNG_PA_BK_FEED_REVNU_SUM
(
--Keys of target table
RBT_YR_DT
, MLR_SRC_SYS_CD
, LGL_ENTTY_CD
, CLIENT_ID
, CLIENT_ACCT_NUM
, BEN_PLAN_ID
, CLIENT_CNTRCT_ST_CD
--Properties of target table
, MLR_EXTRT_SYS_CD
, PA_LGL_ENTTY_CD
, COA_CO_CD
, COMMRCL_BUS_IND
, COA_SITUS_ST_CD
, ASGND_SITUS_STE_IND
, CLIENT_TY_CD
, MLR_SEG_CD
--Fact
, REVNU_AMT
)
SELECT
--values for keys of target table
D.RBT_YR_DT
, D.MLR_SRC_SYS_CD
, D.LGL_ENTTY_CD
, D.CLIENT_ID
, D.CLIENT_ACCT_NUM
, D.BEN_PLAN_ID
, J.SITUS_STE_CD AS CLIENT_CNTRCT_ST_CD
--values for properties of target table
, D.MLR_EXTRT_SYS_CD
, D.PA_LGL_ENTTY_CD
, D.COA_CO_CD
, D.COMMRCL_BUS_IND
, D.COA_SITUS_ST_CD
, 'N' AS ASGND_SITUS_STE_IND
, D.CLIENT_TY_CD
, D.MLR_SEG_CD
--Fact
,SUM(D.REVNU_AMT) AS REVNU_AMT
FROM
dbo.STAGNG_PA_BK_FEED_REVNU_DTL D
INNER JOIN JE_NT_STE_MAP J
ON D.COA_SITUS_ST_CD = J.CONTRACT_SITUS_STATE
GROUP BY
--PK
D.RBT_YR_DT
, D.MLR_SRC_SYS_CD
, D.LGL_ENTTY_CD
, D.CLIENT_ID
, D.CLIENT_ACCT_NUM
, D.BEN_PLAN_ID
, J.SITUS_STE_CD
--Properties
-- Must be unique distinct group of value within the key grouping else key violation on target will result
, D.MLR_EXTRT_SYS_CD
, D.PA_LGL_ENTTY_CD
, D.COA_CO_CD
, D.COMMRCL_BUS_IND
, D.COA_SITUS_ST_CD
, D.CLIENT_TY_CD
, D.MLR_SEG_CD
更新
我之前考慮過使用 Jaime 的方法將所有屬性值連接成一個值，以便在 HAVING 子句中應用 DISTINCT 動詞.
Update
I previously considered using Jaime's approach of concatenating all of the property values into a single value so the DISTINCT verb could be applied in the HAVING clause.
根據他的回答，我將其修改為顯示報告源表中需要檢查和更正的所有記錄，以防止重復鍵違規，這是我的目標，而不僅僅是顯示重復的非鍵價值.
Taking his answer, I modified it to display a report all of the records in the source table that need to be examined and corrected in order to prevent the duplicate key violation, which was my goal, not just display the duplicated non key values.
WITH DUPS AS
(
SELECT
--values for keys of target table
D.RBT_YR_DT
, D.MLR_SRC_SYS_CD
, D.LGL_ENTTY_CD
, D.CLIENT_ID
, D.CLIENT_ACCT_NUM
, D.BEN_PLAN_ID
, J.SITUS_STE_CD AS CLIENT_CNTRCT_ST_CD
FROM
dbo.STAGNG_PA_BK_FEED_REVNU_DTL D
INNER JOIN JE_NT_STE_MAP J
ON D.COA_SITUS_ST_CD = J.CONTRACT_SITUS_STATE
GROUP BY
--PK
D.RBT_YR_DT
, D.MLR_SRC_SYS_CD
, D.LGL_ENTTY_CD
, D.CLIENT_ID
, D.CLIENT_ACCT_NUM
, D.BEN_PLAN_ID
, J.SITUS_STE_CD
HAVING
COUNT(DISTINCT
CONVERT(VARCHAR(MAX), D.MLR_EXTRT_SYS_CD) + '-' +
CONVERT(VARCHAR(MAX), D.PA_LGL_ENTTY_CD) + '-' +
CONVERT(VARCHAR(MAX), D.COA_CO_CD) + '-' +
CONVERT(VARCHAR(MAX), D.COMMRCL_BUS_IND) + '-' +
CONVERT(VARCHAR(MAX), D.COA_SITUS_ST_CD) + '-' +
CONVERT(VARCHAR(MAX), D.CLIENT_TY_CD) + '-' +
CONVERT(VARCHAR(MAX), D.MLR_SEG_CD)
) > 1
)
SELECT
--Keys
D.RBT_YR_DT
, D.MLR_SRC_SYS_CD
, D.LGL_ENTTY_CD
, D.CLIENT_ID
, D.CLIENT_ACCT_NUM
, D.BEN_PLAN_ID
, D.COA_SITUS_ST_CD
--Properties that have dups with a key groups
, D.MLR_EXTRT_SYS_CD
, D.PA_LGL_ENTTY_CD
, D.COA_CO_CD
, D.COMMRCL_BUS_IND
, D.COA_SITUS_ST_CD
-- , 'N' AS ASGND_SITUS_STE_IND
, D.CLIENT_TY_CD
, D.MLR_SEG_CD
FROM
STAGNG_PA_BK_FEED_REVNU_DTL D
INNER JOIN JE_NT_STE_MAP J
ON D.COA_SITUS_ST_CD = J.CONTRACT_SITUS_STATE
inner join DUPS ON
DUPS.RBT_YR_DT = D.RBT_YR_DT
AND DUPS.MLR_SRC_SYS_CD = D.MLR_SRC_SYS_CD
AND DUPS.LGL_ENTTY_CD = D.LGL_ENTTY_CD
AND DUPS.CLIENT_ID = D.CLIENT_ID
AND DUPS.CLIENT_ACCT_NUM = D.CLIENT_ACCT_NUM
AND DUPS.BEN_PLAN_ID = D.BEN_PLAN_ID
AND DUPS.CLIENT_CNTRCT_ST_CD = J.SITUS_STE_CD
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
雖然這可能在實踐中起作用，但我發現它有點笨拙，最后我可能會顯示具有重復的非鍵的記錄，該非鍵出現了另一個鍵組但只出現過一次，所以最終報告可能不準確.
While this may work in practice I find it a little kludgy and in the end I may be displaying records that had a duplicate non-key that appeared another key grouping but only appeared there once, so the end report may not be accurate.
推薦答案
這樣的事情可以幫助您找到具有 1 個以上不同屬性組合的 PK
Something like this could help you to find those PK with more than 1 different properties combination
SELECT
--values for keys of target table
D.RBT_YR_DT
,D.MLR_SRC_SYS_CD
,D.LGL_ENTTY_CD
,D.CLIENT_ID
,D.CLIENT_ACCT_NUM
,D.BEN_PLAN_ID
,J.SITUS_STE_CD AS CLIENT_CNTRCT_ST_CD
FROM dbo.STAGNG_PA_BK_FEED_REVNU_DTL D
INNER JOIN JE_NT_STE_MAP J
ON D.COA_SITUS_ST_CD = J.CONTRACT_SITUS_STATE
GROUP BY
--PK
D.RBT_YR_DT
,D.MLR_SRC_SYS_CD
,D.LGL_ENTTY_CD
,D.CLIENT_ID
,D.CLIENT_ACCT_NUM
,D.BEN_PLAN_ID
,J.SITUS_STE_CD
HAVING
COUNT(DISTINCT
CONVERT(VARCHAR(MAX),D.MLR_EXTRT_SYS_CD) + '-' +
CONVERT(VARCHAR(MAX),D.PA_LGL_ENTTY_CD) + '-' +
CONVERT(VARCHAR(MAX),D.COA_CO_CD) + '-' +
CONVERT(VARCHAR(MAX),D.COMMRCL_BUS_IND) + '-' +
CONVERT(VARCHAR(MAX),D.COA_SITUS_ST_CD) + '-' +
CONVERT(VARCHAR(MAX),'N' AS ASGND_SITUS_STE_IND) + '-' +
CONVERT(VARCHAR(MAX),D.CLIENT_TY_CD) + '-' +
CONVERT(VARCHAR(MAX),D.MLR_SEG_CD)
) > 1
這篇關于SQL 返回一組鍵列中非鍵列的唯一組合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！