Как изменить SQL-запрос с моим требованием?
В основном это - сводная таблица.
Хороший учебник о том, как этого достичь, можно найти здесь: http://www.artfulsoftware.com/ Обновить
g15]
После того, как ссылка выше в настоящее время недоступна, я чувствую себя обязанным предоставить дополнительную информацию для всех вас, ищущих ответы на mysql pivot. У него действительно было огромное количество информации, и я не буду вкладывать все оттуда сюда (еще больше, так как я просто не хочу копировать свои обширные знания), но я дам несколько советов о том, как справляться с точкой опоры таблицы sql в общем случае с примером из peku, который задал вопрос в первую очередь.
Возможно, ссылка скоро вернется, я буду следить за ней.
Метод электронной таблицы ...
Для этой цели многие используют инструмент MSExcel, OpenOffice или другие инструменты для работы с электронными таблицами. Это действительное решение, просто скопируйте данные и используйте инструменты, предлагаемые графическим интерфейсом для решения этой проблемы.
Но ... это был не вопрос, и это могло бы даже привести к некоторым недостаткам, например, как получить данные в электронную таблицу, проблемное масштабирование и т. д.
Путь SQL ...
Учитывая, что его таблица выглядит примерно так:
CREATE TABLE `test_pivot` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`company_name` varchar(32) DEFAULT NULL,
`action` varchar(16) DEFAULT NULL,
`pagecount` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM;
Теперь загляните в его / ее желаемую таблицу:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
Строки (EMAIL, PRINT x pages) напоминают условия. Основная группировка - company_name.
Чтобы настроить условия, это скорее кричит для использования CASE -стратега. Чтобы группировать что-то, хорошо, используйте ... GROUP BY.
Основной SQL, предоставляющий этот опорный элемент, может выглядеть примерно так:
SELECT P.`company_name`,
COUNT(
CASE
WHEN P.`action`='EMAIL'
THEN 1
ELSE NULL
END
) AS 'EMAIL',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '1'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 1 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '2'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 2 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '3'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 3 pages'
FROM test_pivot P
GROUP BY P.`company_name`;
Это должно обеспечить желаемый результат очень быстрый. Главным недостатком такого подхода является то, что чем больше строк вы хотите в сводной таблице, тем больше условий вам необходимо определить в вашем SQL-заявлении.
Это также может быть рассмотрено, поэтому люди склонны использовать подготовленные операторов, подпрограмм, счетчиков и т. д.
Некоторые дополнительные ссылки по этой теме:
2 ответа
Вы должны попытаться изменить свой SQL на более новую нотацию JOIN , поскольку это значительно упростит подобные вещи, тогда вы можете использовать LEFT JOIN , чтобы указать необязательный стол ...
SELECT a.cusName, a.cusMob, a.invoiceNo,
a.invoiceDate, a.total_VAT, a.bill_tot,
b.itemsName ,b.rate, b.amt_vat,
ROUND(b.amt_vat + b.amount, 2) as amount
FROM invoices a
LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
WHERE a.invoiceDate between '$getfromdate' and '$gettodate'
and a.status IS NULL
order by a.invoiceID desc
Я бы также рекомендовал вам посмотреть на использование подготовленных высказываний , поскольку они имеют много преимуществ.
Если вы хотите получить результат также, когда invoice_items не имеет значения, вы должны использовать левое соединение, и если null
$sql = "SELECT a.cusName
, a.cusMob
, a.invoiceNo
, a.invoiceDate
, a.total_VAT
, a.bill_tot
, b.itemsName
, b.rate
, b.amt_vat
, ROUND(b.amt_vat + inull(b.amount,0), 2) as amount
FROM invoices a
LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
WHERE a.invoiceDate between '$getfromdate'
and '$gettodate' and a.status IS NULL
order by a.invoiceID desc";
Вам также следует избегать старых синта неявных соединений, основанных на, где и использовать явный синтаксис соединений, также позволяют использовать левое соединение (внешнее соединение)
вам также следует избегать использования php var в вашем sql-коде ... вы рискуете для sqlinjection .. для этого взгляните на драйвер db для операторов prpared и bindig пары