选择每个组中的最后一条记录以及计数

We are having a following sqlite3 table named 'atable'

我们有一个名为'atable'的sqlite3表

id  student assignment      grade
-----------------------------------
1    A       123             9
2    A       456             9
3    A       234             8
4    B       534             7
5    B       654             9
6    C       322             7

id is unique and incremented for each records. We are fetching latest assignment for each user by running query

id是唯一的,并为每个记录递增。我们通过运行查询为每个用户获取最新的分配

SELECT student, assignment, grade from atable where id in 
       (select max(id) from atable group by student) order by id desc

This is working fine. However, we also need to fetch number of assignments for each user where user received a particular grade in the same query, say 9.

这工作正常。但是,我们还需要为用户在同一查询中收到特定成绩的每个用户获取分配数量,例如9。

Any idea suggestion how to enhance or rewrite above query to return count as well. As mentioned, we are using sqlite3.

任何想法建议如何增强或重写上述查询以返回计数。如上所述,我们使用的是sqlite3。

Thanks

3 个解决方案

#1

You can do it using a correlated query:

您可以使用相关查询来执行此操作:

SELECT t.student, t.assignment, t.grade 
       (SELECT COUNT(*) FROM atable s
        WHERE s.student = t.student and s.grade >= 9) as total_above_9
from atable t
where t.id in 
   (select max(id) from atable group by student)
order by t.id desc

#2

It would be better to join to a derived table that contains an aggregated version of the original table:

最好是加入包含原始表的聚合版本的派生表:

select t1.student, t1.assignment, t1.grade, t2.cnt 
from mytable as t1
join (
   select student, max(id) as id, 
          count(case when grade = 9 then 1 end) as cnt
   from mytable 
   group by student
) as t2 on t1.id = t2.id

#3

Try this;)

select t1.student, t1.assignment, t1.grade, t2.count
from atable t1
inner join (select max(id) as id, count(if(grade=9, 1, null)) as count from atable group by student) t2
on t1.id = t2.id
order by t1.id desc

#1