Разные SQL-запросы
Материал из BiTel WiKi
(Различия между версиями)
DimOn (Обсуждение | вклад) |
DimOn (Обсуждение | вклад) |
||
| Строка 1: | Строка 1: | ||
== Тарифы договора == | == Тарифы договора == | ||
| - | Активные на сегодня тарифы для договора | + | Активные на сегодня тарифы для договора. Версия 4.6/5.0 |
<source lang="sql"> | <source lang="sql"> | ||
SELECT tariff_plan.title | SELECT tariff_plan.title | ||
| Строка 14: | Строка 14: | ||
WHERE contract_tariff.cid=352715 AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) | WHERE contract_tariff.cid=352715 AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) | ||
</source> | </source> | ||
| - | |||
--[[Участник:DimOn|dimOn]] 07:49, 18 февраля 2010 (UTC) | --[[Участник:DimOn|dimOn]] 07:49, 18 февраля 2010 (UTC) | ||
== Активные договоры без тарифов == | == Активные договоры без тарифов == | ||
| - | Все договоры, у которых статус "активен" и у которых на сегодня нет ни одного тарифного плана. | + | Все договоры, у которых статус "активен" и у которых на сегодня нет ни одного тарифного плана. Версия 4.6/5.0 |
<source lang="sql"> | <source lang="sql"> | ||
| Строка 27: | Строка 26: | ||
WHERE ct.id IS NULL AND contract.status=0 | WHERE ct.id IS NULL AND contract.status=0 | ||
</source> | </source> | ||
| - | |||
--[[Участник:DimOn|dimOn]] 12:32, 17 февраля 2010 (UTC) | --[[Участник:DimOn|dimOn]] 12:32, 17 февраля 2010 (UTC) | ||
| Строка 34: | Строка 32: | ||
Показывает сколько в каждом договоре каждых тарифов, по убыванию. | Показывает сколько в каждом договоре каждых тарифов, по убыванию. | ||
Было сделано для поиска одинаковых тарифов в договорах. Правда, не учитывает даты (добавить несложно), но для поиска дубляжей сойдёт. | Было сделано для поиска одинаковых тарифов в договорах. Правда, не учитывает даты (добавить несложно), но для поиска дубляжей сойдёт. | ||
| + | Версия 4.6/5.0 | ||
<source lang="sql"> | <source lang="sql"> | ||
SELECT contract_tariff.cid, contract_tariff.tpid, count(contract_tariff.id) as tariff_count | SELECT contract_tariff.cid, contract_tariff.tpid, count(contract_tariff.id) as tariff_count | ||
| Строка 62: | Строка 61: | ||
вместо <source lang="sql">ORDER BY tariff_count DESC</source> можно сделать <source lang="sql">HAVING tariff_count>1</source> | вместо <source lang="sql">ORDER BY tariff_count DESC</source> можно сделать <source lang="sql">HAVING tariff_count>1</source> | ||
| - | |||
--[[Участник:DimOn|dimOn]] 12:32, 17 февраля 2010 (UTC) | --[[Участник:DimOn|dimOn]] 12:32, 17 февраля 2010 (UTC) | ||
Версия 08:12, 18 февраля 2010
Содержание |
Тарифы договора
Активные на сегодня тарифы для договора. Версия 4.6/5.0
SELECT tariff_plan.title FROM tariff_plan INNER JOIN contract_tariff ON contract_tariff.tpid = tariff_plan.id AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) WHERE contract_tariff.cid=352715
или лучше
SELECT contract_tariff.tpid, contract_tariff.date1, contract_tariff.date2, tariff_plan.title FROM contract_tariff LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid WHERE contract_tariff.cid=352715 AND (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE())
--dimOn 07:49, 18 февраля 2010 (UTC)
Активные договоры без тарифов
Все договоры, у которых статус "активен" и у которых на сегодня нет ни одного тарифного плана. Версия 4.6/5.0
SELECT contract.id, contract.title, contract.comment, contract.STATUS, ct.id FROM contract LEFT JOIN contract_tariff AS ct ON contract.id=ct.cid AND (ct.date1 IS NULL OR ct.date1<=CURDATE()) AND (ct.date2 IS NULL OR ct.date2>=CURDATE()) WHERE ct.id IS NULL AND contract.STATUS=0
--dimOn 12:32, 17 февраля 2010 (UTC)
Дублированные тарифы
Показывает сколько в каждом договоре каждых тарифов, по убыванию. Было сделано для поиска одинаковых тарифов в договорах. Правда, не учитывает даты (добавить несложно), но для поиска дубляжей сойдёт. Версия 4.6/5.0
SELECT contract_tariff.cid, contract_tariff.tpid, count(contract_tariff.id) AS tariff_count FROM contract_tariff GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
или то же самое, с дополнительной инфой:
SELECT count(contract_tariff.id) AS tariff_count, contract_tariff.tpid, contract.id, contract.title, contract.comment, tariff_plan.title FROM contract_tariff LEFT JOIN contract ON contract.id=contract_tariff.cid LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
вот с добавлением учёта дат:
SELECT count(contract_tariff.id) AS tariff_count, contract_tariff.tpid, contract_tariff.cid, contract.title, contract.comment, tariff_plan.title FROM contract_tariff LEFT JOIN contract ON contract.id=contract_tariff.cid LEFT JOIN tariff_plan ON tariff_plan.id=contract_tariff.tpid WHERE (contract_tariff.date1 IS NULL OR contract_tariff.date1<=CURDATE()) AND (contract_tariff.date2 IS NULL OR contract_tariff.date2>=CURDATE()) GROUP BY contract_tariff.cid, contract_tariff.tpid ORDER BY tariff_count DESC
ORDER BY tariff_count DESC
HAVING tariff_count>1
--dimOn 12:32, 17 февраля 2010 (UTC)
Использование тарифов (трафик по договорам)
Нужна таблица Тариф | Количество активных договоров (тех, у кого трафик за месяц больше определённого значения)
ЧЕРНОВИК!!!
# чистыми цифрами: тарифный план -> его использований в договорах SELECT contract_tariff.tpid, count(contract_tariff.id) FROM contract_tariff GROUP BY contract_tariff.tpid
# джойним название SELECT contract_tariff.tpid, tariff_plan.title, count(contract_tariff.id) FROM contract_tariff LEFT JOIN tariff_plan ON contract_tariff.tpid=tariff_plan.id GROUP BY contract_tariff.tpid
# связка cid => сумма трафика за месяц SELECT `session_detail_21_200810`.cid, sum(`session_detail_21_200810`.`amount`) FROM `session_detail_21_200810` WHERE (1) GROUP BY `session_detail_21_200810`.cid
