一个产品表,有多个商家。每个商家有N个产品。
要求让每个商家只能显示一个最新产品,直接group by 商家id后,结果集默认是商家的一条最早的产品。SELECT * FROM tbl WHERE status=1 GROUP BY bizid ORDER BY level DESC;
搜索了下,找到了以下二个方法,但觉得效率都不高(原应用的sql条件太多),想请教下除此外不知还有没有更好的办法?1、SELECT * FROM(SELECT * FROM tbl ORDER BY id DESC) tem_tpl GROUP BY bizid;2、SELECT * FROM tbl WHERE id IN(SELECT MAX(id) FROM tbl GROUP BY bizid);
[征集]分组取最大N条记录方法征集,及散分....
exists只是判断条件,只要倒序恐怕不行吧?
id int(10) primary key,
biz_id int(10),
pronduct_ame varchar(50),
status tinyint(1),
levels int(10)
) ENGINE=MyISAM;INSERT INTO products VALUES
(1,'1','书籍1',1,10),
(2,'1','书籍2',1,10),
(3,'1','书籍3',1,10),
(4,'2','教材1',1,22),
(5,'2','教材2',1,22),
(6,'3','工具1',1,15),
(7,'3','工具2',1,15);期望结果+----+------+------+------+
| id | biz_id | pronduct_name | status | levels
+----+------+------+------+
| 5 | 2 | 教材2 | 1 | 22
| 7 | 3 | 工具2 | 1 | 15
| 3 | 1 | 书籍3 | 1 | 10
+----+------+------+------+
现用的语句
SELECT * FROM products WHERE id IN(SELECT MAX(id) FROM products GROUP BY biz_id) ORDER BY levels DESC; 解释:biz_id为商家id,一个商家有几个产品,但只能显示一个最新的。比如像商家1里有1、2、3 共三个产品,就只要显示id为3的产品。
在biz_id、ID上建立复合索引
explain SELECT * FROM tbl WHERE id IN(SELECT MAX(id) FROM tbl GROUP BY bizid);
explain ...的结果以供大家分析。
SELECT * FROM products a WHERE NOT EXISTS(SELECT 1 FROM products WHERE a.`biz_id`=`biz_id` AND a.`id`<id)
执行逻辑是什么,看不大懂……