mysql怎么打印数组中最新的购买的产品信息
程序员文章站
2024-02-10 22:54:28
...
mysql如何打印数组中最新的购买的产品信息
有一个book表（stitle是产品名称，一个ID可以购买多个book，如ID为1的用户购买 a,b 2个产品）
现在请教如何打印最新的前5条购买产品的名称，显示的顺序应该是：
c,b,e,a,d
------解决方案--------------------
有一个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 tunion allselect id,substring(stitle,3,1) as newsitle,time,2 as order1 from t ) awhere 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 |+----+----------+------------+--------+
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。
相关文章
相关视频