COUNT DISTINCT ROW_NUMBER DENSE_RANK 以及对COUNT去重（非PARTITION）

1：COUNT DISTINCT

        SELECT 
        COUNT(DISTINCT [QS_QuestionStem].Id)  AS ReqCount1,
        [QS_QuestionStem].Content AS Content
        FROM [EL_QuestionBank].[QS_QuestionStem] AS [QS_QuestionStem]
        INNER JOIN [EL_QuestionBank].[QS_QuestionProperty] AS [QS_QuestionProperty]
        ON [QS_QuestionStem].Id=[QS_QuestionProperty].[QuestionStemCode]
        INNER JOIN [EL_QuestionBank].[QuestionCategory] AS [QuestionCategory]
        ON [QuestionCategory].Id=[QS_QuestionProperty].SubjectCode
        LEFT JOIN [EL_QuestionBank].[QS_QuestionOptions] AS [QS_QuestionOptions]
        ON [QS_QuestionOptions].QuestionStemCode=[QS_QuestionStem].Id
        WHERE [QuestionCategory].Id=@CategoryIds OR charindex(@CategoryIds,[QuestionCategory].ParentIds )>0
        AND [QS_QuestionStem].QuestionFaceCode IS null
        GROUP BY [QS_QuestionStem].Content

image

2：DENSE_RANK Vs ROW_NUMBER

        SELECT 
        DENSE_RANK() OVER (ORDER By [QS_QuestionStem].Id) AS X,
        ROW_NUMBER() OVER (ORDER By [QS_QuestionStem].Id) AS Y,
        COUNT([QS_QuestionStem].Id) OVER()  AS ReqCount2,
        [QS_QuestionStem].Content AS Content
        FROM [EL_QuestionBank].[QS_QuestionStem] AS [QS_QuestionStem]
        INNER JOIN [EL_QuestionBank].[QS_QuestionProperty] AS [QS_QuestionProperty]
        ON [QS_QuestionStem].Id=[QS_QuestionProperty].[QuestionStemCode]
        INNER JOIN [EL_QuestionBank].[QuestionCategory] AS [QuestionCategory]
        ON [QuestionCategory].Id=[QS_QuestionProperty].SubjectCode
        LEFT JOIN [EL_QuestionBank].[QS_QuestionOptions] AS [QS_QuestionOptions]
        ON [QS_QuestionOptions].QuestionStemCode=[QS_QuestionStem].Id
        WHERE [QuestionCategory].Id=@CategoryIds OR charindex(@CategoryIds,[QuestionCategory].ParentIds )>0
        AND [QS_QuestionStem].QuestionFaceCode IS null

image

3：关于 Count

DECLARE         @CategoryIds varchar(max)='5315f8616e174094ac7251f2e3c4d584';                --分类ID
--DECLARE         @CategoryIds varchar(max)='1';                --分类ID
DECLARE         @PageNumber int=1;                        --页码
DECLARE         @PageSize int=20;                            --页大小
DECLARE         @Fettle int=1;                        --冻结状态
DECLARE         @RecieveState int=2;                    --接收状态：0共享的所有数据；1：未接收；2：已接受
DECLARE         @RecieveStateConstraint bit=NULL;            --约束接收状态（如果为真，则严格按照此值进行过滤）
DECLARE         @AuditState int =null;                    --审核状态
DECLARE         @AuditStateCustom int=null;                --审核状态:2:已审核；小于2：未开始、审核中
DECLARE         @Total int=0;                        --输出参数，总条数

--SELECT * FROM EL_QuestionBank.QuestionCategory
--with tempGR as( select row_number() over( order by A.Id) as ReqIndex ,COUNT(A.Id) OVER() as ReqCount , A.*
--                    from EL_CourseMaker.Course A where CATEGORYID = '1' )
--select * from tempGR where ReqIndex between -99 and 0 order by name

with tempGR as(
    SELECT
        DISTINCT(
            CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id + 'XXXX'
                  WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode + 'YYYY'
            END) AS XId,
--        DENSE_RANK() OVER (
--            ORDER By (
--                CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id
--                      WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode
--                END)) AS Y,
        DENSE_RANK() OVER (
            ORDER By (
                CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id
                      WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode
                END)) AS ReqIndex,
--                MAX(Y),
--        COUNT(*) OVER(ORDER BY [QS_QuestionStem].Id) AS REQCOUNT,
        [QS_QuestionProperty].Difficult AS Difficult,
--        COUNT(distinct coalesce([QS_QuestionStem].Id,[QS_QuestionStem].QuestionFaceCode))  AS ReqCount,
        COUNT((
                CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id
                      WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode
                END)) OVER()  AS ReqCount,
        (CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Content
             WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN(SELECT CONTENT FROM [EL_QuestionBank].[QS_QuestionFace] WHERE ID= [QS_QuestionStem].QuestionFaceCode)
        END) AS Content
        FROM [EL_QuestionBank].[QS_QuestionStem] AS [QS_QuestionStem]
        INNER JOIN [EL_QuestionBank].[QS_QuestionProperty] AS [QS_QuestionProperty]
        ON [QS_QuestionStem].Id=[QS_QuestionProperty].[QuestionStemCode]
        INNER JOIN [EL_QuestionBank].[QuestionCategory] AS [QuestionCategory]
        ON [QuestionCategory].Id=[QS_QuestionProperty].SubjectCode
        WHERE [QuestionCategory].Id=@CategoryIds OR charindex(@CategoryIds,[QuestionCategory].ParentIds )>0
--        GROUP BY [QS_QuestionStem].Id,[QS_QuestionStem].QuestionFaceCode
) select * from tempGR  where ReqIndex between 1 and 100


