想做一个报表
想得到的效果是这样的:
__________________________________________________
|部门 | 职位 | 姓名 | 编号 | 项目A | 项目B | 项目C | ......
|IT部 | 程序员 | 张三 | 001 | 10 | 0 | 0 |.......
|IT部 | 程序员 | 李四 | 002 | 8 | 5 | 0 |.......
|IT部 | 程序员 | 张三 | 003 | 0 | 0 | 0 |.......
....
....
....
....请高手指教:DROP PROCEDURE IF EXISTS `test_myproc`CREATE PROCEDURE `test_myproc`()BEGIN
DECLARE _sqltext VARCHAR(5000);
DECLARE _deptName VARCHAR(100);
DECLARE _userPriv VARCHAR(100);
DECLARE _userName VARCHAR(100);
DECLARE _userId VARCHAR(1000;
DECLARE _done TINYINT(1) DEFAULT 0;
DECLARE _isnext TINYINT(1) DEFAULT 0;
#----全体员工的游标
DECLARE cur_user CURSOR FOR SELECT b.`DEPT_NAME`,a.`USER_PRIV_NAME`,a.`USER_NAME`,a.`USER_ID`
FROM USER a JOIN department b ON b.`DEPT_ID`=a.`DEPT_ID`
WHERE b.`DEPT_NO`=( SELECT dept_no FROM department WHERE `DEPT_ID`=a.`DEPT_ID`); DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;
OPEN cur_user;
loop_xxx:LOOP
FETCH cur_user INTO _deptName,_userPriv,_userName,_userId;
IF _done=1 THEN
LEAVE loop_xxx;
END IF;
BEGIN
#----------第一行数据:标题
IF _isnext=0 THEN
SET @sqltitle = CONCAT('select \'部门\',\'职位\',\'姓名\',\'编号\'');
END IF;
#----------第二行开始,人员信息
SET @sqlvalue = CONCAT('union all select ',_deptName,',',_userPriv,',',_userName,',',_userId,',');
DECLARE _inner TINYINT(1) DEFAULT 0;
DECLARE _eName VARCHAR(100); #--------所有项目名称的游标
DECLARE cur_att CURSOR FOR SELECT DISTINCT _EventName FROM tb_attendance ORDER BY _beginTime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _inner = 1;
OPEN cur_att;
loop_yyy:LOOP
FETCH cur_att INTO _eName;
IF _inner=1 THEN
LEAVE loop_yyy;
END IF;
#----这里需要将各个项目名称做为一个列显示
@sqltitle := CONCAT(@sqltitle+',', _eName);
DECLARE _sumcv FLOAT DEFAULT 0;
SELECT SUM(IFNULL(_cv,0)) INTO _sumcv FROM tb_attendance WHERE _realid=_userId AND _EventName=_eName;
#----这里需要将各个项目名称的得分做为列下的值显示
@sqlvalue := CONCAT(@sqlvalue+',', _sumcv); END LOOP;
CLOSE cur_att;
SET _isnext = 1;
END; END LOOP;
CLOSE cur_user;
#----这里将上面的动态SQL拼装
SET @allvalue = CONCAT(@sqltitle,@sqlvalue);
PREPARE _sqltext FROM @allvalue;
EXECUTE _sqltext;END;
想得到的效果是这样的:
__________________________________________________
|部门 | 职位 | 姓名 | 编号 | 项目A | 项目B | 项目C | ......
|IT部 | 程序员 | 张三 | 001 | 10 | 0 | 0 |.......
|IT部 | 程序员 | 李四 | 002 | 8 | 5 | 0 |.......
|IT部 | 程序员 | 张三 | 003 | 0 | 0 | 0 |.......
....
....
....
....请高手指教:DROP PROCEDURE IF EXISTS `test_myproc`CREATE PROCEDURE `test_myproc`()BEGIN
DECLARE _sqltext VARCHAR(5000);
DECLARE _deptName VARCHAR(100);
DECLARE _userPriv VARCHAR(100);
DECLARE _userName VARCHAR(100);
DECLARE _userId VARCHAR(1000;
DECLARE _done TINYINT(1) DEFAULT 0;
DECLARE _isnext TINYINT(1) DEFAULT 0;
#----全体员工的游标
DECLARE cur_user CURSOR FOR SELECT b.`DEPT_NAME`,a.`USER_PRIV_NAME`,a.`USER_NAME`,a.`USER_ID`
FROM USER a JOIN department b ON b.`DEPT_ID`=a.`DEPT_ID`
WHERE b.`DEPT_NO`=( SELECT dept_no FROM department WHERE `DEPT_ID`=a.`DEPT_ID`); DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;
OPEN cur_user;
loop_xxx:LOOP
FETCH cur_user INTO _deptName,_userPriv,_userName,_userId;
IF _done=1 THEN
LEAVE loop_xxx;
END IF;
BEGIN
#----------第一行数据:标题
IF _isnext=0 THEN
SET @sqltitle = CONCAT('select \'部门\',\'职位\',\'姓名\',\'编号\'');
END IF;
#----------第二行开始,人员信息
SET @sqlvalue = CONCAT('union all select ',_deptName,',',_userPriv,',',_userName,',',_userId,',');
DECLARE _inner TINYINT(1) DEFAULT 0;
DECLARE _eName VARCHAR(100); #--------所有项目名称的游标
DECLARE cur_att CURSOR FOR SELECT DISTINCT _EventName FROM tb_attendance ORDER BY _beginTime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _inner = 1;
OPEN cur_att;
loop_yyy:LOOP
FETCH cur_att INTO _eName;
IF _inner=1 THEN
LEAVE loop_yyy;
END IF;
#----这里需要将各个项目名称做为一个列显示
@sqltitle := CONCAT(@sqltitle+',', _eName);
DECLARE _sumcv FLOAT DEFAULT 0;
SELECT SUM(IFNULL(_cv,0)) INTO _sumcv FROM tb_attendance WHERE _realid=_userId AND _EventName=_eName;
#----这里需要将各个项目名称的得分做为列下的值显示
@sqlvalue := CONCAT(@sqlvalue+',', _sumcv); END LOOP;
CLOSE cur_att;
SET _isnext = 1;
END; END LOOP;
CLOSE cur_user;
#----这里将上面的动态SQL拼装
SET @allvalue = CONCAT(@sqltitle,@sqlvalue);
PREPARE _sqltext FROM @allvalue;
EXECUTE _sqltext;END;
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c..