Кто нибудь реализовывал для себя выборку из базы?
день мес. Фалмилия потреблено(клтраф1) потреблено(клтраф2)
Несколько полезных SQL запросов
дошел до такого
select full_name,from_unixtime(discount_date,'%Y-%m-%d') as data,sum(bytes) from UTM5.discount_transactions_iptraffic_all,UTM5.users where discount_transactions_iptraffic_all.account_id=users.id and users.id='161' and t_class='10' group by users.id,from_unixtime(discount_date,'%Y-%m-%d');
+------------------------------------------------+------------+------------+
| full_name | data | sum(bytes) |
+------------------------------------------------+------------+------------+
| xxxxxxxxxxxxxxxxxxx | 2009-02-01 | 2828047 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-02 | 13852551 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-03 | 26005194 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-04 | 993679 |
+------------------------------------------------+------------+------------+
'это по классу трафика 10. подскаите как по одновременно еще дописать запрос для класса 200.
т.е. еще колонку вывести класс 200
select full_name,from_unixtime(discount_date,'%Y-%m-%d') as data,sum(bytes) from UTM5.discount_transactions_iptraffic_all,UTM5.users where discount_transactions_iptraffic_all.account_id=users.id and users.id='161' and t_class='10' group by users.id,from_unixtime(discount_date,'%Y-%m-%d');
+------------------------------------------------+------------+------------+
| full_name | data | sum(bytes) |
+------------------------------------------------+------------+------------+
| xxxxxxxxxxxxxxxxxxx | 2009-02-01 | 2828047 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-02 | 13852551 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-03 | 26005194 |
| xxxxxxxxxxxxxxxxxxx | 2009-02-04 | 993679 |
+------------------------------------------------+------------+------------+
'это по классу трафика 10. подскаите как по одновременно еще дописать запрос для класса 200.
т.е. еще колонку вывести класс 200
-
plaguekriz
- Сообщения: 64
- Зарегистрирован: Пн июн 15, 2009 15:28
SQL для основного отчета:
Пимечание: не забудьте поменять даты
Код: Выделить всё
SELECT `users`.`id`, `users`.`login`, `users`.`full_name`, `users`.`home_telephone`,
`main_rep`.`account_id`, `main_rep`.`incoming_rest`, `main_rep`.`once_service`, `main_rep`.`period_service`,
`main_rep`.`iptraf_service`, `main_rep`.`hotspot_service`, `main_rep`.`dialup_service`, `main_rep`.`iptel_service`,
`main_rep`.`nds`, `main_rep`.`sum_with_nds`, `main_rep`.`payment`, `main_rep`.`outgoing_rest`
FROM `users`,`users_accounts`,
(SELECT `nach`.`account_id`,
`in_rest`.`incoming_rest` as `incoming_rest`,
SUM(CASE `nach`.`service_type` WHEN 1 THEN `nach`.`discount` ELSE 0 END) as `once_service`,
SUM(CASE `nach`.`service_type` WHEN 2 THEN `nach`.`discount` ELSE 0 END) as `period_service`,
SUM(CASE `nach`.`service_type` WHEN 3 THEN `nach`.`discount` ELSE 0 END) as `iptraf_service`,
SUM(CASE `nach`.`service_type` WHEN 4 THEN `nach`.`discount` ELSE 0 END) as `hotspot_service`,
SUM(CASE `nach`.`service_type` WHEN 5 THEN `nach`.`discount` ELSE 0 END) as `dialup_service`,
SUM(CASE `nach`.`service_type` WHEN 6 THEN `nach`.`discount` ELSE 0 END) as `iptel_service`,
(SUM(`nach`.`discount_with_tax`) - SUM(`nach`.`discount`)) as `nds`,
SUM(CASE `nach`.`service_type` WHEN 0 THEN 0 ELSE `nach`.`discount_with_tax` END) as `sum_with_nds`,
SUM(CASE `nach`.`service_type` WHEN 0 THEN `nach`.`discount` ELSE 0 END)*(-1) as `payment`,
`out_rest`.`outgoing_rest` as `outgoing_rest`
FROM ( SELECT SUM(`discount`) as `discount`,SUM(`discount_with_tax`) as `discount_with_tax`,`service_type`,`account_id`
FROM `discount_transactions_all`
WHERE (UNIX_TIMESTAMP('<ДАТА С>') <= `discount_date`)AND(`discount_date` < UNIX_TIMESTAMP('<ДАТА ПО>'))
GROUP BY `service_type`, `account_id` ) `nach`,
( SELECT `temp`.`account_id`,`incoming_rest`
FROM `discount_transactions_all`,
( SELECT `account_id`,MIN(id) as `id_in_rest`
FROM `discount_transactions_all`
WHERE (UNIX_TIMESTAMP('<ДАТА С>') <= `discount_date`)AND(`discount_date` < UNIX_TIMESTAMP('<ДАТА ПО>'))
GROUP BY `account_id` ) `temp`
WHERE `id` = `id_in_rest` ) `in_rest`,
( SELECT `temp`.`account_id`,`outgoing_rest`
FROM `discount_transactions_all`,
( SELECT `account_id`,MAX(id) as `id_out_rest`
FROM `discount_transactions_all`
WHERE (UNIX_TIMESTAMP('<ДАТА С>') <= `discount_date`)AND(`discount_date` < UNIX_TIMESTAMP('<ДАТА ПО>'))
GROUP BY `account_id` ) `temp`
WHERE `id` = `id_out_rest`) `out_rest`
WHERE (`nach`.`account_id` = `in_rest`.`account_id`) AND (`nach`.`account_id` = `out_rest`.`account_id`)
GROUP BY `nach`.`account_id`
ORDER BY `nach`.`account_id`) `main_rep`
WHERE (`users`.`id` = `users_accounts`.`uid`) and (`users_accounts`.`account_id` = `main_rep`.`account_id`)
eugeneA писал(а):А не подскажете запрос - пользователи которые раньше платили, а теперь почему-то нет?
Код: Выделить всё
SELECT account_id
FROM payment_transactions
GROUP BY account_id
HAVING MAX(payment_enter_date)<unix_timestamp('2010-01-01');А как можно сделать чтобы выводилось еще поле полное имя?dk писал(а):eugeneA писал(а):А не подскажете запрос - пользователи которые раньше платили, а теперь почему-то нет?Код: Выделить всё
SELECT account_id FROM payment_transactions GROUP BY account_id HAVING MAX(payment_enter_date)<unix_timestamp('2010-01-01');
Puzan_aga писал(а): А как можно сделать чтобы выводилось еще поле полное имя?
Код: Выделить всё
SELECT payment_transactions.account_id AS id, users.full_name, accounts.balance, from_unixtime( max( payment_enter_date ) ) AS date FROM payment_transactions, users, accounts, users_accounts WHERE (accounts.id = payment_transactions.account_id) AND (users.id = uid) AND (accounts.id = users_accounts.account_id) AND (accounts.is_deleted =0) AND (accounts.balance >0) GROUP BY payment_transactions.account_id HAVING max( payment_enter_date ) < unix_timestamp( date_sub( curdate( ) , INTERVAL 6 MONTH ));
-
mikkey finn
- Сообщения: 1612
- Зарегистрирован: Пт ноя 10, 2006 15:23