时间戳

首页 > 解决方案 > MYSQL 别名连接不存在

mysql - MYSQL 别名连接不存在

问题描述

我有两张桌子。在TableNames中存储了几首歌曲的库。在TableTimes中存储了播放歌曲的确切日期和时间。

在每个新的一周里，我都会检查是否有以前从未播放过的新歌。简单的。但我的问题如下：我需要知道一首歌是否会在 12:00:00（24 小时）之前或之后播放。因此，我正在运行CASE WHEN并输出带有值+或-的别名TimeCase，请参阅下面的查询。如果在实际一周中有歌曲具有新的“ TimeCase ”，我的查询应该输出。您可以在下面找到 ID 为 101 的示例。

ID 101 在 201908 周不是新的，但它在 12:00:00 之后第一次播放，因此需要在我的查询输出中。

表名

+-------+-----+
| Name  | id  |
+-------+-----+
| Song1 | 100 |
+-------+-----+
| Song2 | 101 |
+-------+-----+
| Song3 | 102 |
+-------+-----+
| Song4 | 103 |

餐桌时间：

+--------+--------+------+
| Week   | Time   | idFI |
+--------+--------+------+
| 201908 | 08:00  | 100  |
+--------+--------+------+
| 201908 | 19:00  | 101  |
+--------+--------+------+
| 201907 | 09:00  | 101  |
+--------+--------+------+
| 201906 | 22:00  | 103  |

我的查询：

SELECT t2.idFI, t1.id, Name, TimeCase
    FROM TableTimes t2
    JOIN TableNames t1 ON t2.idFI = t1.id
    JOIN (
        SELECT idFI,
    CASE WHEN Time < '12:00:00' THEN '-'
        ELSE '+'
        END AS TimeCase
    FROM TableTimes GROUP BY idFI, TimeCase
    ) t3 ON t2.idFI = t3.idFI
    WHERE '201908' = Week
    AND deleteSZ = false
    AND   NOT EXISTS
            (
            SELECT  null
            FROM    TableTimes t5
                JOIN (
                    SELECT idFI,
                CASE WHEN Time < '12:00:00' THEN '-'
                    ELSE '+'
                    END AS TimeCaseBefore
                FROM TableTimes GROUP BY idFI, TimeCaseBefore
                ) t4 ON t5.idFI = t4.idFI
            WHERE   t5.idFI = t2.idFI
            AND TimeCaseBefore = TimeCase
            AND     Week<'201908'
            AND     deleteSZ = false
            GROUP BY t5.idFI, TimeCaseBefore)
    GROUP BY TimeCase, t2.idFI

这给了我以下内容：

+-------+-----+------+----------+
| Name  | id  | idFI | TimeCase |
+-------+-----+------+----------+
| Song1 | 100 | 100  | -        |
+-------+-----+------+----------+

我真正想要的是：

+-------+-----+------+----------+
| Name  | id  | idFI | TimeCase |
+-------+-----+------+----------+
| Song1 | 100 | 100  | -        |
+-------+-----+------+----------+
| Song2 | 101 | 101  | +        |
+-------+-----+------+----------+

我对我的问题的理解是，我应该能够将t5.idFI = t2.idFI更改为t5.TimeCaseBefore = t2.TimeCase由于别名，这是不可能的。

我该如何解决这个问题？

基于 P.Salmon 的最终解决方案：

select   tn.name,
            idfi,
            case when thiswkafter12 > 0 and priorwkafter12 = 0 and thiswkbefore12 > 0 and priorwkbefore12 = 0 then '+ -'
                 when thiswkafter12 > 0 and priorwkafter12 = 0 and priorwkbefore12 = 0 then '+'
                 when thiswkbefore12 > 0 and priorwkbefore12 = 0 and priorwkafter12 = 0 then '-'
            end as timecase
from
(
SELECT  idfi,
            sum(case when week = 201908 and time >= '12:00:00' then 1 else 0 end) as thiswkafter12,
            sum(case when week = 201908 and time <  '12:00:00' then 1 else 0 end) as thiswkbefore12,
            sum(case when week < 201908 and time >= '12:00:00' then 1 else 0 end) as priorwkafter12,
            sum(case when week < 201908 and time < '12:00:00' then 1 else 0 end) as priorwkbefore12
FROM TABLETIMES
group by idfi
having (thiswkafter12 > 0 and priorwkafter12 = 0 and thiswkbefore12 > 0 and priorwkbefore12 = 0) or
       (thiswkafter12 > 0 and priorwkafter12 = 0 and priorwkbefore12 = 0) or
       (thiswkbefore12 > 0 and priorwkbefore12 = 0 and priorwkafter12 = 0)
        ) s
join tablenames tn on tn.id = s.idfi    ;

标签： mysql

解决方案

也许一种更简单的方法可以计算出本周播放歌曲的频率并与前几周的播放频率进行比较，分为上午和下午。我假设您的 101 示例的反例也可以应用，即本周上午播放了一首歌曲但之前在下午播放的情况。

注意我添加了一首在本周上午和下午第一次播放的歌曲

+--------+----------+------+
| Week   | Time     | idFI |
+--------+----------+------+
| 201908 | 08:00:00 |  100 |
| 201908 | 19:00:00 |  101 |
| 201907 | 09:00:00 |  101 |
| 201906 | 22:00:00 |  103 |
| 201908 | 08:00:00 |  104 |
| 201908 | 13:00:00 |  104 |
| 201908 | 08:00:00 |  104 |
| 201908 | 13:00:00 |  104 |
+--------+----------+------+
8 rows in set (0.00 sec)

   select   tn.name,
                idfi,
                case when thiswkafter12 > 0 and priorwkafter12 = 0 then '+'
                      when thiswkbefore12 > 0 and priorwkbefore12 = 0 then '-'
                end as timecase
    from
    (
    SELECT  idfi,
                sum(case when week = 201908 and time >= '12:00:00' then 1 else 0 end) as thiswkafter12,
                sum(case when week = 201908 and time <  '12:00:00' then 1 else 0 end) as thiswkbefore12,
                sum(case when week < 201908 and time >= '12:00:00' then 1 else 0 end) as priorwkafter12,
                sum(case when week < 201908 and time < '12:00:00' then 1 else 0 end) as priorwkbefore12
    FROM TABLETIMES
    group by idfi
    having (thiswkafter12 > 0 and priorwkafter12 = 0) or
             (thiswkbefore12 > 0 and priorwkbefore12 = 0)
    ) s
    join tablenames tn on tn.id = s.idfi    ;

+-------+------+----------+
| name  | idfi | timecase |
+-------+------+----------+
| Song1 |  100 | -        |
| Song2 |  101 | +        |
| song5 |  104 | +        |
+-------+------+----------+
3 rows in set (0.00 sec)

您还没有说在 104 的情况下应该发生什么，所以我假设 pm 获胜。

推荐阅读