将所有房间与所有其他房间进行比较(笛卡尔产品)

我不能百分之百地肯定我理解你的问题,也没有足够的样本数据/预期输出可以确定,但我认为这个查询会给你想要的结果。它使用两个CTE:一个用于获取每个建筑物/房间/日期/时间组合的差异,另一个用于求和 RoomDirectionCount 列),然后只计算分组的行以获得 OccurrenceCount 列。

with atdiff AS (SELECT
                building, room, date, 'Morning' AS time_of_day,
                morning - lag(morning) over (partition by building, room order by date) AS diff
                from attendance
                UNION SELECT 
                building, room, date, 'Evening',
                evening - lag(evening) over (partition by building, room order by date) diff
                from attendance),
    dircounts AS (SELECT
                  building, room, time_of_day, SIGN(diff) AS direction, COUNT(*) AS DirectionCount
                  FROM atdiff
                  GROUP BY building, room, time_of_day, direction)
select a1.building AS Building1, 
       a1.room AS Room1, 
       a1.time_of_day AS TimeOfDay1, 
       (CASE SIGN(a1.diff) WHEN 1 THEN 'Up' WHEN -1 THEN 'Down' ELSE 'Unchanged' END) AS Direction1,
       a2.building AS Building2, 
       a2.room AS Room2,
       a2.time_of_day AS TimeOfDay2, 
       (CASE SIGN(a2.diff) WHEN 1 THEN 'Up' WHEN -1 THEN 'Down' ELSE 'Unchanged' END) AS Direction2,
       COUNT(*) AS OccurrenceCount,
       MIN(d1.DirectionCount) AS Room1DirectionCount,
       MIN(d2.DirectionCount) AS Room2DirectionCount
from atdiff a1
join atdiff a2 on a2.date = a1.date + 1 AND (a2.building != a1.building OR a2.room != a1.room)
JOIN dircounts d1 ON d1.building = a1.building AND d1.room = a1.room AND d1.time_of_day = a1.time_of_day AND d1.direction = SIGN(a1.diff)
JOIN dircounts d2 ON d2.building = a2.building AND d2.room = a2.room AND d2.time_of_day = a2.time_of_day AND d2.direction = SIGN(a2.diff)
where a1.diff is not NULL
group by Building1, Room1, TimeofDay1, Direction1, Building2, Room2, TimeOfDay2, Direction2
order by Building1, Room1, TimeofDay1 DESC, Direction1 DESC, Building2, Room2, TimeOfDay2 DESC, Direction2 DESC

输出太长,无法包含在此处,但我创建了一个 demo on dbfiddle . 交替的 demo on dbfiddle.uk

请注意,我使用了 WHERE a1.diff IS NOT NULL 如果要从第一天排除结果,可以将 COALESCE 在计算周围 diff 在 atdiff 然后就不用了。

我很难理解你在 第二 预期输出。然而,对于它的价值,这里有一些例子和演示可能会帮助您。

如果您使用的是MySQL8.0,那么您可以使用 window functions 访问与当前行相关的行。以下查询返回 第一 预期产量(尽管没有上一个日期, NULL 返回而不是 0 ,以区别于频率与前一天相同的情况):

select 
    a.*, 
    morning - lag(a.morning) over (partition by a.building, a.room order by a.date) morning_diff,
    evening - lag(a.evening) over (partition by a.building, a.room order by a.date) evening_diff
from attendance a
order by a.building, a.room, a.date

See the db fiddle .

使用旧版本的MySQL,您可以使用- LEFT JOIN 要访问上一行:

select 
    a.*,
    a.morning - a1.morning morning_diff,
    a.evening - a1.evening evening_diff
from 
    attendance a
    left join attendance a1 
        on a1.building = a.building and a1.room = a.room and a1.date = a.date - 1
order by a.building, a.room, a.date

见 this MySQL 5.7 db fiddle .

一旦您有了一个返回出勤差异的查询,您就可以很容易地看到它在外部查询中是上升还是下降。例如,考虑:

select t.*, 
    case 
        when morning_diff is null then 'Unknown'
        when morning_diff = 0 then 'Even'
        when morning_diff > 0 then 'Up'
        when morning_diff < 0 then 'Down'
    end morning_direction,
        case 
        when evening_diff is null then 'Unknown'
        when evening_diff = 0 then 'Even'
        when evening_diff > 0 then 'Up'
        when evening_diff < 0 then 'Down'
    end evening_direction
from (
    select 
        a.*, 
        morning - lag(a.morning) over (partition by a.building, a.room order by a.date) morning_diff,
        evening - lag(a.evening) over (partition by a.building, a.room order by a.date) evening_diff
    from attendance a
) t
order by t.building, t.room, t.date;

见 this db fiddle .