mysql - 获取带有子项和条件的父行

Question

我有一个名为 Ticket 的表:

ticket (idTicket, title , message , parentTicket, lastdate, status)

状态获得默认值:打开，一旦我(mod)回复设置为已回复

我正在尝试获取状态为打开的事件票证(新创建的和上次用户回复)

示例:

13 , 'Title 1' , 'msg Title1' , NULL , 'open'
14 , 'Title 2' , 'msg Title2' , NULL , 'open'
15 , 'reply Title 2' , 'reply msg Title2' , 14 , 'replied'
16 , 'Title 3' , 'msg Title3' , NULL ,'open'
17 ,  'reply Title 3' , 'reply msg Title3' , 16 , 'replied' 

通常我应该只列出票证 ID 13

我的 2 个查询想法，但没有成功

1/

SELECT *
FROM ticket AS parent LEFT JOIN
     ticket AS child 
     ON child.parentTicket = parent.idTicket
WHERE parent.parentTicket IS NULL and child.status ="open"
ORDER BY parent.idTicket, child.idTicket;

2/

Select t1.idTicket ,t2.parentTicket
from ticket t1 inner join
     ticket t2
     on (t1.idTicket != t2.parentTicket)
where t1.status ="open" and t2.parentTicket is null
Group by idTicket

3/

Select a.idTicket ,a.titleTicket,a.parentTicket
from ticket a
where not exists (select null from ticket b where a.parentTicket!=b.parentTicket) 
and a.status='open'

Answer 1

返回没有任何子票证的开放“根”票证...也就是说，ticket 中的一行对于 parentTicket 具有 NULL ，并且具有 status='open'，并且子票证不存在，您可以使用反加入模式:

 SELECT p.*
   FROM ticket p
   LEFT
   JOIN ticket c
     ON c.parentTicket = p.idTicket
  WHERE p.parentTicket IS NULL
    AND p.status = 'open'
    AND c.parentTicket IS NULL

反连接是一种外连接，在 WHERE 子句中带有一个谓词，用于排除匹配的行。这基本上是说获取所有开放的“根”票证以及任何匹配的子行。诀窍是最后一个谓词，c.parentTicket IS NULL...任何匹配的子行都保证具有非 NULL 值(由于 JOIN 谓词)，因此这排除了具有匹配的 child 。

这相当于带有 NOT EXISTS(子查询) 谓词的查询。这也许更容易理解:

 SELECT p.*
   FROM ticket p
  WHERE p.parentTicket IS NULL
    AND p.status = 'open'
    AND NOT EXISTS
        ( SELECT 1
            FROM ticket c
           WHERE c.parentTicket = p.idTicket
        )
 ;

这两个查询都将返回带有 idTicket=13 的行。

SQL Fiddle demonstration here: http://sqlfiddle.com/#!9/393b7/1

但是...虽然这些查询返回指定的行，并且足以满足问题中提供的示例数据，但我认为这不能满足规范的全部。问题中有一些关于“最后用户回复”的内容，但除此之外，规范相当稀疏。没有示例数据说明应该(和不应该)返回哪些行。如果有多个回复，或者一个回复一个回复怎么办，回复的状态、作者身份等怎么办？

或者，也许这些查询就足够了，或者至少足以让您朝着正确的方向前进。