mysql怎么打印数组中最新的购买的产品信息

WBOY

發布： 2016-06-13 13:27:27

原創

881 人瀏覽過

mysql如何打印数组中最新的购买的产品信息
有一个book表（stitle是产品名称，一个ID可以购买多个book，如ID为1的用户购买 a,b 2个产品）

SQL code

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->
字段  id      stitle    time

      1       a,b,     2011-01-01

      2       c,d,     2011-01-02

      3       a,       2011-01-03
 
      4       b,e,     2011-01-04

      5       c,       2011-01-05

登入後複製

现在请教如何打印最新的前5条购买产品的名称，显示的顺序应该是：

c,b,e,a,d

------解决方案--------------------

SQL code

SELECT LEFT(GROUP_CONCAT(stitle),10) FROM (SELECT TRIM(',' FROM stitle) AS stitle FROM `book` ORDER BY time DESC LIMIT 5)a
<br><font color="#e78608">------解决方案--------------------</font><br>我觉得逆序搜5条就好了<br><br>然后纳入数组，，因为重复不足5项，重复这个过程
<br><font color="#e78608">------解决方案--------------------</font><br>其实他必有一个产品目录为何不利用一下呢

登入後複製

SQL code

DROP TABLE IF EXISTS `t1`;  
create table t1(id int primary key AUTO_INCREMENT,stitle varchar(10),time date);

insert into t1 values (null,'a,b','2011-01-01');
insert into t1 values (null,'c,d','2011-01-02');
insert into t1 values (null,'a','2011-01-03');
insert into t1 values (null,'b,e','2011-01-04');
insert into t1 values (null,'c','2011-01-05');


DROP TABLE IF EXISTS `t2`;  
create table t2(id int primary key AUTO_INCREMENT,name varchar(10));

insert into t2 values (null,'a');
insert into t2 values (null,'b');
insert into t2 values (null,'c');
insert into t2 values (null,'d');
insert into t2 values (null,'e');
insert into t2 values (null,'f');

select t1.id, t2.name as stitle, t1.time from t1, t2 where find_in_set(t2.name, t1.stitle) order by t1.time desc limit 5
<br><font color="#e78608">------解决方案--------------------</font><br>

登入後複製

SQL code



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 |
+----+----------+------------+--------+ <div class="clear">
                 
              
              
        
            </div>

登入後複製