php - 为什么 mysql order by 关键字不能按预期与 GROUP by 关键字一起工作？

Question

我有以下带有列的表格:

1) 用户

user_id  username

2)项目

p_id   p_name

3) 项目状态

psdi    p_id    cdid   cid   sid  short_list   res_sent   status_date

4) 公司

cid   company_name

5) 状态

sid    status_name    status_order    is_cv_sent 

6) 项目日志

pl_id   cdid    project_name     p_id   user_id   status   date_time    

现在，在这个 projects_log 表中存在相同的 p_id 值。

我想显示所有唯一的 p_id 以及最新的 date_time。 作为 DESC 顺序。

我正在使用以下查询，但无法获得结果。它没有向我显示所有唯一的 p_id 作为 date_time DESC 顺序:(

$get_log = mysqli_query($link, "SELECT 
    pl.*, 
    u.username, 
    p.p_name, 
    c.company_name, 
    s.status_name, 
    ps.* 
    FROM projects_log AS pl 
    LEFT JOIN users AS u ON u.user_id =  pl.user_id 
    LEFT JOIN projects AS p ON p.p_id = pl.p_id     
    LEFT JOIN project_status AS ps ON ps.p_id = pl.p_id 
    LEFT JOIN company AS c ON c.cid = ps.cid 
    LEFT JOIN status AS s ON s.sid = ps.sid 
    WHERE pl.cdid = '$cdid' GROUP BY pl.p_id
    ORDER BY pl.pl_id DESC ");

更新:

现在使用此查询，但显示错误消息:

mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

$get_log = mysqli_query($link, "SELECT * FROM (
    SELECT 
    pl.*, 
    u.username, 
    p.p_name, 
    c.company_name, 
    s.status_name, 
    ps.* 
    FROM projects_log AS pl 
    LEFT JOIN users AS u ON u.user_id =  pl.user_id 
    LEFT JOIN projects AS p ON p.p_id = pl.p_id     
    LEFT JOIN project_status AS ps ON ps.p_id = pl.p_id 
    LEFT JOIN company AS c ON c.cid = ps.cid 
    LEFT JOIN status AS s ON s.sid = ps.sid 
    WHERE pl.cdid = '$cdid' 
    ORDER BY pl.pl_id DESC )
    as new_tab
    GROUP BY new_tab.p_id ");

更新错误消息:

Error: Duplicate column name 'p_id' Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\dev\frontend\getContactDetails.php on line 648

Answer 1

尝试这个查询:-

Select * from (
SELECT
    pl.pl_id,pl.cdid,pl.project_name,pl.p_id,pl.user_id,pl.status,pl.date_time,
    ps.psid,ps.cid,ps.sid,ps.short_list,ps.res_sent,ps.status_date,  
    u.username, 
    p.p_name, 
    c.company_name, 
    s.status_name, 
    pl.pl_id grp_use
    FROM projects_log AS pl 
    LEFT JOIN users AS u ON u.user_id =  pl.user_id 
    LEFT JOIN projects AS p ON p.p_id = pl.p_id     
    LEFT JOIN project_status AS ps ON ps.p_id = pl.p_id 
    LEFT JOIN company AS c ON c.cid = ps.cid 
    LEFT JOIN status AS s ON s.sid = ps.sid 
    WHERE pl.cdid = '$cdid' 
    ORDER BY pl.pl_id DESC
) as new_tab
GROUP BY new_tab.grp_use