SQL:每天选择项目，但剥离多个条目 - SQL: Selecting items per day but strip multiple entrys

SQL: Selecting items per day but strip multiple entrys

本文关键字：剥离每天选择项目 SQL | 更新日期: 2023-09-27

我得到了一个accesslog，它记录了(注册)用户在每个项目上的每次点击。通过连接表，我得到了每个用户的视图，如下所示:

ID | projecttitle | Project release of Project |点击时间

的代码:

$request = "
    SELECT
        u.id,
        u.nachname,
        u.vorname,
        u.login,
        al.project,
        DATE_FORMAT(al.date, '%d. %b %Y, %H:%i') as date,
        a.titel,
        DATE_FORMAT(a.datum, '%d. %b %Y') as datum
    FROM
        user as u
        JOIN accesslog al ON al.user = u.id
        LEFT JOIN ausschreibungen as a ON al.project = a.id
    WHERE
        al.date >= '$interval_from'
        AND al.date <= '$interval_to'
        AND al.project != 0
        AND u.id = ".(int)$_GET['userid']."
    ORDER BY al.date DESC
        ";

自然输出给我每一个项目的每一次点击。

所以，现在对于真正的问题，所显示的数据应该减少到每天每个项目只显示一次点击。

我试过按天分组，当然不行，按项目分组，当然也不行。

User Table:
ID | Name | other useless stuff
accesslog Table:
User ID | ProjectID | Time
ausschreibungen Table:
Project ID | Project name | other useless stuff

我需要一些东西，如显示我所有条目为这一天，但剥离多个。

是这种复杂性仍然是可能的，而改变sql语句或我需要评估的数据与php?

如果后者是唯一的可能性，有什么建议从哪里开始吗?

祝你今天愉快!

解决方案:

$request = "
        SELECT DISTINCT
            u.id,
            u.nachname,
            u.vorname,
            u.login,
            al.project,
            DATE_FORMAT(al.date, '%d. %b %Y') as date,
            a.titel,
            DATE_FORMAT(a.datum, '%d. %b %Y') as datum
        FROM
            user as u
            JOIN accesslog al ON al.user = u.id
            LEFT JOIN ausschreibungen as a ON al.project = a.id
        WHERE
            al.date >= '$interval_from'
            AND al.date <= '$interval_to'
            AND al.project != 0
            AND u.id = ".(int)$_GET['userid']."
        GROUP BY al.project, date
        ORDER BY al.date DESC
            ";

请尝试这个(未经测试的)查询。您需要将各种记录的日期/时间值视为日期(我认为)

$request = "
    SELECT
        u.id,
        u.nachname,
        u.vorname,
        u.login,
        al.project,
        date(al.date)
        a.titel,
        MIN(a.datum)
    FROM
        user as u
        JOIN accesslog al ON al.user = u.id
        LEFT JOIN ausschreibungen as a ON al.project = a.id
    WHERE
        al.date >= '$interval_from'
        AND al.date <= '$interval_to'
        AND al.project != 0
        AND u.id = ".(int)$_GET['userid']."
    GROUP BY
        u.id,
        u.nachname,
        u.vorname,
        u.login,
        al.project,
        date(al.date)
        a.titel
    ORDER BY date(al.date) DESC
        ";