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
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
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 namewith 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]
4:错误?
我们发现第三步中的 ReqCount 是错误的,那应该如何才是正确的?
要实现它?级联一个 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