问题描述:

I've taken over a project that is a real mess so I've left with bad code structure that is forcing me to basically program in SQL. So changing the way of calculating this is for now not an option.

I have $sqlAdd variable that i need to populate in function and then concatenate that to main query to count number of lost tickets.

Main query looks like this:

$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";

So $sqlAdd is getting from another function

$sqlAdd = getSqlAdd();

And in that function i have this:

$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) is getting me these rows when i execute it

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

I just want to count rows that have only LOST status in it. So the result should be 1. But i keep getting 7. It it counting every LOST status in results.

网友答案:

You would seem to want something like this:

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')
                 );

Some notes:

  • GROUP_CONCAT() is not appropriate for this type of comparison. In SQL, you don't convert lists to strings and then do comparisons -- at least if you want performance.
  • You should avoid NATURAL JOIN. A small change to any of the tables can totally change the semantics of the query. In addition, it is unclear what the JOIN keys are. I would recommend USING instead.
  • You might want tr.ticketstatus NOT IN ('LOST') in the subquery.
网友答案:

So i've wrote very ugly query that is probably slow but its working i'm getting results that i need.

$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 )"
相关阅读:
Top