货币兑换表设计mysql_带范围的Mysql查询案例和带货币的案例

我有一个表’order’,其中列几乎看起来像这样：

| order_id | shop_id | order_total | currency | created_at |

现在我想进行查询,返回一个如下所示的表：

| interval | currency | sum |

其中interval是日期的间隔,currency是货币,sum是该区间中该货币的所有订单的总和.一个例子可能是：

| 2012-08-12-2012-09-12 | EUR | 540922 |

| 2012-08-12-2012-09-12 | DKK | 43215 |

| 2012-09-12-2012-10-12 | EUR | 123643 |

| 2012-09-12-2012-10-12 | DKK | 0 |

我已经有了一个指导货币的查询,但我不知道如何让它考虑到货币..任何想法？

下面是我现在使用的查询示例.真正的查询当然是由代码生成的,这只是一个例子.

SELECT CASE

WHEN created_at BETWEEN '2012-08-12' AND '2012-09-12' THEN '2012-08-12-2012-09-12'

WHEN created_at BETWEEN '2012-09-12' AND '2012-10-12' THEN '2012-09-12-2012-10-12'

WHEN created_at BETWEEN '2012-10-12' AND '2012-10-14' THEN '2012-10-12-2012-10-14'

end AS intrvl,

Sum(order_total)

FROM `order` o

WHERE shop_id = 4

AND created_at BETWEEN '2012-08-12' AND '2012-11-17'

GROUP BY CASE

WHEN created_at BETWEEN '2012-08-12' AND '2012-09-12' THEN '2012-08-12-2012-09-12'

WHEN created_at BETWEEN '2012-09-12' AND '2012-10-12' THEN '2012-09-12-2012-10-12'

WHEN created_at BETWEEN '2012-10-12' AND '2012-10-14' THEN '2012-10-12-2012-10-14'

end

/莫滕