查询中的 MySQL 子位置计数器

发布于 2024-12-06 18:16:48 字数 721 浏览 1 评论 0原文

我需要获取一个记录集，其中为每 60 条记录填充字段 level1，这意味着我需要以这种方式填充 level1 字段：

在 pos=1 和 pos=60 上，level1 = 1
在 pos=61 和 pos=120 上，使用 level1 = 2
位于 pos=121 和 pos=180 且 level1 = 3

... 然后：如果我有 630 条记录，我必须设置 pos=601 和 630 level1 = 10，因为我没有 660 条记录，第 630 条记录完成了关卡。

有谁知道如何以干净的方式完成此操作？

SET @pos:=0; 
SET @posrel:=0;
SET @level1:=0;
SELECT id, member_id, member_name, pos, @posrel:=@posrel+1 AS posrel, @level1:=@level1+??? AS 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);

原文

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 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);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岛徒 2024-12-13 18:16:48

-- 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

或者也许

-- 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

-- 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

回复收藏 0 原文

~没有更多了~