mysql为select结果集添加序号列
两个函数简单搞定
DELIMITER $$
USE `oso_isp`$$
DROP FUNCTION IF EXISTS `rownum_reset`$$
CREATE FUNCTION `rownum_reset`() RETURNS INT(11)
BEGIN
SET @rno = 0;
RETURN 1;
END$$
DELIMITER ;
DELIMITER $$
USE `oso_isp`$$
mysql中selectDROP FUNCTION IF EXISTS `rownum`$$
CREATE  FUNCTION `rownum`() RETURNS INT(11)
BEGIN
/* ⽤法如：
SELECT
`rownum`(
)      AS `id`,
t.*
FROM TABLE t
WHERE rownum_reset() = 1
*/
SET @rno = @rno + 1;
RETURN @rno;
END$$
DELIMITER ;
查询结果测试
测试代码：
SELECT
`rownum`(
)  AS `row_num`
,
`a`.`stock_in_date`  AS `stock_in_date`
,`a`.`dinner_type`    AS `dinner_type`
,`a`.`stockroom_id`  AS `stockroom_id`
,SUM(`a`.`dinner_num`)
AS `dinner_num`
,SUM((SELECT SUM(`wh_stock_in_order_item`.`total_price`) FROM `wh_stock_in_order_item` WHERE (`wh_stock_in_order_item`.`stock_in_order_id` = `a`.`id`)))    AS `total_price`
,COUNT(0)
AS `order_num`
,GROUP_CONCAT(DISTINCT (SELECT `staff`.`name` FROM `staff` WHERE (`staff`.`id` = `a`.`buyer_id`)) SEPARATOR ',') -- ⾏变列
AS `buyer_name`
FROM `wh_stock_in_order` `a`
WHERE ((`a`.`is_deleted` = 0)
AND (`a`.`status` = 1)
AND (`rownum_reset`() = 1)) -- 初始化序号列从1开始
GROUP BY `a`.`stock_in_date`,`a`.`dinner_type`,`a`.`stockroom_id`
ORDER BY `a`.`stock_in_date`,`a`.`dinner_type`
返回结果：