订单流水号=年月日+插入时记录的ID，如何实现？
如题，我要在订单表里生成订单记录，订单表有流水号，流水号要求不可重复，我想把这个流水号的生成规则设置为：当天的日期+订单ID，如当前时间是20120330，前一条订单id是5，那新要求新生成的订单流水号为：201203306，这个要怎么实现？最好能一个SQL语句来实现的，或者效率高些的。。谢谢！
[解决办法]
SELECT CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),RIGHT(CONCAT('00',(SELECT MAX(id) FROM `attention`)+1),2))
[解决办法]
mysql> CREATE TABLE `order` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> cdate date, -> `oid` int(11) NOT NULL, -> PRIMARY KEY (cdate,`id`) -> );Query OK, 0 rows affected (0.12 sec)mysql> insert into `order`(cdate,oid) values -> (curdate(),123), -> (curdate(),124), -> (curdate(),125), -> (curdate()+interval 1 day,126), -> (curdate()+interval 1 day,127), -> (curdate()+interval 1 day,128);Query OK, 6 rows affected (0.05 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from `order`;+----+------------+-----+| id | cdate | oid |+----+------------+-----+| 1 | 2012-03-30 | 123 || 2 | 2012-03-30 | 124 || 3 | 2012-03-30 | 125 || 1 | 2012-03-31 | 126 || 2 | 2012-03-31 | 127 || 3 | 2012-03-31 | 128 |+----+------------+-----+6 rows in set (0.00 sec)mysql> select concat(DATE_FORMAT(cdate,'%Y%m%d'),right(1000+id,3)) as sn,oid from `order`;+-------------+-----+| sn | oid |+-------------+-----+| 20120330001 | 123 || 20120330002 | 124 || 20120330003 | 125 || 20120331001 | 126 || 20120331002 | 127 || 20120331003 | 128 |+-------------+-----+6 rows in set (0.00 sec)mysql>
