Помогите с mysql запросом по выводу баланса

V
На сайте с 31.01.2008
Offline
118
749

помогите плиз с запросом

есть таблица

CREATE TABLE IF NOT EXISTS `order` (

`id` int(11) NOT NULL auto_increment,

`id_adv` int(11) NOT NULL,

`sum` int(11) NOT NULL,

`date` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `id_adv` (`id_adv`,`sum`,`date`)

)

эта таблица хранит в себе суммы оплат за показ объявлений (id_adv) из таблицы adv

например 1 марта на счет обявления id_adv = 1 упала сумма sum=100

это означает что это объявление будет показываться 100 дней до 8 июня, те каждый день со счета баланса объявления условно будет сниматься 1, например при запросе на 10 марта баланс объявления будет 90

так вот нужно показать баланс объявления на определенную дату, с учетом того что юзер может пополнить баланс этого объявления сколько угодно раз в любое время

пример 1 марта на счет обявления id_adv = 1 упала сумма sum=20

это означает что это объявление будет показываться 20 дней до 20 марта, но 15 марта юзер добавил на счет этого же объявления еще sum=50

как выполнить запрос например на 31 марта что бы получить баланс с учетом всех платежей

пример:

id id_adv sum date

1 5 20 1-03-2012

2 5 50 15-03-2012

1 марта баланс = 20 платеж 20 (запись в таблице)

10 марта баланс = 10 остаток по истечении 10 дней

11 марта баланс = 9

...

14 марта баланс = 6

15 марта баланс = 55 , (платеж 50 запись в таблице) + 5 остаток от прошлого платежа

31 марта баланс = 39

или

id id_adv sum date

1 5 10 1-03-2012

2 5 50 15-03-2012

1 марта баланс = 10 платеж 10 (запись в таблице)

10 марта баланс = 0 остаток по истечении 10 дней

11 марта баланс = 0

... промежуток не активности объявления

14 марта баланс = 0

15 марта баланс = 50 платеж 50 (запись в таблице)

31 марта баланс = 34 остаток по истечении 16 дней

заранее благодарен за помощь

Funaki
На сайте с 13.09.2008
Offline
85
#1


SELECT IFNULL(ITOG1,0) AS BALANS
FROM (
SELECT MAX(SUM_ACC) AS ITOG1
FROM (
/*ТАБЛИЦА БАЛАНСА ПО ДНЯМ*/
SELECT NUM_ROW, NUM, CUR_DAY,
CASE WHEN NUM = 0
THEN @N3:=@N3 + SM2
ELSE CASE WHEN @N3-1 > 0 THEN @N3:=@N3-1 ELSE @N3:=0 END
END SUM_ACC
FROM (
SELECT A1.NUM_ROW, A2.SM SM2, A3.NUM,
CASE WHEN DATEDIFF(A1.DT, A2.DT) = NUM
THEN A1.DT
ELSE DATE_ADD(A2.DT, INTERVAL NUM DAY)
END AS CUR_DAY
FROM
(
SELECT @n1 := @n1 +1 AS NUM_ROW, SM, DT, ID
FROM (
SELECT `sum` as sm, `date` as dt, `id` AS ID
FROM `order`
WHERE `id_adv` = 1 AND `date` <= '2012-03-31 15:00:00'
UNION ALL
SELECT 0, '2012-03-31 15:00:00' IN_DATE , 0
ORDER BY DT, ID ASC) T1, (SELECT @n1 :=0 ) AS T2
) A1,
(
SELECT @n2 := @n2 +1 AS NUM_ROW, SM, DT, ID
FROM (
SELECT `sum` as sm, `date` as dt, `id` as id
FROM `order`
WHERE `id_adv` = 1 AND `date` <= '2012-03-31 15:00:00'
UNION ALL
SELECT 0, '2012-03-31 15:00:00' IN_DATE, 0
ORDER BY DT, ID ASC) T1, (SELECT @n2 :=0 ) AS T2
) A2,
(
/*Числовая последовательность на 620 дней*/
SELECT 5*5*5*(a-1)+5*5*(b-1) + 5*(c-1) + d AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z CROSS JOIN
(SELECT 1 d UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) w
WHERE 5*5*5*(a-1)+5*5*(b-1) + 5*(c-1) + d <= 620
UNION ALL
SELECT 0
ORDER BY 1
) A3
WHERE (A1.NUM_ROW = A2.NUM_ROW + 1)
AND (DATEDIFF(A1.DT, A2.DT) >= A3.NUM)
ORDER BY A1.NUM_ROW, A3.NUM
) B1, (SELECT @N3:=0) B2
ORDER BY NUM_ROW, NUM
) RES
WHERE CUR_DAY = '2012-03-31 15:00:00' ) XXXX

Вместо '2012-03-31 15:00:00' ставь дату за какой день баланс (!учитывается время)

V
На сайте с 31.01.2008
Offline
118
#2
Funaki:

Вместо '2012-03-31 15:00:00' ставь дату за какой день баланс (!учитывается время)

нихрена себе запросец :), сначала решил что это апрельская шутка,

хотя в запросе есть и не понятные для меня решения, например:


/*Числовая последовательность на 620 дней*/
SELECT 5*5*5*(a-1)+5*5*(b-1) + 5*(c-1) + d AS num

но проверил и запрос дает верный результат!

ща разберусь с ним более плотно и отпишу, спасибо

Funaki
На сайте с 13.09.2008
Offline
85
#3
vitvvs:
нихрена себе запросец :), сначала решил что это апрельская шутка,
хотя в запросе есть и не понятные для меня решения, например:

/*Числовая последовательность на 620 дней*/
SELECT 5*5*5*(a-1)+5*5*(b-1) + 5*(c-1) + d AS num

как есть так есть

это генерация столбца со значениями от 1 до 620, если есть возможность создавать таблицы, то лучше создать вспомогательную таблицу с последовательностью, в итоге тогда будет быстрее работать запрос

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий