Imam sledecu tabelu u bazi:
Code:
id month price
--------------------------------------
1 12 74.00
2 11 27.00
3 12 35.00
4 12 85.00
5 12 38.00
6 04 45.00
7 03 27.00
8 12 23.00
9 12 45.00
10 03 36.00
11 12 67.00
12 06 45.00
13 12 23.00
14 12 35.00
id month price
--------------------------------------
1 12 74.00
2 11 27.00
3 12 35.00
4 12 85.00
5 12 38.00
6 04 45.00
7 03 27.00
8 12 23.00
9 12 45.00
10 03 36.00
11 12 67.00
12 06 45.00
13 12 23.00
14 12 35.00
Kako izabrati slucajne redove iz tabele a da zbir kolone price bude izmedju 100 i 150, na primer na prvi upit da dobijem ovo:
Code:
id month price
--------------------------------------
1 12 74.00
5 12 38.00
14 12 35.00
id month price
--------------------------------------
1 12 74.00
5 12 38.00
14 12 35.00
znaci dobio sam zbir 147
na sledeci upit primer dobijem ovo:
Code:
id month price
--------------------------------------
3 12 35.00
5 12 38.00
8 12 23.00
14 12 35.00
id month price
--------------------------------------
3 12 35.00
5 12 38.00
8 12 23.00
14 12 35.00
i uvek da mi izbaci random rows ali da zbir bude izmedju 100 i 150 ili npr. max 150, ili <= 150 ili kako vec...
Pokusao sam nesto tipa:
Code:
SELECT id,month,price,SUM(price)
FROM orders
WHERE month = '12' AND SUM(price) < 150 AND SUM(price) > 100
ORDER BY rand()
SELECT id,month,price,SUM(price)
FROM orders
WHERE month = '12' AND SUM(price) < 150 AND SUM(price) > 100
ORDER BY rand()
ali nije to to,onda sam nasao nesto ovako na nekom forumu, radi ali nece da ga pomesa (random), mozda moze ovo malo i vama da pomogne:
Code:
SELECT
O.id,
O.month,
O.price,
(SELECT
sum(price) FROM orders
WHERE month = '12' AND id <= O.id ) 'Total'
FROM orders O WHERE month = '12'
HAVING Total <=150
SELECT
O.id,
O.month,
O.price,
(SELECT
sum(price) FROM orders
WHERE month = '12' AND id <= O.id ) 'Total'
FROM orders O WHERE month = '12'
HAVING Total <=150
googlam vec dva dana i ne mogu da resim, da li moze neko malo da pomogne ?