我有一个表格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
}
?>
现在通过这样做,我被计为 2 504:Yes
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
试试这个:
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 小提琴演示
输出
| 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 , '''|%''');';
在代码中执行此字符串,以便获取动态查询以获取计数
<?php
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('test',$con);
function countIt($checkArray)
{
$checkVals = explode(',', $checkArray);
foreach($checkVals AS $val){
list($qNo, $pRes) = explode(':', $val);
$query = mysql_query("SELECT * FROM `paid_response` WHERE `qno`='$qNo' AND `paid_response`='$pRes'");
if(mysql_num_rows($query) > 0){
$query = mysql_query("SELECT * FROM `lead_submission` WHERE `qa_details` LIKE '%$val%'");
$countArray[$val] = mysql_num_rows($query);
} else {
$countArray[$val] = 0;
}
}
foreach($countArray AS $key=>$val){
echo $key . ' => ' . $val . "<br/>";
}
}
echo countIt('504:yes,1083:no,1083:yes,1083:possibly,504:no');
试试这个人!
呼应他人的评论。您可能应该规范化您的模型。
也就是说,提取所需的结果并非不可能,只是解决方案效率低下,不可扩展,新开发人员难以理解并且扩展性不强。
此外,以长格式而不是宽格式提取数据
更容易,即# wide data format
CountAgent 504-COUNT 1083-Count
2 2 1
与。
# Long data format
dimension count
CountAgent 2
504-Count 2
1083-Count 1
在 php 中,从长到宽的转换更容易(甚至可能不需要)。
SELECT
CONCAT(pr.qno, ":", pr.paid_response) dimension,
COUNT(*) `count`
FROM lead_submission ls
JOIN paid_response pr
ON ls.qa_details LIKE CONCAT("%", pr.qno, ":", pr.paid_response, "%")
-- insert where clause
GROUP BY 1
UNION
SELECT 'count-agent' dimension,
COUNT(DISTINCT ls.agent_id) `count`
FROM lead_submission ls
JOIN paid_response pr
ON ls.qa_details LIKE CONCAT("%", pr.qno, ":", pr.paid_response, "%")
-- insert where clause
GROUP BY 1
在上面的查询中,两个联合选择的where clause
应该是相同的,我认为对于您的情况,它应该采用以下形式:
WHERE CONCAT(pr.qno, ":", pr.paid_response) IN (<key-value pair 1>, <key-value pair 2>, ...)
这将返回以下结果:
DIMENSION COUNT
1083:No 1
504:Yes 2
count-agent 2
这是 sqlfiddle 演示
假设数据的格式保持不变,您可以尝试这样的事情(未经测试):
$query[] = "SELECT COUNT(agent_name) as agent_cnt"; // number of agents in total
// Counts of each question/answer.
foreach ($exqno as $qno => $value) {
$query[] = "(SELECT COUNT(agent_name) FROM lead_submission WHERE qa_details LIKE '%|$qno:$value|%') AS count_{$qno}";
}
$full_query = implode(', ', $query) . " FROM lead_submission";
$sql = mysql_query( $full_query );
SELECT productOwner_org_id,
(SELECT COUNT(*) FROM tblProducts P2 WHERE P1.product_id=p2.product_id AND productDescription='Leisure at PER01 IVR Gas') AS '504-data',
(SELECT COUNT(*) FROM tblProducts P3 WHERE P1.product_id=p3.product_id AND productDescription='Leisure Plus at Centerpoint') AS '1083-data'
FROM tblProducts p1
WHERE productOwner_org_id = 'AEG01'
AND
(SELECT COUNT(*) FROM tblProducts P2 WHERE P1.product_id=p2.product_id AND productDescription='Leisure at PER01 IVR Gas') != 0
OR
(SELECT COUNT(*) FROM tblProducts P3 WHERE P1.product_id=p3.product_id AND productDescription='Leisure Plus at Centerpoint') != 0
;
如你所见,它有点丑陋。
A) 您更好的选择是重新组织您的数据或B)使用更多的php逻辑以不同的方式呈现/格式化数据时
每个搜索案例中的计数代理可能不同。如果在上面的示例中搜索"504","1083:No",则在这两种情况下都不是2。我建议你像这样修改你的脚本:
<?php
$array = array('504','1083');
//db connection goes here
$th="";
$tr="";
foreach($array as $arr) {
$srch=$arr;
$sql=mysql_query("select qno,paid_response from paid_response where qno=$srch ");
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 ");
$th.="<th>CountAgent(".$arr.")</th><th>(".$arr.")-COUNT</th>";
while($row=mysql_fetch_array($sql)) {
$tr.="<td>".$row['agent_cnt']."</td><td>".$row['ppicount']."</td>";
}
}
?>
<table border="1">
<thead>
<tr>
<?php echo $th; ?>
</tr>
</thead>
<tbody>
<tr style="color:red" >
<?php echo $tr; ?>
</tr>
</tbody>
</table>
好的,这是不完整的答案。不完整,因为我懒得为您编写特定的查询。但我正在写如何做到这一点。
注意:阅读规范化。你会看到你的错误。
如果您可以通过在数据开头添加|
来修改数据,即1001:|1083:|504:Yes|1009:|
|1001:|1083:|504:Yes|1009:|
将对您有很大帮助。
现在,您可以轻松搜索文本搜索%|<ID>:%
这确保您找到 504 或 1009 或任何数字,方法是将该数字替换为<ID>
。
你读过规范化吗?无论如何。
完成此操作后,您现在可以执行行到列概念(我认为它被称为 PIVOT 查询,在谷歌规范化时谷歌它。这里给出了一些例子
MySQL 行到列或Mysql 查询以动态将行转换为列
很抱歉答案不完整,但文本搜索是您最好的选择,最好在复杂查询中使用它之前创建它的视图。但实际上获取数据会非常慢,不建议这样做。
希望你找到正常化...如果没有,请尝试以下操作: https://www.google.co.in/#q=normalization+in+database
哦,别忘了索引该列...