MySQL sub position counters in Query
I need to get a record set where I fill the field level1 for every 60 records, means I need to fill level1 field this way:
- on pos=1 and pos=60 with level1 = 1
- on pos=61 and pos=120 with level1 = 2
- on pos=121 and pos=180 with level1 = 3
... and then: if I have let's say 630 records i must set for pos=601 and 630 level1 = 10 because I don't have 660 records the 630th record finished the level.
Has anyone a idea how this can be done in a clean way?
SET @pos:=0;
SET @posrel:=0;
SET @level1:=0;
SELECT id, member_id, member_name, pos, @posrel:=@posrel+1 AS posrel, @level1:=@level1+??? AS 开发者_StackOverflow社区level1
FROM
(
SELECT id, member_id, LEFT(member_name, LENGTH(member_name)-36) AS member_name, @pos:=@pos+1 AS pos FROM member_directory WHERE member_name_first= 'A'
) AS directory_listing
HAVING pos % 60 IN(0,1);
-- SET @pos:=0; //Can be moved inside the query.
-- SET @posrel:=0;
SELECT inner.*, posrel DIV 60 as level1 FROM (
SELECT directory_listing.*, @posrel:=@posrel+1 AS posrel AS level1
FROM
(
SELECT
id
, member_id
, LEFT(member_name, LENGTH(member_name)-36) AS member_name_first
, @pos:=@pos+1 AS pos
FROM member_directory
CROSS JOIN (SELECT @pos:= 0) x1
WHERE member_name_first = 'A'
AND directory_listing.pos < 120
) directory_listing
CROSS JOIN (SELECT @posrel:= 0) x2
) inner
Or maybe
-- SET @pos:=0; //Can be moved inside the query.
-- SET @posrel:=0;
SELECT inner.*, posrel DIV 60 as level1 FROM (
SELECT directory_listing.*, @posrel:=@posrel+1 AS posrel AS level1
FROM
(
SELECT
id
, member_id
, LEFT(member_name, 1) AS member_name_first
, @pos:=@pos+1 AS pos
FROM member_directory
CROSS JOIN (SELECT @pos:= 0) x1
WHERE member_name LIKE 'A%'
AND directory_listing.pos < 120
) directory_listing
CROSS JOIN (SELECT @posrel:= 0) x2
) inner
精彩评论