ROW_NUMBER()与DISTINCT [英] ROW_NUMBER() vs. DISTINCT
问题描述
如果我在以下查询中将DISTINCT与ROW_NUMBER()结合使用,则会遇到问题 我有两种情况: 1-直接运行此查询:例如,给我400条记录 2-取消注释以[--Uncomment1--]开头的行:结果是给我700条记录 它复制了一些记录,而不是全部记录
I have a problem with ROW_NUMBER() , if i used it with DISTINCT in the following Query I have 2 scenarios: 1- run this query direct : give me for example 400 record as a result 2- uncomment a line which start with [--Uncomment1--] : give me 700 record as a result it duplicated some records not all the records
我想要解决的问题或寻找任何方法在每行旁边显示一个行计数器,以使[其中行号在1到30之间] --Uncomment2-
what I want is to solve this problem or to find any way to show a row counter beside each row, to make a [where rownumber between 1 and 30] --Uncomment2--
如果我将整个查询放在一个表中,然后对其进行过滤,那是可以的,但是仍然很慢
if I put the whole query in a table, and then filter it , it is work but it still so slow
等待任何反馈,我将不胜感激
waiting for any feedback and I will appreciate that
SELECT * FROM
(SELECT Distinct CRSTask.ID AS TaskID,
CRSTask.WFLTaskID,
--Uncomment1-- ROW_NUMBER() OVER (ORDER By CRSTask.CreateDate asc ) AS RowNum ,
CRSTask.WFLStatus AS Task_WFLStatus,
CRSTask.Name AS StepName,
CRSTask.ModifiedDate AS Task_ModifyDate,
CRSTask.SendingDate AS Task_SendingDate,
CRSTask.ReceiveDate AS Task_ReceiveDate,
CRSTask.CreateDate AS Task_CreateDate,
CRS_Task_Recipient_Vw.Task_CurrentSenderName,
CRS_Task_Recipient_Vw.Task_SenderName,
CRS_INFO.ID AS CRS_ID,
CRS_INFO.ReferenceNumber,
CRS_INFO.CRSBeneficiaries,
CRS_INFO.BarCodeNumber,
ISNULL(dbo.CRS_FNC_GetTaskReceiver(CRSTask.ID), '') + ' ' + ISNULL
(CRS_Organization.ArName, '')
AS OrgName,
CRS_Info.IncidentID,
COALESCE(CRS_Subject.ArSubject, 'غير مبين') AS ArSubject,
COALESCE(CRS_INFO.Subject, 'Blank Subject') AS CRS_Subject,
CRS_INFO.Mode,
CRS_Task_Recipient_Vw.ReceiverID,
CRS_Task_Recipient_Vw.ReceiverType,
CRS_Task_Recipient_Vw.CC,
Temp_Portal_Users_View.ID AS CRS_LockedByID,
Temp_Portal_Users_View.ArabicName AS CRS_LockedByName,
CRSDraft.ID AS DraftID,
CRSDraft.Type AS DraftType,
CASE
WHEN CRS_Folder = 1 THEN Task_SenderName
WHEN CRS_Folder = 2 THEN Task_SenderName
WHEN CRS_Folder = 3 THEN Task_CurrentSenderName
END AS SenderName,
CRS_Task_Folder_Vw.CRS_Folder,
CRS_INFO.Status,
CRS_INFO.CRS_Type,
CRS_Type.arName AS CRS_Type_Name
FROM CRS_Task_Folder_Vw
LEFT OUTER JOIN CRSTask
ON CRSTask.ID = CRS_Task_Folder_Vw.TaskID
LEFT OUTER JOIN CRS_INFO
ON CRS_INFO.ID = CRSTask.CRSID
LEFT OUTER JOIN CRS_Subject
ON COALESCE(
SUBSTRING(
CRS_INFO.Subject,
CHARINDEX('_', CRS_INFO.Subject) + 1,
LEN(CRS_INFO.Subject)
),
'Blank Subject'
) = CRS_Subject.ID
LEFT OUTER JOIN CRSInfoAttribute
ON CRS_INFO.ID = CRSInfoAttribute.ID
LEFT OUTER JOIN CRS_Organization
ON CRS_Organization.ID = CRSInfoAttribute.SourceID
LEFT OUTER JOIN CRS_Type
ON CRS_INFO.CRS_Type = CRS_Type.ID
LEFT OUTER JOIN CRS_Way
ON CRS_INFO.CRS_Send_Way = CRS_Way.ID
LEFT OUTER JOIN CRS_Priority
ON CRS_INFO.CRS_Priority_ID = CRS_Priority.ID
LEFT OUTER JOIN CRS_SecurityLevel
ON CRS_INFO.SecurityLevelID = CRS_SecurityLevel.ID
LEFT OUTER JOIN Portal_Users_View
ON Portal_Users_View.ID = CRS_INFO.CRS_Initiator
LEFT OUTER JOIN AD_DOC_TBL
ON CRS_INFO.DocumentID = AD_DOC_TBL.ID
LEFT OUTER JOIN CRSTask AS Temp_CRSTask
ON CRSTask.ParentTask = Temp_CRSTask.ID
LEFT OUTER JOIN Portal_Users_View AS Temp_Portal_Users_View
ON Temp_Portal_Users_View.ID = AD_DOC_TBL.Lock_User_ID
LEFT OUTER JOIN Portal_Users_View AS Temp1_Portal_Users_View
ON Temp1_Portal_Users_View.ID = CRS_INFO.ClosedBy
LEFT OUTER JOIN CRSDraft
ON CRSTask.ID = CRSDraft.TaskID
LEFT OUTER JOIN CRS_Task_Recipient_Vw
ON CRSTask.ID = CRS_Task_Recipient_Vw.TaskID
--LEFT OUTER JOIN CRSTaskReceiverUsers ON CRSTask.ID =
CRSTaskReceiverUsers.CRSTaskID AND CRS_Task_Recipient_Vw.ReceiverID = CRSTaskReceiverUsers.ReceiverID
LEFT OUTER JOIN CRSTaskReceiverUserProfile
ON CRSTask.ID = CRSTaskReceiverUserProfile.TaskID
WHERE Crs_Info.SUBJECT <> 'Blank Subject'
AND (CRS_INFO.Subject NOT LIKE '%null%')
AND CRS_Info.IsDeleted <> 1
/* AND CRSTask.WFLStatus <> 6
AND CRSTask.WFLStatus <> 8 */
AND (
(
CRS_Task_Recipient_Vw.ReceiverID IN (1, 29)
AND CRS_Task_Recipient_Vw.ReceiverType IN (1, 3, 4)
)
)
AND 1 = 1
)Codes
--Uncomment2-- WHERE Codes.RowNum BETWEEN 1 AND 30
ORDER BY
Codes.Task_CreateDate ASC
推荐答案
如果问题是您有重复的行,而DISTINCT
失败,因为序号行使每行都是唯一的;尝试(使用DISTINCT
):
If the issue is that you have duplicate rows and DISTINCT
is failing because the ordinal row number is making each row unique; try (with DISTINCT
):
DENSE_RANK() OVER (ORDER By CRSTask.CreateDate asc)
您还可以删除CTE中的所有DISTINCT
和GROUP BY
.
You can also remove the DISTINCT
and GROUP BY
everything in the CTE.
这篇关于ROW_NUMBER()与DISTINCT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!