create function concat_name
( id varchar(10),
tablename varchar(20))
returns varchar(50)
begin
declare var varchar(10);
declare varsum varchar(10);
declare found boolean default true;
declare continue handler for not found
set found=false;
prepare s1 from ' declare cur cursor for select id from ? ';
execute s1 using tablename;
deallocate prepare s1;
open cur;
fetch cur into var;
while found do
set varsum=concat(var,',');
fetch cur into var;
end while;
close cur;
return reverse(substring(reverse(varsum), 2) );
end
我写了一个脚本,主要是用来把查找出的列值连接成一个字符串,只想到了这种方法,但是对于动态sql,表名作为游标参数总报错,想了多天未果,盼高手快速回复!!!!
( id varchar(10),
tablename varchar(20))
returns varchar(50)
begin
declare var varchar(10);
declare varsum varchar(10);
declare found boolean default true;
declare continue handler for not found
set found=false;
prepare s1 from ' declare cur cursor for select id from ? ';
execute s1 using tablename;
deallocate prepare s1;
open cur;
fetch cur into var;
while found do
set varsum=concat(var,',');
fetch cur into var;
end while;
close cur;
return reverse(substring(reverse(varsum), 2) );
end
我写了一个脚本,主要是用来把查找出的列值连接成一个字符串,只想到了这种方法,但是对于动态sql,表名作为游标参数总报错,想了多天未果,盼高手快速回复!!!!
SET @cmd = CONCAT('SELECT `id` FROM `',tbname,'`');PREPARE stmt FROM @cmd;
execute s1;
mysql> DROP PROCEDURE IF EXISTS p_concat_name $$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE p_concat_name(colname VARCHAR(64),tbname VARCHAR(64))
-> BEGIN
-> SET @cmd = CONCAT('SELECT GROUP_CONCAT(`',colname,'`) INTO @data FROM `',tbname,'`');
->
-> PREPARE stmt FROM @cmd;
->
-> EXECUTE stmt;
->
-> DEALLOCATE PREPARE stmt;
->
-> SELECT @data;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL p_concat_name('data','test');
+-----------------------------------------+
| @data |
+-----------------------------------------+
| liang,jia,hui,aaa,bbb,ccc,ddd,dddd,eeee |
+-----------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
prepare / execute 来生成SQL语句然后执行。按四楼的做法就行了。
set @sql=concat('select group_concat(id,',') into @varsum from ', tablename);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
set var=reverse(substring(reverse(@varsum), 2) );
end
我写的哪里有错?
报的错误是这里 set @sql=concat('select group_concat(id,',') into @varsum from ', tablename);
SET @cmd = CONCAT('SELECT GROUP_CONCAT(`',colname,'`) INTO @data FROM `',tbname,'`');我怎么看不懂,中间怎么加的逗号,结尾的怎么没有??
十分感谢您的回信,盼你答复。
select date(atime) as 时间,isnull( count(*),0) as 总量
from sum1
where atime>'2007-11-01 00:00:00' and atime< '2007-12-01 00:00:00'
group by date(atime)
怎么报isnull有错????
( in id varchar(10),
in tablename varchar(20),
out var varchar(50))
begin
declare varsum varchar(50);
set @sql=concat('select group_concat(id,',') into varsum from ',tablename);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
set var=reverse(substring(reverse(varsum), 2) );
end
十分感谢,在线等。。
GROUP_CONCAT()函数默认的分隔符是,不设置分隔符
SET @cmd = CONCAT('SELECT GROUP_CONCAT(`',colname,'` SEPARATOR '''') INTO @data FROM `',tbname,'`');将分隔符设置为分号;
SET @cmd = CONCAT('SELECT GROUP_CONCAT(`',colname,'` SEPARATOR '';'') INTO @data FROM `',tbname,'`');
select date(atime) as 时间,isnull( count(*),0) as 总量
from sum1
where atime>'2007-11-01 00:00:00' and atime < '2007-12-01 00:00:00'
group by date(atime)
怎么报isnull有错????
怎么让count返回值问0 ????
in id varchar(10),
in tablename varchar(20),
out var varchar(50))
begin
-- declare varsum varchar(50);
set @sql=concat('select group_concat(',id,') into @varsum from ',tablename);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
set var=reverse(substring(reverse(@varsum), 2) );
end
//delimiter ;
-> (select table3.时间,总量,长途,短途 from
-> (
-> select table1.时间,总量,长途
-> from
-> (select date(atime) as 时间,ifnull( count(*),0) as 总量
-> from sum1
-> where atime>'2007-11-01 00:00:00' and atime< '2007-12-01 00:
-> group by date(atime)) as table1 left join
-> ( select date(atime) as 时间,ifnull(count(*),0) as 长途
-> from sum1
-> where atime between '2007-11-01 00:00:00' and '2007-12-01
-> and tele like '%长途%'
-> group by date(atime)) as table2
-> on table1.时间=table2.时间
-> )
-> as table3 left join
-> ( select date(atime) as 时间,ifnull(count(*),0) as 短途
-> from sum1
-> where atime between '2007-11-01 00:00:00' and '2007-12-01
-> and tele not like '%长途%'
-> group by date(atime)) as table4
-> on table3.时间=table4.时间 )as table5 left join
-> ( select table8.时间,拨入,拨出 from
-> ( select table6.时间,拨入
-> from
-> (select date(atime) as 时间
-> from sum1
-> where atime>'2007-11-01 00:00:00' and atime< '2007-12-01 00:
-> group by date(atime)) as table6 left join
-> ( select date(atime) as 时间,ifnull(count(*),0) as 拨入
-> from sum1
-> where atime between '2007-11-01 00:00:00' and '2007-12-01
-> and xingshi='拨入'
-> group by date(atime)) as table7
-> on table6.时间=table7.时间
-> )
-> as table8 left join
-> ( select date(atime) as 时间,ifnull(count(*),0) as 拨出
-> from sum1
-> where atime between '2007-11-01 00:00:00' and '2007-12-01
-> and xingshi='拨出'
-> group by date(atime)) as table10
-> on table8.时间=table10.时间 )as table9
-> on table5.时间=table9.时间
-> ;
+------------+------+------+------+------+------+
| 时间 | 总量 | 长途 | 短途 | 拨入 | 拨出 |
+------------+------+------+------+------+------+
| 2007-11-01 | 3 | NULL | 3 | 3 | NULL |
| 2007-11-02 | 2 | 2 | NULL | NULL | 2 |
| 2007-11-03 | 1 | NULL | 1 | 1 | NULL |
+------------+------+------+------+------+------+
3 rows in set (0.00 sec)
我改成ifnull,怎么还没有变成0,刚才是看别人的帖子改成的isnull,能否对count的返回值做个解释。我也感觉到我的这种做法是不是太过于繁琐了。
十分感谢
还有怎么是count(*),返回成0而不是null,见我上问。我该后还是没结果。
你在最外层的SELECT里判断不就行了吗?
IFNULL(长途,0) AS 长途,
IFNULL(短途,0) AS 短途,
IFNULL(拨入,0) AS 拨入,
IFNULL(拨出,0) AS 拨出 from
另外为什么你喜欢加上反引号?反引号起什么作用??show create procedure 有部分被加上了反引号。
还有怎么使count(*),返回成0而不是null???
加上反引号就是可以保证你的列就算有关键字什么的.也不会出错..
保险一些,还是加上比较好..当然在设计的时候应该避些这样的名字出现..不是COUNT(*)返回NULL了..
是因为你外面又用了LEFT JOIN .没有找到时,又将该列设置为NULL了..
怎么做..我在19楼已经说了..希望你看看再问.好不好?