Получение определенных значений ключа из запроса и выборка их пары в запросе
У меня есть стол 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
Решение
<?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');
Попробуй этого парня!
Другие решения
Попробуй это:
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 , '''|%''');';
Выполните эту строку в своем коде, чтобы вы могли получить динамический запрос для получения подсчета
Повторяя комментарии других. Вы, вероятно, должны нормализовать свою модель.
Тем не менее, невозможно получить требуемый результат, просто решение будет неэффективным, не масштабируемым, трудным для понимания новыми разработчиками и не слишком расширяемым.
Кроме того, легче извлечь данные в длинном формате, а не в широком, т.е.
# 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 demo
Предполагая, что формат данных остается прежним, вы можете попробовать что-то вроде этого (не проверено):
$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;
Как вы можете видеть это некрасиво.
А) Ваш лучший вариант — реорганизовать ваши данные
или же
Б) При представлении / форматировании ваших данных по-другому с большей логикой php
Количество агентов может быть разным в каждом случае поиска. Если в приведенном выше примере вы ищете «504», «1083: Нет», то в обоих случаях это не 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> с этим номером.
Вы уже читали нормализацию? В любом случае.
как только вы это сделали, теперь вы можете использовать концепцию Row to Column (я думаю, что это известно как PIVOT Queries, Google его, пока вы Google нормализации.). Некоторые примеры приведены здесь
MySQL строка в столбец
Или же
Mysql-запрос для динамического преобразования строк в столбцы
Извините за неполный ответ, но текстовый поиск — лучший выбор, и лучше создать его, прежде чем использовать его в сложных запросах. Но на самом деле это будет ОЧЕНЬ МЕДЛЕННО Получить данные и не рекомендуется.
Надеюсь, вы найдете нормализацию еще … если не попробуйте это: https://www.google.co.in/#q=normalization+in+database
О, и не забудьте проиндексировать этот столбец …