mysql怎么打印数组中最新的购买的产品信息
程序员文章站
2022-05-12 10:26:44
...
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 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 |
+----+----------+------------+--------+
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。
相关文章
相关视频