mysql 查询如下:
SELECT
b.memberId,
b.memberName,
aa.orderid,
aa.productid,
aa.productname,
aa.other,
aa.num,
c.paytime
FROM
(SELECT
a.orderid,
a.productid,
a.productname,
a.other,
SUM(a.number) num
FROM
pengcz_order.order_details_b2b a
GROUP BY a.orderid,
a.productid,
a.productname,
a.other) aa,
pengcz_order.`order_current` b,
pengcz_order.`order_paymoney` c
WHERE aa.`orderId` = c.`orderId`
AND aa.orderid = b.orderid
AND aa.productid ='3952584' LIMIT 1,20;
转换成存储过程如下:
DELIMITER $$
USE `pengcz_order`$$
DROP PROCEDURE IF EXISTS `pub_shopProductRecordByProductId`$$
CREATE
DEFINER = `root`@`%`
PROCEDURE `pengcz_order`.`pub_shopProductRecordByProductId`(IN productId VARCHAR(50),IN page INT,IN pagesize INT)
COMMENT '根据产品id获取成交记录'
BEGIN
/**
* 创建临时表
* 用于存放订单交易快照明细表查出来的集合
*/
DROP TABLE IF EXISTS `detail`;
CREATE TEMPORARY TABLE `detail` (
`orderId` VARCHAR(45) NOT NULL,
`productId` VARCHAR(36) NOT NULL,
`productName` VARCHAR(200) NULL,
`other` VARCHAR(200) NULL,
`num` INT(11) NOT NULL DEFAULT '0'
);
SET @SQL=CONCAT("insert into `detail` select `orderId`,`productId`,`productName`,`other`,SUM(number) from `pengcz_order`.`order_details_b2b` group by `orderId`,`productId`,`productName`,`other`");
PREPARE m FROM @SQL;
EXECUTE m;
DEALLOCATE PREPARE m;
/**
* 使用临时表做查找
*/
SET @SQL=CONCAT("select b.memberId,b.memberName,a.orderid,a.productid,a.productname,a.other,a.num,c.paytime from `detail` a,pengcz_order.`order_current` b,pengcz_order.`order_paymoney` c where a.`orderId` = c.`orderId` AND a.orderid = b.orderid AND a.productid ='",productId,"' limit ",page,",",pagesize);
PREPARE m FROM @SQL;
EXECUTE m;
DEALLOCATE PREPARE m;
/**
* 清理临时表
*/
DROP TABLE `detail`;
END$$
DELIMITER ;
CALL pub_shopProductRecordByProductId('3952584',1,20);