--
--SELECT COUNT(*) FROM [EL_QuestionBank].[QS_QuestionStem] AS [QS_QuestionStem]
--        INNER JOIN [EL_QuestionBank].[QS_QuestionProperty] AS [QS_QuestionProperty]
--        ON [QS_QuestionStem].Id=[QS_QuestionProperty].[QuestionStemCode]
--        INNER JOIN [EL_QuestionBank].[QuestionCategory] AS [QuestionCategory]
--        ON [QuestionCategory].Id=[QS_QuestionProperty].SubjectCode
--        WHERE [QuestionCategory].Id=@CategoryIds OR charindex(@CategoryIds,[QuestionCategory].ParentIds )>0
--        AND [QS_QuestionStem].QuestionFaceCode IS NULL
--   

--SELECT ID FROM [EL_QuestionBank].[QS_QuestionStem] WHERE [QS_QuestionStem].QuestionFaceCode IS null

--SELECT TOP 1 * FROM [EL_QuestionBank].[QS_QuestionFace]

image

4：错误？

我们发现第三步中的 ReqCount 是错误的，那应该如何才是正确的？

image

要实现它？级联一个 with 字句吧：

DECLARE         @CategoryIds varchar(max)='5315f8616e174094ac7251f2e3c4d584';                --分类ID
--DECLARE         @CategoryIds varchar(max)='1';                --分类ID
DECLARE         @PageNumber int=1;                        --页码
DECLARE         @PageSize int=20;                            --页大小
DECLARE         @Fettle int=1;                        --冻结状态
DECLARE         @RecieveState int=2;                    --接收状态：0共享的所有数据；1：未接收；2：已接受
DECLARE         @RecieveStateConstraint bit=NULL;            --约束接收状态（如果为真，则严格按照此值进行过滤）
DECLARE         @AuditState int =null;                    --审核状态
DECLARE         @AuditStateCustom int=null;                --审核状态:2:已审核；小于2：未开始、审核中
DECLARE         @Total int=0;                        --输出参数，总条数

--SELECT * FROM EL_QuestionBank.QuestionCategory
--with tempGR as( select row_number() over( order by A.Id) as ReqIndex ,COUNT(A.Id) OVER() as ReqCount , A.*
--                    from EL_CourseMaker.Course A where CATEGORYID = '1' )
--select * from tempGR where ReqIndex between -99 and 0 order by name
with tempGR as(
--    SELECT COUNT(*) FROM
--    (
        SELECT
            DISTINCT(
                CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id + 'XXXX'
                    WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode + 'YYYY'
                END) AS XId,
            DENSE_RANK() OVER (
                ORDER By (
                    CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Id
                        WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN [QS_QuestionStem].QuestionFaceCode
                    END)) AS ReqIndex,
            [QS_QuestionProperty].Difficult AS Difficult,
            (CASE WHEN [QS_QuestionStem].QuestionFaceCode IS NULL THEN [QS_QuestionStem].Content
                WHEN [QS_QuestionStem].QuestionFaceCode IS NOT NULL THEN(SELECT CONTENT FROM [EL_QuestionBank].[QS_QuestionFace] WHERE ID= [QS_QuestionStem].QuestionFaceCode)
            END) AS Content
            FROM [EL_QuestionBank].[QS_QuestionStem] AS [QS_QuestionStem]
            INNER JOIN [EL_QuestionBank].[QS_QuestionProperty] AS [QS_QuestionProperty]
            ON [QS_QuestionStem].Id=[QS_QuestionProperty].[QuestionStemCode]
            INNER JOIN [EL_QuestionBank].[QuestionCategory] AS [QuestionCategory]
            ON [QuestionCategory].Id=[QS_QuestionProperty].SubjectCode
            WHERE [QuestionCategory].Id=@CategoryIds OR charindex(@CategoryIds,[QuestionCategory].ParentIds )>0
--    )
),MAXGR AS(
    SELECT *, count(xid) over() as x FROM TEMPGR
)select * from MAXGR

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/luminji/p/3658509.html