php - 计算组连续行中的状态数

Question

我接手了一个非常困惑的项目，因此我留下了糟糕的代码结构，这迫使我基本上使用 SQL 进行编程。因此，改变计算方式目前还不是一个选择。

我有 $sqlAdd 变量，我需要在函数中填充该变量，然后将其连接到主查询以计算丢失票证的数量。

主要查询如下所示:

$sql = "SELECT COUNT(*) as num_tickets, SUM(t.total_amount) as total_payin, SUM(t.total_payout) as total_payout
FROM t WHERE t.tickettime BETWEEN '$dateFrom' AND '$dateTo' AND t.bsid = $bsID
$sqlAdd";

所以 $sqlAdd 是从另一个函数获取的

$sqlAdd = getSqlAdd();

在那个函数中我有这个:

$sqlAdd = " AND 'WON' NOT IN (
           SELECT GROUP_CONCAT(tr.ticketstatus)
           FROM tr INNER JOIN m ON tr.ticketid = m.ticketid
           WHERE tr.ticketid = t.ticketid GROUP BY m.ticket_groupid
           )
       AND 'PAYEDOUT' NOT IN (
           SELECT GROUP_CONCAT(tr.ticketstatus)
           FROM tr INNER JOIN m ON tr.ticketid = m.ticketid
           WHERE tr.ticketid = t.ticketid GROUP BY m.ticket_groupid
           )
       AND 'CLOSED' NOT IN (
           SELECT GROUP_CONCAT(tr.ticketstatus)
           FROM tr INNER JOIN m ON tr.ticketid = m.ticketid
           WHERE tr.ticketid = t.ticketid GROUP BY m.ticket_groupid
           )
       AND 'OPEN' NOT IN (
           SELECT GROUP_CONCAT(tr.ticketstatus)
           FROM tr INNER JOIN m ON tr.ticketid = m.ticketid
           WHERE tr.ticketid = t.ticketid GROUP BY m.ticket_groupid
           )";

GROUP_CONCAT(tr.ticketstatus) 在执行时获取这些行

CLOSED,CLOSED,CLOSED
PAYEDOUT,PAYEDOUT
CLOSED,CLOSED
WON,LOST
LOST,WON,WON,WON,WON,WON
CLOSED,CLOSED
LOST,LOST,WON
WON,WON,WON,LOST,LOST,WON,WON
LOST

我只想计算其中仅具有“丢失”状态的行。所以结果应该是 1。但我一直得到 7。它计算结果中的每个 LOST 状态。

Answer 1

你似乎想要这样的东西:

SELECT COUNT(*) as num_tickets, SUM(t.total_amount) as total_payin,
       SUM(t.total_payout) as total_payout
FROM t
WHERE t.tickettime BETWEEN '$dateFrom' AND
      '$dateTo' AND t.bsid = $bsID AND
      NOT EXISTS (SELECT 1
                  FROM tr NATURAL JOIN
                       m NATURAL JOIN
                       tg
                  WHERE tr.ticketid = t.ticketid AND
                        tr.ticketstatus IN ('WON', 'PAYEDOUT', 'CLOSED', 'OPEN')
                 );

一些注意事项:

• GROUP_CONCAT() 不适合这种类型的比较。在 SQL 中，您不需要将列表转换为字符串，然后进行比较——至少如果您想要性能的话。
• 您应该避免NATURAL JOIN。对任何表的微小更改都可以完全改变查询的语义。此外，还不清楚JOIN键是什么。我建议改为USING。
• 您可能希望在子查询中使用 tr.ticketstatus NOT IN ('LOST')。