欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

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

程序员文章站 2024-02-12 09:10:46
...
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 |
+----+----------+------------+--------+ 
mysql怎么打印数组中最新的购买的产品信息

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。

相关文章

相关视频