我接手了一个非常混乱的项目,所以我留下了糟糕的代码结构,这迫使我基本上用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_CONCT(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
我只想计算其中只有LOST状态的行。所以结果应该是1。但我一直得7分。它计算结果中的每个丢失状态。
您似乎想要这样的东西:
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')
所以我写了一个非常难看的查询,它可能很慢,但它很有效,我得到了我需要的结果。
$sql = " AND ticket_groupid IN (
SELECT tg FROM t as tt
NATURAL JOIN tr NATURAL JOIN m
WHERE tt.tickettime BETWEEN '$dateFrom' AND '$dateTo' AND tt.bsid = $bsid
AND 'LOST' IN (
SELECT GROUP_CONCAT(tr.ticketstatus)
FROM tr NATURAL JOIN m as mm
WHERE mm.ticket_groupid = m.ticket_groupid
)
GROUP BY mm.ticket_groupid )"