现在有一个报表,数据量太多了.我想用一个存储过程来处理部分返回数据..
现在的SQL如下:SELECT Sum(ul.money) as total FROM userlog AS ul left Join tz AS tz ON ul.tzid = tz.id WHERE
ul.uid in(4213,4215,4216,4218,4219,4224,4226,4232,4235,4248,4251,4255,4262,4268,4308,4354)
AND tz.zt < 4 and ( ul.logtype ='tzfd' or ul.logtype = 'zhfd' or ul.logtype = 'dlfd' ) and
( ul.logtime >='1369074600' and ul.logtime <= '1369161000' )
参数uid和logtime是传入的参数.要求返回相关的数据.
现在的SQL如下:SELECT Sum(ul.money) as total FROM userlog AS ul left Join tz AS tz ON ul.tzid = tz.id WHERE
ul.uid in(4213,4215,4216,4218,4219,4224,4226,4232,4235,4248,4251,4255,4262,4268,4308,4354)
AND tz.zt < 4 and ( ul.logtype ='tzfd' or ul.logtype = 'zhfd' or ul.logtype = 'dlfd' ) and
( ul.logtime >='1369074600' and ul.logtime <= '1369161000' )
参数uid和logtime是传入的参数.要求返回相关的数据.
DROP PROCEDURE test$$
CREATE PROCEDURE test(auid VARCHAR(100),alogtime1 DATETIME,alogtime2 DATETIME,OUT aa INT)
BEGIN
SELECT SUM(ul.money) AS total FROM userlog AS ul LEFT JOIN tz AS tz ON ul.tzid = tz.id WHERE
FIND_IN_SET(ul.uid,auid)
AND tz.zt < 4 AND ( ul.logtype ='tzfd' OR ul.logtype = 'zhfd' OR ul.logtype = 'dlfd' ) AND
( ul.logtime >=alogtime1 AND ul.logtime <= alogtime2 );
END$$
DELIMITER ;call test('4213,4215,4216,4218,4219,4224,4226,4232,4235,4248,4251,4255,4262,4268,4308,4354','1369074600','1369161000',@a);
select @a
把SUM(ul.money)的值插入到表中保存,再 取值
好象不行啊..这个UID实际比这个要多多了,还有这个传入的时候类型,DATETIME好象不对吧,是不是应该为timestamp或者为int类型的?
我用你这个创建过后,执行返回为null
还有这个传入的时候类型,DATETIME好象不对吧,是不是应该为timestamp或者为int类型的?
用UNIX_TIMESTAMP函数转换一下
能不能写一个我直接使用PHP调用传入参数的就能返回信息的...
另外返回的数据是带小数点的...
谢谢了.
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
CREATE TABLE `userlog` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`idchar` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '随机编号' ,
`fid` int(11) NULL DEFAULT NULL COMMENT '0' ,
`uid` int(11) NULL DEFAULT NULL ,
`tzid` int(11) NULL DEFAULT NULL ,
`czlx` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`logtype` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` double(20,6) UNSIGNED NULL DEFAULT 0.000000 ,
`oldmoney` double(20,6) UNSIGNED NOT NULL ,
`logtime` int(11) NULL DEFAULT NULL ,
`logip` char(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `uid` (`uid`) USING BTREE ,
INDEX `tzid` (`tzid`) USING BTREE ,
INDEX `czlx` (`czlx`) USING BTREE ,
INDEX `logtype` (`logtype`) USING BTREE ,
INDEX `money` (`money`) USING BTREE ,
INDEX `logtype_2` (`logtype`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='tz=tz'
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;我要返回根据传入的UID和时间,返回money的总和.我这样写应该可以明白了点吧
建议你看看MYSQL的HELP,动手做一下吧,有问题再问logtime都是int类型的,你的SQL语句logtime >='1369074600'?DELIMITER $$
DROP PROCEDURE test$$
CREATE PROCEDURE test(auid VARCHAR(100),alogtime1 INT,alogtime2 INT ,OUT aa DECIMAL)
BEGIN
SELECT SUM(ul.money) AS total FROM userlog AS ul LEFT JOIN tz AS tz ON ul.tzid = tz.id WHERE
FIND_IN_SET(ul.uid,auid)
AND tz.zt < 4 AND ( ul.logtype ='tzfd' OR ul.logtype = 'zhfd' OR ul.logtype = 'dlfd' ) AND
( ul.logtime >=alogtime1 AND ul.logtime <= alogtime2 );
END$$
DELIMITER ;
为什么不直接用SQL语句呢?在PHP代码中根据已知的信息,直接生成顶楼的字符串,然后提交到MYSQL执行不就行了?用存储过程对这个需求没有什么性能上或者程序维护上的帮助啊。