展开全部
我只模拟一个 输出 3条商品信息的效果了.
否则 文字又要超长了CREATE TABLE sp_classify (
cid INT,
cname VARCHAR(10)
);
INSERT INTO sp_classify VALUES(1, '笔记本32313133353236313431303231363533e58685e5aeb931333332623964');
INSERT INTO sp_classify VALUES(2, '台式机');
INSERT INTO sp_classify VALUES(3, '超级本');
INSERT INTO sp_classify VALUES(4, '平板');
CREATE TABLE sp_goods (
cid INT,
gtitle VARCHAR(50)
);
INSERT INTO sp_goods VALUES(1, '笔记本1');
INSERT INTO sp_goods VALUES(1, '笔记本2');
INSERT INTO sp_goods VALUES(1, '笔记本3');
INSERT INTO sp_goods VALUES(1, '笔记本4');
INSERT INTO sp_goods VALUES(1, '笔记本5');
INSERT INTO sp_goods VALUES(2, '台式机1');
INSERT INTO sp_goods VALUES(2, '台式机2');
INSERT INTO sp_goods VALUES(2, '台式机3');
INSERT INTO sp_goods VALUES(2, '台式机4');
INSERT INTO sp_goods VALUES(2, '台式机5');
INSERT INTO sp_goods VALUES(3, '超级本1');
INSERT INTO sp_goods VALUES(3, '超级本2');
INSERT INTO sp_goods VALUES(3, '超级本3');
INSERT INTO sp_goods VALUES(3, '超级本4');
INSERT INTO sp_goods VALUES(3, '超级本5');
INSERT INTO sp_goods VALUES(4, '平板1');
INSERT INTO sp_goods VALUES(4, '平板2');
INSERT INTO sp_goods VALUES(4, '平板3');
INSERT INTO sp_goods VALUES(4, '平板4');
INSERT INTO sp_goods VALUES(4, '平板5');
SELECT
resultName
FROM
(
SELECT 0 as seqNo, cid, cname as resultName FROM sp_classify
UNION ALL
SELECT CASE WHEN @cn != cid THEN @rownum:= 1
ELSE @rownum:= @rownum + 1 END as seqNo,
@cn := cid AS cid,
gtitle as resultName FROM sp_goods, (SELECT @rownum:=1) r, (SELECT @cn:=0) p
) subQuery
WHERE
SeqNo <=3
ORDER BY
cid, seqNo;
+------------+
| resultName |
+------------+
| 笔记本 |
| 笔记本1 |
| 笔记本2 |
| 笔记本3 |
| 台式机 |
| 台式机1 |
| 台式机2 |
| 台式机3 |
| 超级本 |
| 超级本1 |
| 超级本2 |
| 超级本3 |
| 平板 |
| 平板1 |
| 平板2 |
| 平板3 |
+------------+
16 rows in set (0.00 sec)
你需要 10行商品的, 就是简单把 sql 语句中的 WHERE SeqNo <=3
修改为 WHERE SeqNo <= 10 即可。