I have the following scenario, I have a table column with the name categoryid in table categories, I need to select all rows within the table where categoryid is 100 and above in intervals of 100.
eg
SELECT * FROM categories WHERE categoryid IN(100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000...
Is there a better way to do this than manually typing all intervals, it should start at hundred and go up to the last number. Keep in mind that the last number can change as client adds new categories. is this possible, hope I make sense.
解决方案
So this % operator that I have used is called the modulo operator and it will give you the remainder after dividing by a number (in this case 100). If the remainder is 0 then it's a multiple of 100. We also need the >= 100 condition because zero also passes the modulo condition.
SELECT * FROM categories WHERE categoryid >= 100 AND categoryid % 100 = 0;