DELIMITER //
CREATE PROCEDURE `test21`(IN date1 DATETIME,IN date2 DATETIME)
BEGIN SET @mysql = ' SELECT requestsource,COUNT(*),COUNT(*)/(SELECT COUNT(requestsource) FROM visitor) FROM visitor V WHERE 1 = 1 ';
IF date1 <> NULL & date2 = NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' GROUP BY requestsource');
ELSEIF date1 <> NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' AND outdate <',date2,' GROUP BY requestsource');
ELSEIF date1 = NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND outdate <',date2,' GROUP BY requestsource');
ELSE
SET @s=@mysql;
END IF;
PREPARE stmt FROM @s;
EXECUTE stmt;END;//可以创建,但已运行就报下面的错误错误码: 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
CREATE PROCEDURE `test21`(IN date1 DATETIME,IN date2 DATETIME)
BEGIN SET @mysql = ' SELECT requestsource,COUNT(*),COUNT(*)/(SELECT COUNT(requestsource) FROM visitor) FROM visitor V WHERE 1 = 1 ';
IF date1 <> NULL & date2 = NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' GROUP BY requestsource');
ELSEIF date1 <> NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' AND outdate <',date2,' GROUP BY requestsource');
ELSEIF date1 = NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND outdate <',date2,' GROUP BY requestsource');
ELSE
SET @s=@mysql;
END IF;
PREPARE stmt FROM @s;
EXECUTE stmt;END;//可以创建,但已运行就报下面的错误错误码: 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
CREATE PROCEDURE `test21`(IN date1 DATETIME,IN date2 DATETIME)
BEGIN SET @mysql = ' SELECT requestsource,COUNT(*),COUNT(*)/(SELECT COUNT(requestsource) FROM visitor) FROM visitor V WHERE 1 = 1 ';
IF date1 <> NULL & date2 = NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' GROUP BY requestsource');
ELSEIF date1 <> NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND visitdate >',date1,' AND outdate <',date2,' GROUP BY requestsource');
ELSEIF date1 = NULL & date2 <> NULL THEN
SET @s = CONCAT(@mysql,' AND outdate <',date2,' GROUP BY requestsource');
ELSE
SET @s=@mysql;
END IF;select @s;PREPARE stmt FROM @s;
EXECUTE stmt;END;//加上这一句看一下你实现的@s 到底是什么,应该是语法错误。少了什么东西。
(1 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:015
Total Time : 00:00:00:015错误码: 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clauseExecution Time : 00:00:00:000
Transfer Time : 00:00:00:015
Total Time : 00:00:00:015
CREATE PROCEDURE test_time(IN date1 DATETIME,IN date2 DATETIME)
begin
set @sqlstr = 'SELECT requestsource,COUNT(*),COUNT(*)/(SELECT COUNT(requestsource) FROM visitor) FROM visitor V WHERE 1 = 1';if date1 is not null then
set @sqlstr=concat(@sqlstr,' and visitdate > date''',date1,'''');
end if;
if date2 is not null then
set @sqlstr=concat(@sqlstr,' and outdate < date''',date2,'''');
end if;set @sqlstr = concat(@sqlstr,' group by requestsource');select @sqlstr;
prepare stmt from @sqlstr;
execute stmt;
end;
/
我测试的:mysql> drop procedure if exists test_time /
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE test_time(IN date1 DATETIME,IN date2 DATETIME)
-> begin
-> set @sqlstr = 'SELECT gb,COUNT(*),COUNT(*)/(SELECT COUNT(gb) FROM testtime) FROM testtime V WHERE 1 = 1';
->
-> if date1 is not null then
-> set @sqlstr=concat(@sqlstr,' and d1 > ''',date1,'''');
-> end if;
-> if date2 is not null then
-> set @sqlstr=concat(@sqlstr,' and d2 < ''',date2,'''');
-> end if;
->
-> set @sqlstr = concat(@sqlstr,' group by gb');
->
-> select @sqlstr;
-> prepare stmt from @sqlstr;
-> execute stmt;
-> end;
-> /
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql>
mysql> call test_time(now()-interval 2 day,now());
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sqlstr |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT gb,COUNT(*),COUNT(*)/(SELECT COUNT(gb) FROM testtime) FROM testtime V WHERE 1 = 1 and d1 > '2010-03-09 13:52:53' and d2 < '2010-03-11 13:52:53' group by gb |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)+------+----------+-------------------------------------------+
| gb | COUNT(*) | COUNT(*)/(SELECT COUNT(gb) FROM testtime) |
+------+----------+-------------------------------------------+
| 1 | 1 | 0.1000 |
| 2 | 1 | 0.1000 |
+------+----------+-------------------------------------------+
2 rows in set (0.03 sec)Query OK, 0 rows affected (0.06 sec)mysql>
mysql> call test_time(null,null);
+------------------------------------------------------------------------------------------------------+
| @sqlstr |
+------------------------------------------------------------------------------------------------------+
| SELECT gb,COUNT(*),COUNT(*)/(SELECT COUNT(gb) FROM testtime) FROM testtime V WHERE 1 = 1 group by gb |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)+------+----------+-------------------------------------------+
| gb | COUNT(*) | COUNT(*)/(SELECT COUNT(gb) FROM testtime) |
+------+----------+-------------------------------------------+
| 1 | 4 | 0.4000 |
| 2 | 3 | 0.3000 |
| 3 | 3 | 0.3000 |
+------+----------+-------------------------------------------+
3 rows in set (0.03 sec)Query OK, 0 rows affected (0.06 sec)mysql>
mysql> call test_time(null,now());
+-------------------------------------------------------------------------------------------------------------------------------------+
| @sqlstr |
+-------------------------------------------------------------------------------------------------------------------------------------+
| SELECT gb,COUNT(*),COUNT(*)/(SELECT COUNT(gb) FROM testtime) FROM testtime V WHERE 1 = 1 and d2 < '2010-03-11 13:52:53' group by gb |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)+------+----------+-------------------------------------------+
| gb | COUNT(*) | COUNT(*)/(SELECT COUNT(gb) FROM testtime) |
+------+----------+-------------------------------------------+
| 1 | 4 | 0.4000 |
| 2 | 3 | 0.3000 |
| 3 | 3 | 0.3000 |
+------+----------+-------------------------------------------+
3 rows in set (0.03 sec)Query OK, 0 rows affected (0.06 sec)mysql>
mysql> call test_time(now() - interval 2 day,null);
+-------------------------------------------------------------------------------------------------------------------------------------+
| @sqlstr |
+-------------------------------------------------------------------------------------------------------------------------------------+
| SELECT gb,COUNT(*),COUNT(*)/(SELECT COUNT(gb) FROM testtime) FROM testtime V WHERE 1 = 1 and d1 > '2010-03-09 13:52:54' group by gb |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)+------+----------+-------------------------------------------+
| gb | COUNT(*) | COUNT(*)/(SELECT COUNT(gb) FROM testtime) |
+------+----------+-------------------------------------------+
| 1 | 1 | 0.1000 |
| 2 | 1 | 0.1000 |
+------+----------+-------------------------------------------+
2 rows in set (0.03 sec)Query OK, 0 rows affected (0.08 sec)mysql> select * from testtime;
+----+---------------------+---------------------+------+
| id | d1 | d2 | gb |
+----+---------------------+---------------------+------+
| 1 | 2010-03-10 13:31:23 | 2010-03-11 13:31:23 | 1 |
| 2 | 2010-03-09 13:31:45 | 2010-03-11 13:31:45 | 1 |
| 3 | 2010-03-08 13:31:53 | 2010-03-11 13:31:53 | 1 |
| 4 | 2010-03-07 13:31:59 | 2010-03-11 13:31:59 | 1 |
| 5 | 2010-03-10 13:39:54 | 2010-03-11 13:39:54 | 2 |
| 6 | 2010-03-09 13:40:01 | 2010-03-11 13:40:01 | 2 |
| 7 | 2010-03-08 13:40:08 | 2010-03-11 13:40:08 | 2 |
| 8 | 2010-03-08 13:40:15 | 2010-03-11 13:40:15 | 3 |
| 9 | 2010-03-08 13:40:21 | 2010-03-11 13:40:21 | 3 |
| 10 | 2010-03-08 13:40:29 | 2010-03-11 13:40:29 | 3 |
+----+---------------------+---------------------+------+
10 rows in set (0.00 sec)