在MySql存储过程中显示带有动态日期的月度考勤报告 [英] Display Monthly Attendance Report With Dynamic Dates in MySql Stored Procedure

问题描述

STUDENTNAME   | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |          A |          A |          A |          A |          P |          P |          P |          P |          P |          P |
|       Srinu |      2 |    22 |          P |          P |          P |          P |          P |          P |          P |          P |          P |          P |
|        Blah |      3 |    22 |          A |          P |          P |          P |          P |          P |          P |          P |          P |          P |

我想要这样的出勤报告

我的出勤表是

Att_id   User_id    Attendance_date Present/Absent
1          1              2015-01-01    P
2          2              2015-01-01    L
3          1              2015-01-02    P
4          2              2015-01-02    P
5          1              2015-01-03    L
6          2              2015-01-03    L

我有一个存储过程

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.present, '''') END) AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-05';

SET @sql 
  = CONCAT('SELECT ca.user_id,
               ', @sql, ' 
            from
            (
              select c.date, a.user_id
              from calendar c
              cross join attendance a
            ) ca
            left join attendance  p
              on ca.user_id= p.user_id
              and ca.date = p.attendance_date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
          group by ca.user_id');

但是这里什么是@sql.我没看懂这个 Sql 存储过程

but here What is @sql. I didn't understood This Sql Store Procedure

请问你能写在Mysql存储过程中吗

Plz can you write in Mysql Stored Procedure

推荐答案

一般语法

DELIMITER $$

CREATE
    PROCEDURE `moodle`.`procedureName`(IN id INT, IN NAME VARCHAR(20))

    BEGIN
          --    paste QUERY here;
                SELECT * FROM user WHERE user_name = NAME  AND user_id =id;
    END$$

DELIMITER ;

这是一个参数化存储过程，其中包含两个 IN 类型参数，名为 id，其 datatype 是 int 其他是 name 其 datatype 是 varchar(20) ，它们作为 user_name = NAME 和 user_name = NAME 在在存储过程中查询.

this is a parametrized stored procedure where which contain two IN type parameter named as id whose datatype is int other is name whose datatype is varchar(20) which are passe as user_name = NAME and user_name = NAME in query in the stored procedure.

您可以使用以下查询调用存储过程

you can call stored procedure using following query

call procedureName(1,'test');

@sql 不是存储过程，它是用户定义的变量，因为 var 在下面的情况下

@sql is not an stored procedure it is user defined variable as var is in below case

SET @var='testing varible';
SELECT @var;

通过运行上面的查询，您将获得存储在上面变量中的值

by running the query above you will get the value stored in the above variable

这篇关于在MySql存储过程中显示带有动态日期的月度考勤报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！