当前位置:Gxlcms > PHP教程 > mysql怎么打印数组中最新的购买的产品信息

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

时间:2021-07-01 10:21:17 帮助过:7人阅读

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

SQL code

字段  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

------解决方案--------------------
我觉得逆序搜5条就好了

然后纳入数组,,因为重复不足5项,重复这个过程
------解决方案--------------------
其实他必有一个产品目录为何不利用一下呢
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

------解决方案--------------------
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 |
+----+----------+------------+--------+                     

人气教程排行