-- 表 tB
CREATE TABLE `tB` (
`id` int(11) NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL,
`dt1` datetime NOT NULL,
`dt2` datetime NOT NULL,
`prices` decimal(10,0) NOT NULL,
`dt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 表 tA
CREATE TABLE `tA` (
`id` int(11) NOT NULL auto_increment,
`uid` int(10) unsigned default NULL,
`sname` varchar(50) default NULL,
`dtlast` datetime default NULL,
`dtnow` datetime default NULL,
`mprice` decimal(10,0) NOT NULL,
`dt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;参数uid int,sid int内容
BEGIN
DECLARE dt1,dt2 datetime;
DECLARE total,cardmoney,barmoney,openmoney,backmoney decimal ;
DECLARE timetotal,cardtotal,bartotal,opentotal,backtotal int ;
if sid = 0 then
set dt2 = now();
SELECT `dt1` into dt1 FROM `tB` WHERE `uid`=uid ORDER BY `dt` DESC LIMIT 1;
if dt1 is null then
set dt1='1970-1-1 08:00:00';
end if;
else
/*(省略...)*/
end if /*调试*/
SELECT dt1;
SELECT dt2;
SELECT uid;
SELECT SUM(`prices`) into total from `tA` where (`dt` between dt1 and dt2) and (`uid`=uid);
SELECT total;
/*调试 end*/ /*(省略...)*/
END
基本上与原表结构相差不大,就是做一个统计金额的。初次使用MySQL的存储过程,使用中发现如果是一个条件的话,是可以匹配的,很正常。但是一旦有个AND 的多条件时,它只取其中的一个条件,就是说,我上面这条SELECT SUM(`prices`) into total from `tA` where (`dt` between dt1 and dt2) and (`uid`=uid);
SELECT total;
它只认到(`dt` between dt1 and dt2),而后面的`uid`字段,我再怎么使劲变换它出来的结果都是一样的。根本没有用处,我就郁闷了,难道……MySQL的存储过程只支持一个条件。于是我又写了个简单,取某一表的某一字段,结果,我一个条件的时候,一下就取出来了。但是多一个条件的时候,取出来的不正确的。郁闷极了,于是我用了个土办法解决了:参数:uid int,conf VARCHAR(50) SET @SQL=concat('SELECT `F3` FROM tC WHERE `uid`=''',uid,''' and `F2`=''',conf,'''');
prepare executesqlstring from @SQL;
execute executesqlstring;
但是用在上面的话,也太让人郁闷了,上面的语句太多了,起码也有七八条吧。
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
`id` int(11) NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL,
`dt1` datetime NOT NULL,
`dt2` datetime NOT NULL,
`prices` decimal(10,0) NOT NULL,
`dt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tA` (
`id` int(11) NOT NULL auto_increment,
`uid` int(10) unsigned default NULL,
`sname` varchar(50) default NULL,
`dtlast` datetime default NULL,
`dtnow` datetime default NULL,
`mprice` decimal(10,0) NOT NULL,
`dt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('3', '304', '2011-07-25 00:00:00', '2011-08-25 00:00:00', '30', '2010-11-26 12:01:04');
INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('3', '305', '2011-08-25 00:00:00', '2011-09-25 00:00:00', '30', '2010-11-27 17:10:05');
INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('3', '606', '2010-11-28 00:00:00', '2010-12-28 00:00:00', '30', '2010-11-28 15:09:41');
INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('3', '509', '2010-11-28 00:00:00', '2010-12-28 00:00:00', '30', '2010-11-28 15:14:08');
INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('3', '404', '2010-11-28 00:00:00', '2010-12-28 00:00:00', '30', '2010-11-28 15:16:28');
INSERT INTO `tA` (`uid`,`sname`,`dtlast`,`dtnow`,`mprice`,`dt`) VALUES ('1', 'Z21', '2010-11-28 00:00:00', '2010-12-28 00:00:00', '30', '2010-11-28 15:16:28');
INSERT INTO `tB` (`uid`,`dt1`,`dt2`,`prices`,`dt`) VALUES ('3', '2010-12-01 18:58:22', '1970-01-01 08:00:00', '210', '2010-12-01 18:58:22');-- 存储过程
DELIMITER //
CREATE PROCEDURE test_check(uid int,sid int)
BEGIN
DECLARE dt1,dt2 datetime;
DECLARE total,cardmoney,barmoney,openmoney,backmoney decimal ;
DECLARE timetotal,cardtotal,bartotal,opentotal,backtotal int ;
if sid = 0 then
set dt2 = now();
SELECT `dt1` into dt1 FROM `tB` WHERE `uid`=uid ORDER BY `dt` DESC LIMIT 1;
if dt1 is null then
set dt1='1970-1-1 08:00:00';
end if; end if;SELECT dt1;
SELECT dt2;
SELECT uid;
SELECT SUM(`mprice`) into total from `tA` where (`dt` between dt1 and dt2) and (`uid`=uid);
SELECT total;END//
DELIMITER;-- 测试结果
call test_check(1,0);
call test_check(2,0);
call test_check(3,0);
mysql> call test_check(1,0);
+---------------------+
| dt1 |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set+---------------------+
| dt2 |
+---------------------+
| 2010-12-04 11:16:01 |
+---------------------+
1 row in set+-----+
| uid |
+-----+
| 1 |
+-----+
1 row in set+-------+
| total |
+-------+
| 180 |
+-------+
1 row in setQuery OK, 0 rows affectedmysql> call test_check(2,0);
+---------------------+
| dt1 |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set+---------------------+
| dt2 |
+---------------------+
| 2010-12-04 11:16:01 |
+---------------------+
1 row in set+-----+
| uid |
+-----+
| 2 |
+-----+
1 row in set+-------+
| total |
+-------+
| 180 |
+-------+
1 row in setQuery OK, 0 rows affectedmysql> call test_check(3,0);
+---------------------+
| dt1 |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set+---------------------+
| dt2 |
+---------------------+
| 2010-12-04 11:16:01 |
+---------------------+
1 row in set+-----+
| uid |
+-----+
| 1 |
+-----+
1 row in set+-------+
| total |
+-------+
| 180 |
+-------+
1 row in setQuery OK, 0 rows affected
以上结果明显不对,如UID为1时,结果total应为30,而UID为3时,total结果应为150