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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值