统计mysql中一行中的项目数 [英] count number of items in a row in mysql

问题描述

  CREATE TABLE classlist 
（`id` int，`studentid` int，`subjectid` int，`presentid` int）
; 
 
 CREATE TABLE学生
（`id` int，`name` varchar（4））
; 
 
 CREATE TABLE subject 
（`id` int，`name` varchar（4））
; 
 
 CREATE TABLE classStatus 
（`id` int，`name` varchar（8））
; 
 
 INSERT INTO classlist 
（`id`，`studentid`，`subjectid`，`presentid`）
 VALUES 
（1，111，1，1） ，
（2,223,3,0），
（3,333,2,1），
（4，111,4,0​​），
（5， （6，222，3，0），
（7，333，2，1），
（8，111，4，0），
（9,111,4,0​​），
（10,111,4,0​​），
（11，111,1,1），
（12，333， 3，1），
（13，333，2，1），
（14，333，3，1）
; 
 
 INSERT INTO student 
（`id`，`name`）
 VALUES 
（111，'John'），
（222，'Kate '），
（333，'Matt'）
; 
 
 INSERT INTO subject 
（`id`，`name`）
 VALUES 
（1，'MATH'），
（2，'ENG '），
（3，'SCI'），
（4，'GEO'）
; 
 
 INSERT INTO classStatus 
（`id`，`name`）
 VALUES 
（0，'Absent'），
（1，'Present '）
; 
  

请参阅Fiddle http://sqlfiddle.com/#!2/a2d93/5

我可以指出谁不在场，

 选择
 studentid，
 students.name AS NAME，
 SUM（presentid = 1）AS as present，
 SUM（presentid = 0）AS absent 
 
 FROM classlist 
 INNER JOIN学生作为学生ON classlist.studentid = students.id 
 
 GROUP BY studentid，名称
  

但我想知道有多少班连续有一名学生参加了/错过了，这样老师就可以很容易地看到是否有人错过了很多时间，或者有人因为出席率高而获得奖励等等。我看过一些关于条纹的帖子，但是他们没有一个匹配数据的方式已经呈现给我，所以我不知道如何实现这一目标？

只要基于我的山姆（1，111，1，1），/ *约翰目前的数据连续1次* /
（2，222，3，0），/ * Kate连续1次* /
（3,333,2,1），/ * Matt Present连续1次* /
（4，111，4，0），/ *约翰缺席连续1次* /
（5，111，1，1），/ * John Present连续1次* /
（6，222，3，0），/ *凯特不在连续2次* /
（7，333，2，1），/ * Matt Present连续2次* /
（8，111，4，0），/ * John连续1次* /
（9，111，4，0），/ * John Absent连续2次* /
（10，111，4，0），/ * John连续2次连续* /
（11，111，1，1），/ * John Present连续1次* /
（12，333，3，1），/ * Matt连续出现3次* /
（13，333，2，1），/ * Matt Present连续4次* /
（14,333,3,1）/ * Matt连续出现5次* /
/ * S每个用户的最新状态* /
/ * John连续出现1次* /
/ *凯特连续出现2次* /
/ * Matt出现5次一行* /

约翰礼物1.

凯特缺席2。

马特现在5。 这应该提供与最后一行相同值的连续行数：

  select 
 classlist.studentid ，
 student.name，
 classStatus.name status，
 count（*）presentcnt 
 from 
 classlist内部加入学生
 on classlist.studentid = student .id 
内部连接classstatus 
 classlist.presentid = classstatus.id 
内部连接（
选择
 studentid，
 max（presentid = 0时的情况）然后id结束）max_0，
 max（presentid = 1的情况下，然后id结束）max_1 
 from classlist 
 group by studentid 
）s 
 on coales ce（classlist.id>至少（max_0，max_1）和
 classlist.id< =最大（max_0，max_1），1）
和s.studentid = classlist.studentid 
按类别列表.studentid 
  

在子查询中，我提取最大id，其中presentid = 0，最大id presentid = 1。

在外部查询中，我将提取并计算id大于max_0和max_1中最小值的所有行，并且<=最大值这两个。无论最后一个值是什么，这些都是与最后一个值相同的行。

如果 max_0 或 max_1 为空，这意味着所有行只有一个值，1或0，我们必须获取所有这些值。如果max_0或max_1中的一个为空，则整个条件也将为空。使用 Coalesce（condition，1 ）我在这种情况下返回所有行。

请注意，我正在分组通过 classlist.studentid 显示一些非聚集列，但是由于所有非聚集列具有相同的值，因此这是允许的情况。

