sqlite GROUP BY,然后WHERE,最后COUNT(在给定数据库的SQL中

jjjwad0x  于 2023-03-03  发布在  SQLite
关注(0)|答案(2)|浏览(154)

如果给我们一个如下所示的数据库:
说出表格名称:'附表'
| 行号|课程标识|评估类型|评估日期|通过了?|
| - ------|- ------|- ------|- ------|- ------|
| 1个|千|测试1|2020年9月1日|是|
| 第二章|零零一|测试2|2020年10月1日|数量|
| 三个|千|测试1|2020年9月2日|是|
| 四个|千|测试1|2020年10月11日|是|
| 五个|千|测试2|2020年9月1日|是|
| 六个|零零一|测试1|2020年10月1日|是|
我们如何编写一个查询,以便获得我们在第一次尝试时通过两个评估(test1和test2)的课程数量?(我们可以多次执行相同的评估)
例如,根据上面的数据,我希望查询打印1-因为我们在第一次尝试时通过了课程000的test1(第1行),并且在第一次尝试时通过了课程000的测试2(第5行)。但是,即使课程001的测试1通过了第一次尝试(行6),因为课程001的测试2在第一次尝试时失败(行2),所以我们不将课程001作为计数包括到我们的查询结果中。
这是我目前拥有的:

SELECT course_id, eval_type, min(date)
    FROM tbl
    GROUP BY course_id, eval_type
e4yzc0pl

e4yzc0pl1#

将查询分为两个步骤,第一个是"内部"查询,以获得第一次尝试,然后是外部查询,以获得"最终结果"-course_ids,其中两个通过了第一次尝试的测试。
示例:

查询1
select X.course_id
from
(
    select 
        row_number() over (partition by course_id, eval_type order by date) as seq,
        course_id,
        eval_type,
        date,
        passed
from tbl
) X
where
    X.seq = 1
    and X.passed = 'Y'
group by 
    X.course_id
    having count(X.course_id) = 2

我不尝试解决与此查询的关系。如果同一天可以发生两次尝试,一次通过,一次失败,那么应该添加进一步的逻辑...

查询2
select distinct t1.course_id
from
(
    select 
        course_id,
        eval_type,
        min(date) as date
    from
        tbl
    group by
        course_id,
        eval_type
) t1
where not exists (
    select * 
    from tbl t2 
    where 
        t2.course_id = t1.course_id
        and t2.date = t1.date
        and t2.passed = 'N')

此查询使用相关子查询方法(警告:当有许多行数据要处理时,这可能是低效的查询)。作为一个优点,与上面的查询1相比,如果有其它测试,而不仅仅是测试1和测试2,它将继续工作-在第一次尝试的日期的任何初始失败将从结果中排除course_id。

chhqkbe1

chhqkbe12#

使用子查询查找每种检查类型的最早日期,然后查找通过的总次数:

select t.course_id from (select t1.course_id, sum(t2.passed = 'Y') r 
   from (select t.course_id, t.eval_type, min(t.eval_date) d from tbl t group by t.course_id, t.eval_type) t1 
   join tbl t2 on t1.course_id = t2.course_id and t1.eval_type = t2.eval_type and t1.d = t2.eval_date
group by t1.course_id) t where t.r = 2

相关问题