显示 Rownum 问题 MySQL [英] Display Rownum issue MySQL
本文介绍了显示 Rownum 问题 MySQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个查询:
SET @row_num = 0;
SELECT
(SELECT @row_num := @row_num + 1) AS itempurchase_code,
(SELECT supplier_code FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY supplier_code ORDER BY COUNT(*) DESC LIMIT 1) AS supplier_code,
(SELECT user_code FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY user_code ORDER BY COUNT(*) DESC LIMIT 1) AS user_code,
22 AS status_code,
item_invoicecodesupplier AS item_invoicecodesupplier,
(SELECT itempurchase_date FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY itempurchase_date ORDER BY COUNT(*) DESC LIMIT 1) AS itempurchase_date
FROM qa_items_purchases b
GROUP BY (item_invoicecodesupplier)
ORDER BY itempurchase_code;
我得到这个结果:
如果你看没有 (2
) itempurchase_code
列,我能做些什么来按顺序显示数字?
If you look there is no (2
) itempurchase_code
column, What i can do to show the numbers in sequence?
推荐答案
如果您的查询包含 GROUP BY
,您必须在外部查询中添加行号.
You must add the row number in an outer query if your query contains a GROUP BY
.
SET @row_num = 0;
SELECT (SELECT @row_num := @row_num + 1) AS itempurchase_code, *
FROM
(
SELECT ... -- your original query goes here
) AS T1
ORDER BY itempurchase_code
这篇关于显示 Rownum 问题 MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文