Questions about SQL remainder split and quotient-CodePudding

If a total of 350, the number per box 100
Result is need to print four certificate, the former three number 100, eventually 50 number 1

Ask how to implement, print four certificate effect
Into
Certificate number certificate number
1 100
1 100
1 100
1
50
xie

CodePudding user response:

DECLARE @ TOTAL INT=400, @ NUM INT=100

;
WITH CTE AS (
SELECT @ the TOTAL % @ NUM X1, @ TOTAL/@ NUM X2
UNION ALL
FROM the SELECT @ NUM, X2-1 CTE WHERE X2 & gt; 0
)
SELECT * FROM CTE WHERE X1!=0

CodePudding user response:

 declare @ n int, @ m int 
Select @ n=350, @ m=100 

Select (case when (number + 1) * @ mFrom the master.. Spt_values 
Where type='p' and number<=@ n/m @

CodePudding user response:

# 1 code, maximum 100 cases, this is restricted by recursive cte cap

# 2 of the code, support a maximum of 2048 think, this is restricted by spt_values data in the table, but # 2 can create a temporary table, import a few times more spt_values data, can support arbitrary data

CodePudding user response:

OPTION (MAXRECURSION 0) are not affected by the recursive limit 100 times