Форум Сайтостроение Веб-строительство

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

V
На сайте с 31.01.2008
Offline
125
vitvvs
761

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

есть таблица

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
123
Funaki
#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
125
vitvvs
#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
123
Funaki
#3
vitvvs:
нихрена себе запросец :), сначала решил что это апрельская шутка,
хотя в запросе есть и не понятные для меня решения, например: 

/*Числовая последовательность на 620 дней*/ 

    SELECT 5*5*5*(a-1)+5*5*(b-1) + 5*(c-1) + d AS num

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

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

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