SELECT LEFT(GROUP_CONCAT(stitle),10) FROM (SELECT TRIM(',' FROM stitle) AS stitle FROM `book` ORDER BY time DESC LIMIT 5)a等优化
本帖最后由 xuzuning 于 2012-04-11 17:32:38 编辑
DROP TABLE IF EXISTS `t`; create table t(id int primary key AUTO_INCREMENT,stitle varchar(10),time date);insert into t values ('','a,b,',STR_TO_DATE('2011-01-01','%Y-%m-%d' ));insert into t values ('','c,d,',STR_TO_DATE('2011-01-02','%Y-%m-%d' ));insert into t values ('','a, ',STR_TO_DATE('2011-01-03','%Y-%m-%d' ));insert into t values ('','b,e,',STR_TO_DATE('2011-01-04','%Y-%m-%d' ));insert into t values ('','c, ',STR_TO_DATE('2011-01-05','%Y-%m-%d' ));select * from (select id,substring(stitle,1,1) as newsitle,time,1 as order1 from t union all select id,substring(stitle,3,1) as newsitle,time,2 as order1 from t ) a where a.newsitle != ' ' order by time desc,order1 asc limit 0,5; +----+----------+------------+--------+ | id | newsitle | time | order1 | +----+----------+------------+--------+ | 5 | c | 2011-01-05 | 1 | | 4 | b | 2011-01-04 | 1 | | 4 | e | 2011-01-04 | 2 | | 3 | a | 2011-01-03 | 1 | | 2 | c | 2011-01-02 | 1 | +----+----------+------------+--------+
create table t(id int primary key AUTO_INCREMENT,stitle varchar(10),time date);insert into t values ('','a,b,',STR_TO_DATE('2011-01-01','%Y-%m-%d' ));insert into t values ('','c,d,',STR_TO_DATE('2011-01-02','%Y-%m-%d' ));insert into t values ('','a, ',STR_TO_DATE('2011-01-03','%Y-%m-%d' ));insert into t values ('','b,e,',STR_TO_DATE('2011-01-04','%Y-%m-%d' ));insert into t values ('','c, ',STR_TO_DATE('2011-01-05','%Y-%m-%d' ));select * from
(select id,substring(stitle,1,1) as newsitle,time,1 as order1 from t
union all
select id,substring(stitle,3,1) as newsitle,time,2 as order1 from t ) a
where a.newsitle != ' ' order by time desc,order1 asc limit 0,5;
+----+----------+------------+--------+
| id | newsitle | time | order1 |
+----+----------+------------+--------+
| 5 | c | 2011-01-05 | 1 |
| 4 | b | 2011-01-04 | 1 |
| 4 | e | 2011-01-04 | 2 |
| 3 | a | 2011-01-03 | 1 |
| 2 | c | 2011-01-02 | 1 |
+----+----------+------------+--------+