mysql - SQL SELECT 从具有特殊条件的同一表生成附加列

Question

在this fiddle我有一个名为“标签”的表。它有四列:

id  counter sessionid  tag

(1, 1,     'session1', 'start'),
(2, 2,     'session1', 'sessionname1'),
(3, 3,     'session1', 'unimportant tag'),
(4, 4,     'session1', 'unimportant tag'),
(5, 5,     'session1', 'end'),
(6, 1,     'session2', 'start'),
(7, 2,     'session2', 'sessionname2'),
(8, 3,     'session2', 'end')

我想显示此表的所有列PLUS一个名为“name”的additional行。此列中的数据是根据以下条件生成的:

名称写在每个 sessionid 的第二行。这实际上意味着我检查哪一行有标签“开始”。然后将 session 的名称写入列标记，其中 counter 比标记为“start”的行1 高。

我尝试了第一个查询，该查询适用于第一个 sessionid，但随后在第二个 sessionid 处失败，因为它在每一行中写入“sessionname1”。 正确的 查询将为 ID 为 6、7、8 的行返回 name = "sessionname2"。

SELECT  t1.*,
    (SELECT top 1 t2.tag
     FROM tags t2
     JOIN ( SELECT * FROM tags WHERE tag = 'start' ) t3
       ON (t2.counter = t3.counter + 1) AND t3.sessionid = t2.sessionid
    ) AS name
FROM tags t1

我必须更改什么才能在每一行中获得正确的名称？

查看我的 SQL Fiddle

更新

当然，预期的输出显示ALL 行。它看起来像下面这样:

    id  counter sessionid  tag           name

(1, 1,     'session1', 'start',          'sessionname1'),
(2, 2,     'session1', 'sessionname1',   'sessionname1'),
(3, 3,     'session1', 'unimportant tag','sessionname1'),
(4, 4,     'session1', 'unimportant tag','sessionname1'),
(5, 5,     'session1', 'end',            'sessionname1'),
(6, 1,     'session2', 'start',          'sessionname2'),
(7, 2,     'session2', 'sessionname2',   'sessionname2'),
(8, 3,     'session2', 'end',            'sessionname2')

重要提示:

重要的是，trackingcounter 列比带有“start”的行大 1。每次 2 都是NOT，也有可能“开始”的行有 trackingcounter = 80，然后 trackingcounter 必须是 81。

Answer 1

像下面这样的东西应该适合你:

SELECT tags.*, tagnames.tag AS sessionname
FROM tags
INNER JOIN 
  (SELECT sessionid, tag 
   FROM tags WHERE counter = 2) AS tagnames 
ON tags.sessionid = tagnames.sessionid

此方法的工作原理是创建一个仅包含 session 标识符和名称(这是 INNER JOIN 子查询)的表，然后我们将其连接回主集以允许我们将 session 名称作为不同的专栏。

它有两个假设:

1. INNER JOIN 指定我们始终希望 session 有一个名称
2. 名称标签将始终使用具有 counter = 2
的 session ID 进行标识

编辑:

根据您的评论，我发现我们无法通过 counter = 2 来识别名称行，但对于给定的 sessionid，它始终是 second 行。因此，我们可以执行以下操作来选择每个 sessionid 的第二行:

SELECT @previous_session := null;

         SELECT
          sessionid, 
          counter,
          tag,
          @counter_rank := IF(@previous_session = sessionid, @counter_rank + 1, 1) AS counter_rank,
          @previous_session := sessionid
          FROM tags
          ORDER BY sessionid, counter ASC

这会使用 user defined variable 生成一个“人工”行号每个 session 组，因此我们始终可以找到按 session ID 和计数器排序的第二条记录。

将其合并到第一个查询中可以得到:

SELECT @previous_session := null;
SELECT tags.*, tagnames.tag AS sessionname
FROM tags
INNER JOIN 
  (SELECT countranks.sessionid, countranks.tag 
   FROM (
         SELECT
          sessionid, 
          counter,
          tag,
          @counter_rank := IF(@previous_session = sessionid, @counter_rank + 1, 1) AS counter_rank,
          @previous_session := sessionid
          FROM tags
          ORDER BY sessionid, counter ASC
   ) AS countranks WHERE countranks.counter_rank = 2

  ) AS tagnames 
ON tags.sessionid = tagnames.sessionid;

这是 sql in action