Разные SQL-запросы

Материал из BiTel WiKi

(Различия между версиями)
Перейти к: навигация, поиск
(добавил скрипт)
Строка 1: Строка 1:
 +
== Тарифы договора ==
 +
Активные на сегодня тарифы для договора
 +
<source lang="sql">
 +
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
 +
</source>
 +
или лучше
 +
<source lang="sql">
 +
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())
 +
</source>
 +
--[[Участник:DimOn|dimOn]] 07:49, 18 февраля 2010 (UTC)
 +
== Активные договоры без тарифов ==
== Активные договоры без тарифов ==

Версия 07:49, 18 февраля 2010

Содержание

Тарифы договора

Активные на сегодня тарифы для договора

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)

Активные договоры без тарифов

Все договоры, у которых статус "активен" и у которых на сегодня нет ни одного тарифного плана.

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)

Дублированные тарифы

Показывает сколько в каждом договоре каждых тарифов, по убыванию. Было сделано для поиска одинаковых тарифов в договорах. Правда, не учитывает даты (добавить несложно), но для поиска дубляжей сойдёт.

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
Личные инструменты