php - 从查询中检索特定键值并在查询中获取它们对的计数

Question

我有一个表lead_submission，其中包含特定格式的用户值，例如

agent_name     qa_details
xxx      1001:|1083:|504:Yes|1009:|
ccc      504:Yes|1083:No|1008:|1009:|

现在我想从两行中获取只说 504:Yes 的计数

这些值来自另一个表 paid_response

qno    paid_response
504     Yes
1083    No
1083    Possibly

<?php
//db connection goes here

$sql=mysql_query("select qno,paid_response from paid_response where qno='504' ");
while($rows=mysql_fetch_array($sql)) {    
$exqnos= $rows['qno'].'|'.$rows['paid_response'];
}

list($key,$val)=explode('|',$exqnos);
$exqno[$key]=$val;

foreach($exqno as $qno=>$value) {
$string .="qa_details LIKE '%|$qno:$value|%' ";  
}

$sql=mysql_query("SELECT count(agent_name) as agent_cnt,count($string) as ppicount FROM `lead_submission` WHERE $string "); ?>

               <table border="1">
                <thead>
                  <tr>
                    <th>CountAgent</th>
                    <th>504-COUNT</th>                      
                  </tr>

<?php
while($row=mysql_fetch_array($sql)) { ?>

     <tr style="color:red" >            
        <td><?php echo $row['agent_cnt']; ?></td>
        <td><?php echo $row['ppicount']; ?></td>            
        </tr>

<?php
}
?> 

现在通过这样做，我将 504:Yes

算作 2

CountAgent  504-COUNT
 2            2        //as u can see that `504:Yes` has occured two times in lead_submission table.

我的意思是，我怎样才能计算另一个组合，例如 1083:No 并在同一张表中显示计数

NB:- cant we just fetch the combination like `504:Yes` or `1083:No` or `1083:Yes` from paid_response table to maintain stability so that i dont have to change the query everytime. 

CountAgent  504-COUNT   1083-Count
  2           2           1        //how to get this count `1083:No` . as u can see it only appeared 1 times in `lead_submission` table

Answer 1

试试这个:

SELECT COUNT(DISTINCT ls.agent_name), 
       SUM(CASE WHEN pr.qno = 504 AND pr.paid_response = 'Yes' THEN 1 ELSE 0 END) AS '504-Count', 
       SUM(CASE WHEN pr.qno = 1083 AND pr.paid_response = 'No' THEN 1 ELSE 0 END) AS '1083-Count'
FROM lead_submission ls 
INNER JOIN paid_response pr
ON CONCAT('|', ls.qa_details, '|') LIKE CONCAT('%|', pr.qno, ':', pr.paid_response , '|%');

检查 SQL FIDDLE DEMO

输出

| COUNTAGENT | 504-COUNT | 1083-COUNT |
|------------|-----------|------------|
|          2 |         2 |          1 |

::编辑::

首先执行下面的查询

SELECT GROUP_CONCAT('SUM(CASE WHEN pr.qno = ', qno, ' AND pr.paid_response = ''', paid_response,''' THEN 1 ELSE 0 END) AS ''', qno, '-Count''') 
FROM paid_response;

使用此查询的输出并构建您的最终查询，如下所示:

query = 'SELECT COUNT(DISTINCT ls.agent_name), ' + outputOfAboveQuery + ' FROM lead_submission ls NNER JOIN paid_response pr ON CONCAT('''|''', ls.qa_details, '''|''') LIKE CONCAT('''%|''', pr.qno, ''':''', pr.paid_response , '''|%''');';

在你的代码中执行这个字符串，这样你就可以获得动态查询来获取计数