I have a list of students that shows whether they were present or absent from a particular class.

    CREATE TABLE classlist
        (`id` int, `studentid` int, `subjectid` int, `presentid` int)
    ;

    CREATE TABLE student
        (`id` int, `name` varchar(4))
    ;

    CREATE TABLE subject
        (`id` int, `name` varchar(4))
    ;

    CREATE TABLE classStatus
        (`id` int, `name` varchar(8))
    ;

    INSERT INTO classlist
        (`id`, `studentid`, `subjectid`, `presentid`)
    VALUES
        (1, 111, 1, 1),
        (2, 222, 3, 0),
        (3, 333, 2, 1),
        (4, 111, 4, 0),
        (5, 111, 1, 1),
        (6, 222, 3, 0),
        (7, 333, 2, 1),
        (8, 111, 4, 0),
        (9, 111, 4, 0),
        (10, 111, 4, 0),
        (11, 111, 1, 1),
        (12, 333, 3, 1),
        (13, 333, 2, 1),
        (14, 333, 3, 1)
    ;

    INSERT INTO student
        (`id`, `name`)
    VALUES
    (111, 'John'),
    (222, 'Kate'),
    (333, 'Matt')
    ;

    INSERT INTO subject
        (`id`, `name`)
    VALUES
    (1, 'MATH'),
    (2, 'ENG'),
    (3, 'SCI'),
    (4, 'GEO')
    ;

    INSERT INTO classStatus
        (`id`, `name`)
    VALUES
    (0, 'Absent'),
    (1, 'Present')
    ;

See Fiddle http://sqlfiddle.com/#!2/a2d93/5

I can count who is absent and present overall with something like the below.

    SELECT
       studentid,
       students.name AS NAME,
       SUM(presentid = 1) AS present,
       SUM(presentid = 0) AS absent

    FROM classlist
    INNER JOIN student as students ON classlist.studentid=students.id

     GROUP BY studentid, NAME

But I want to know how many classes in a row a student has attended/missed, so that the teachers can easily see if someone has missed a lot of time or someone is rewarded for good attendance etc. I have seen some posts which talk about streaks but none of them seem to match the way the data has been presented to me so I am not sure how to acheive this?

Just to be clear based on my sample data below the out put for the streaks should be.

        (1, 111, 1, 1), /* John Present 1 times in a row */
        (2, 222, 3, 0), /* Kate Absent 1 times in a row */
        (3, 333, 2, 1), /* Matt Present 1 times in a row */
        (4, 111, 4, 0), /* John Absent 1 times in a row */
        (5, 111, 1, 1), /* John Present 1 times in a row */
        (6, 222, 3, 0), /* Kate Absent 2 times in a row */
        (7, 333, 2, 1), /* Matt Present 2 times in a row */
        (8, 111, 4, 0), /* John Absent 1 times in a row */
        (9, 111, 4, 0), /* John Absent 2 times in a row */
        (10, 111, 4, 0), /* John Absent 2 times in a row */
        (11, 111, 1, 1), /* John Present 1 times in a row */
        (12, 333, 3, 1), /* Matt Present 3 times in a row */
        (13, 333, 2, 1), /* Matt Present 4 times in a row */
        (14, 333, 3, 1) /* Matt Present 5 times in a row */
        /*Showing the latest status for each user*/
        /* John Present 1 times in a row */
        /* Kate Absent 2 times in a row */
        /* Matt Present 5 times in a row */

John present 1.

Kate absent 2.

Matt Present 5.

解决方案

This should give the count of the consecutive rows with the same value as the last row:

select
  classlist.studentid,
  student.name,
  classStatus.name status,
  count(*) presentcnt
from
  classlist inner join student
  on classlist.studentid=student.id
  inner join classstatus
  on classlist.presentid=classstatus.id
  inner join (
    select
      studentid,
      max(case when presentid=0 then id end)  max_0,
      max(case when presentid=1 then id end)  max_1
    from classlist
    group by studentid
  ) s
  on coalesce(classlist.id>least(max_0,max_1) and
       classlist.id<=greatest(max_0,max_1),1)
  and s.studentid=classlist.studentid
group by classlist.studentid

On the subquery I'm extracting the max id where presentid = 0 and the max id where presentid = 1.

On the outer query I'm extracting and counting all rows where id is > than the least of max_0 and max_1, and <= than the greatest of those two. No matter what the last value is, these are all the rows with the same value as the last one.

If either max_0 or max_1 is null, it means that all rows have just one value, 1 or 0, and we have to get all of them. If one of max_0 or max_1 is null, the whole conditions will be null as well. Using Coalesce( condition, 1) I'm returning all rows in that case.

Notice that I am grouping by classlist.studentid and showing some nonaggregated columns, but this is a situation in which it is allowed since all nonaggregated columns have the same value.

这篇关于统计mysql中一行中的项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！