php - 使用 JOIN 的 Mysql 查询(LINK 票证、员工、猫、评论)

Question

这是我的表格

门票

 tic_id,       
 tic_cat      
 tic_priority 
 tic_cus      
 tic_date     
 tic_title    
 tic_msg      
 tic_files    
 tic_emp      
 tic_moved    
 tic_statue   
 tic_rate     

员工

 emp_id       
 emp_name     
 emp_username 
 emp_password 
 emp_cat      
 emp_special  
 emp_lastlogin
 emp_session  
 emp_code     
 emp_statue   
 emp_master   
 emp_ip       

猫

 cat_id      
 cat_type    
 cat_name    
 cat_statue  
 cat_delete  
 cat_date    
 cat_ip      
 cat_options 

评论

  com_id     
  tic_id     
  cus_id     
  emp_id     
  com_msg    
  com_time   
  com_ip     
  com_statue 

我需要这样的结果

 tic_id | tic_cat | cat_name | tic_title | tic_statue | tic_priority | tic_msg | emp_name | comments_row | last_comment |

我做了这个查询，但我有 2 个问题

查询是

SELECT
      tickets.tic_id
      ,tickets.tic_cat
      ,cats.cat_name
      ,tickets.tic_title
      ,tic_statue
      ,tic_priority
      ,tickets.tic_msg
      ,employee.emp_name
      ,count(comments.com_id)
      ,( SELECT comments.com_msg
            from comments
                 order by com_id DESC limit 1 )
          AS last_comment
      FROM tickets
      LEFT JOIN employee
                  on (tickets.tic_emp = employee.emp_id)
      LEFT join cats
                  on (tickets.tic_cat = cats.cat_id)
      LEFT JOIN comments
                  on(tickets.tic_id = comments.tic_id)

  WHERE tic_cus=2 /* 2 -> This Is Customer Id */
  GROUP BY comments.tic_id

我的问题是

我在数据库中有 3 个结果给客户编号 2 -> 只显示 2 个结果

我想获得最后评论 -> 2 个结果具有相同的最后评论

如何在没有这 2 个错误的情况下执行此查询

在新查询后编辑帖子

使用这个查询解决了第二个问题

SELECT
      tickets.tic_id
      ,tickets.tic_cat
      ,cats.cat_name
      ,tickets.tic_title
      ,tic_statue
      ,tic_priority
      ,tickets.tic_msg
      ,employee.emp_name
      ,count(comments.com_id)
      ,( SELECT comments.com_msg
            from comments
                 WHERE tickets.tic_id = comments.tic_id
                 order by com_id DESC limit 1 )
          AS last_comment
      FROM tickets
      LEFT JOIN employee
                  on (tickets.tic_emp = employee.emp_id)
      LEFT join cats
                  on (tickets.tic_cat = cats.cat_id)
      LEFT JOIN comments
                  on(tickets.tic_id = comments.tic_id)

  WHERE tic_cus=2
  GROUP BY comments.tic_id 

已解决

SELECT
      tickets.tic_id
      ,tickets.tic_cat
      ,cats.cat_name
      ,tickets.tic_title
      ,tic_statue
      ,tic_priority
      ,tickets.tic_msg
      ,employee.emp_name
      ,count(comments.com_id)
      ,( SELECT comments.com_msg
            from comments
                 WHERE tickets.tic_id = comments.tic_id
                 order by com_id DESC limit 1 )
          AS last_comment
      FROM tickets
      LEFT JOIN employee
                  on (tickets.tic_emp = employee.emp_id)
      LEFT join cats
                  on (tickets.tic_cat = cats.cat_id)
      LEFT JOIN comments
                  on(tickets.tic_id = comments.tic_id)

  WHERE tic_cus=2
  GROUP BY tickets.tic_id

Answer 1

1) 你能在这些表格中展示一些数据来证明吗？换句话说，你怎么知道你应该有三个记录？ select * from tickets where tic_cus = 2 本身会返回 3 条记录吗？

2) 您需要按工单过滤评论子查询。我还建议在子查询中也有评论计数，并完全省略 GROUP BY:

,( select count(comments.com_id)
     from comments
    where comments.tic_id = tickets.tic_id) as comment_count

,( select comments.com_msg
     from comments
    where comments.tic_id = tickets.tic_id
          ^^^^^^^ filter by ticket so not last of ALL comments
 order by com_id DESC limit 1) as last_comment