代码之家 › 专栏 › 技术社区 › Decent Dabbler

查找没有存储过程的相邻行

mysql

Decent Dabbler · 技术社区 · 15 年前

考虑下表:

someId INTEGER #PK
ageStart TINYINT(3)
ageEnd TINYINT(3)
dateBegin INTEGER
dateEnd INTEGER

在哪里? dateBegin 和 dateEnd 日期是否表示为自 1800-12-28 …

考虑到一些样本数据:

someId | ageStart | ageEnd | dateStart | dateEnd
------------------------------------------------
   203 |       16 |     25 |     76533 |   76539   \
   506 |       16 |     25 |     76540 |   76546    adjacent rows
   384 |       16 |     25 |     76547 |   76553   /
   342 |       16 |     25 |     76563 |   76569   \
   545 |       16 |     25 |     76570 |   76576    adjacent rows
   764 |       16 |     25 |     76577 |   76583   /

(在非课程中会有任意的行混合,我只想说明两个相关的行集)

是否可以在没有存储过程的情况下查找给定年龄类别(从AgeStart到AgeEnd)的相邻行?相邻的标准是: dateStart 是1天后 数据终端 上一个找到的行的。

例如,对于上述示例数据,如果我使用以下参数进行查询:

ageStart = 16
ageEnd = 25
dateStart = 76533

我希望它返回示例数据的第1行、第2行和第3行,因为它们的日期是相邻的(daystart是前一行日期end的第二天)。

ageStart = 16
ageEnd = 25
dateStart = 76563

…将给出示例数据的第4、5和6行

3 回复 | 直到 9 年前

Patrick 15 年前

如果表中有大量数据,可能效率不高,但请尝试以下操作:

SELECT b.*
FROM
(SELECT @continue:=2) init,
(
 SELECT * 
 FROM ageTable 
 WHERE ageStart=16 AND 
       ageEnd=25   AND 
       dateStart=76533
) a 
  INNER JOIN (
   SELECT *
   FROM ageTable
   ORDER BY dateStart
  ) b ON (
    b.ageStart=a.ageStart AND
    b.ageEnd=a.ageEnd     AND
    b.dateStart>=a.dateStart
  ) 
  LEFT JOIN ageTable c ON (
    c.dateStart=b.dateEnd+1 AND
    c.ageStart=b.ageStart   AND 
    c.ageEnd=b.ageEnd
  )
WHERE 
 CASE
  WHEN @continue=2 THEN
   CASE
    WHEN c.someId IS NULL THEN
     @continue:=1
    ELSE
     @continue
   END
  WHEN @continue=1 THEN
   @continue:=0
  ELSE
   @continue
 END

titanoboa 15 年前

您可以将数据视为父子关系:如果子记录的开始日期等于父记录的结束日期+1,则记录是父记录的子记录。对于分层数据(具有父子关系),请 嵌套集模型 允许您在不使用存储过程的情况下查询数据。您可以在此处找到嵌套集模型的简要描述:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

其思想是以一种巧妙的方式对记录进行编号,这样您就可以使用简单的查询而不是递归存储过程。

虽然以这种方式存储的分层数据很容易查询,但是添加新记录时需要注意一些。在嵌套集模型中添加新记录需要更新现有记录。这在您的用例中可能是可接受的,也可能是不可接受的。

Silver Light 15 年前

好吧,您可以生成一个以特定方式排序的结果集,并使用限制,从中只获取第一条记录。

例如,按日期获取列表中的下一条记录:

SELECT *
FROM `table`
WHERE `dateEnd` > '76546'
ORDER BY `dateEnd`
LIMIT 1

你会得到:

384 |       16 |     25 |     76547 |   76553

对于前一行:

SELECT *
FROM `table`
WHERE `dateEnd` < '76546'
ORDER BY `dateEnd` DESC
LIMIT 1

你会得到:

   203 |       16 |     25 |     76533 |   76539

我怀疑只需一个查询就可以完成…