Задача: посмотреть сколько денег было потерянно на отмененных заказах:
Решение:
SELECT t.orders_id,o.date_purchased, t.value
, s.orders_status_id, st.orders_status_name
, (case s.orders_status_id when 4 then t.value else 0 end) canceled
, (case s.orders_status_id when 3 then t.value else 0 end) delivered
FROM `orders_total` t, orders o
,orders_status_history s
,orders_status st
WHERE
t.orders_id=o.orders_id
and t.class='ot_total'
and s.orders_id=o.orders_id
and st.orders_status_id=s.orders_status_id
and s.orders_status_history_id=(select max(s1.orders_status_history_id) from orders_status_history s1 where s1.orders_id=o.orders_id group by s1.orders_id)
order by o.date_purchased desc;
Результат:
orders_id
|
date_purchased |
value |
orders_status_id |
orders_status_name |
canceled |
delivered |
| 310 |
2010-12-29 19:29:09 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |
| 309 |
2010-12-28 18:14:13 |
2950.0000 |
2 |
В обработке |
0.0000 |
0.0000 |
| 308 |
2010-12-28 12:36:32 |
2895.0000 |
4 |
Отменен |
2895.0000 |
0.0000 |
| 307 |
2010-12-26 23:47:54 |
1002.0000 |
4 |
Отменен |
1002.0000 |
0.0000 |
| 306 |
2010-12-24 16:28:59 |
3513.0000 |
1 |
В ожидании |
0.0000 |
0.0000 |
| 305 |
2010-12-24 16:03:10 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |
| 304 |
2010-12-23 17:57:48 |
3631.0000 |
4 |
Отменен |
3631.0000 |
0.0000 |
| 303 |
2010-12-23 15:54:17 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |
| 302 |
2010-12-23 00:45:34 |
3435.0000 |
4 |
Отменен |
3435.0000 |
0.0000 |
| 301 |
2010-12-22 16:26:13 |
17900.0000 |
4 |
Отменен |
17900.0000 |
0.0000 |
| 300 |
2010-12-22 12:44:10 |
1166.0000 |
4 |
Отменен |
1166.0000 |
0.0000 |
| 299 |
2010-12-21 22:48:16 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |
| 298 |
2010-12-21 14:21:18 |
17900.0000 |
4 |
Отменен |
17900.0000 |
0.0000 |
| 297 |
2010-12-20 17:36:44 |
1516.0000 |
4 |
Отменен |
1516.0000 |
0.0000 |
| 296 |
2010-12-20 13:07:16 |
2600.0000 |
4 |
Отменен |
2600.0000 |
0.0000 |
| 295 |
2010-12-20 10:41:19 |
1324.0000 |
4 |
Отменен |
1324.0000 |
0.0000 |
| 294 |
2010-12-20 02:12:01 |
1760.0000 |
4 |
Отменен |
1760.0000 |
0.0000 |
| 293 |
2010-12-20 01:14:21 |
370.0000 |
4 |
Отменен |
370.0000 |
0.0000 |
| 292 |
2010-12-15 15:54:30 |
1116.0000 |
4 |
Отменен |
1116.0000 |
0.0000 |
| 291 |
2010-12-13 02:11:44 |
510.0000 |
4 |
Отменен |
510.0000 |
0.0000 |
| 290 |
2010-12-12 15:08:08 |
748.0000 |
4 |
Отменен |
748.0000 |
0.0000 |
| 289 |
2010-12-11 15:36:55 |
1189.0000 |
4 |
Отменен |
1189.0000 |
0.0000 |
| 288 |
2010-12-10 12:36:33 |
2400.0000 |
3 |
Доставлен |
0.0000 |
2400.0000 |
| 287 |
2010-12-07 15:22:32 |
10843.0000 |
1 |
В ожидании |
0.0000 |
0.0000 |
| 286 |
2010-12-07 15:16:19 |
8008.0000 |
1 |
В ожидании |
0.0000 |
0.0000 |
| 285 |
2010-12-06 19:58:11 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |
| 284 |
2010-12-04 22:03:37 |
10960.0000 |
4 |
Отменен |
10960.0000 |
0.0000 |