mysql 取出5条不同类目下最新的商品

Question

1.select c.* from (select * from `item` where `staus`=0 ORDER BY `dateline` DESC) AS c GROUP BY c.`cid` 这句子查询的效率太低了，数据库有100万数据。

2.SELECT `num_iid`,`title`,`promotion_price`,`price`,`pic_url`,`dateline` FROM `item` WHERE `staus`=0 group by `cid` ORDER BY `dateline` DESC LIMIT 4 这一句order by因为group by的原因会时效。

cid是类目ID

Answer 1

看看你的问题， 不是很明白。
要不我举个例子， 确认一下你的需求。

例如你的 item 表有下面这些数据。
分类代码cid 商品代码num_iid 时间dateline
1 1 2013-05-01
1 2 2013-05-02
1 3 2013-05-03
1 4 2013-05-04
1 5 2013-05-05
1 6 2013-05-06
2 7 2013-05-07
2 8 2013-05-08
2 9 2013-05-09
2 10 2013-05-10
2 11 2013-05-11
2 12 2013-05-12
......

很多个分类， 很多个商品， 很多个时间
......

你预期的结果是

1 2 2013-05-02
1 3 2013-05-03
1 4 2013-05-04
1 5 2013-05-05
1 6 2013-05-06

2 8 2013-05-08
2 9 2013-05-09
2 10 2013-05-10
2 11 2013-05-11
2 12 2013-05-12

每个分类都要显示
但是只显示当前分类下的 最新的 5个商品

你是不是要这样的效果呀？追问

不是，只是最新的5个商品，每个商品的cid不一样

追答

我这里不方便创建很多测试数据。 

就简单模拟模拟吧

CREATE TABLE test100 (
  cid  INT,
  num_iid  INT,
  dateline  DATE
);
INSERT INTO test100
SELECT 1,  1,  '2013-05-01'  UNION  ALL
SELECT 1,  2,  '2013-05-02'  UNION  ALL
SELECT 2,  3,  '2013-05-03'  UNION  ALL
SELECT 2,  4,  '2013-05-04'  UNION  ALL
SELECT 3,  5,  '2013-05-05'  UNION  ALL
SELECT 3,  6,  '2013-05-06'  UNION  ALL
SELECT 4,  7,  '2013-05-07'  UNION  ALL
SELECT 4,  8,  '2013-05-08'  UNION  ALL
SELECT 5,  9,  '2013-05-09'  UNION  ALL
SELECT 5,  10, '2013-05-10'  UNION  ALL
SELECT 6,  11, '2013-05-11'  UNION  ALL
SELECT 6,  12, '2013-05-12';

SELECT
  *
FROM
  test100  main
WHERE
  NOT EXISTS( SELECT 1 FROM  test100  sub  WHERE  main.cid = sub.cid AND  main.dateline < sub.dateline)
ORDER BY
  dateline DESC
LIMIT 5;

Answer 2

比如：
分类表为 categories ，商品表为 products ，categories表中的 categories_id 与 products 的 categories_id 关联。
products_date_added 字段为 products 表中的商品添加时间字段

SQL语句如下：

SELECT temp_table . *
FROM (

SELECT p.*
FROM `products` p
ORDER BY p.products_date_added DESC

) temp_table
GROUP BY temp_table.categories_id
ORDER BY temp_table.products_date_added DESC
LIMIT 0 , 5

上面的SQL语句解释如下：
temp_table 临时表的名字
先把所有的商品按添加时间倒排序，再以分类表中的categories_id分组，然后再把得到的结果按添加时间倒排序，最后取前5条数据。

Answer 3

分开写呗，才5个类目。先把类目查出来，5行而已，然后再执行5条独立的sql，建议status和dateline都有索引。

如果你担心多条查询有连接数据库的损失的话可以在mysql那边写一个函数就行了

Answer 4

第一条，为什么要用这种语句呢？浪费资源。

看一下这样的语句是否达到预定要求

select `cid` from `item` where `status`=0 group by `cid`

第二条的话，建议直接取第一条里的cid，再循环取一下，这样更高效。

追问

尼玛，难道真的只能这样方法。。。。第一种我肯定不会用，你的这句sql，没有做order by排序，取出的不是最新的肯定不行的。

追答

因为你用了子查询，原来表上设置的索引都没用了。性能明显差很多。

你的类别多于5个吗？
每个类别分别取5条数据，然后用order，这样数据量应该小很多吧？

追问

类目有12000个，所以我首先要order by取出5个cid，再去执行5次才行。

追答

那我们分析一下，如果我们取100条按odery排序的内容，有多少概率cid超过5？

如果不能够保证100%满足条件，我们把100放大到100%满足的条件的值，然后用这个临时表加索引group？

我想到一个解决方案：

select `cid`,`dateline`=max(`dateline`) from `item` where `status`=0 group by `cid` order by `dateline` desc

这样是不是能够满足了呢？

追问

这个方式其实也不好，感觉还是回到了原点，这样执行效率其实还真不如单条单条的去执行。

貌似我在本机测试 执行时间达到了0.4s。。。

追答

看一下索引是怎么建的，我觉得这样的语句比你的第一条应该效率高很多吧？

对于这么多的数据量，0.4s我个人觉得还是能够接受的，如果你在服务器上执行的话呢？速度能达多少？

追问

select DISTINCT(`cid`) from `item` where `staus`=0 order by `dateline` DESC LIMIT 0,5

那干嘛不这样？第一条我只是举例说能满足条件，我已经说明了效率不行，肯定不会采用的。

追答

那应该也可以吧？具体的肯定要你权衡一下，有好的方法分享一下，共同进步嘛。

Answer 5

多表查询好一点。。where条件可以