下拉式

Question

我想在使用rollup时将名字列和姓列组合起来，但它似乎将相同的姓氏复制到每个名字上：

SELECT IFNULL(CONCAT(u.firstname, ' ', u.lastname),'Total Projects') AS Name, count(DISTINCT(p.id) ) AS Total
FROM projects p
INNER JOIN users u ON p.creator_fk = u.id
GROUP BY Name WITH ROLLUP;

所以我的结局是：

Name           | Total

John Smith     | 10
Sarah Smith    | 25
Tracy Smith    | 5
Total Projects | 40

Answer 1

根据我的理解

SELECT IFNULL(name,'Total Projects') AS Name, count(DISTINCT(id)) AS Total 
from  (select CONCAT(u.firstname,' ', u.lastname) as name,p.id
    FROM projects p INNER JOIN users u ON p.creator_fk = u.id) as p  GROUP BY name WITH ROLLUP;

Answer 2

这就是我能够在不重复结果表中的条目的情况下将名字和姓氏连接起来的原因。

SELECT
  COALESCE(name, 'TOTAL') as name,
  COUNT(ticket_number) as tickets
FROM
  ( SELECT
      CONCAT(emp_last, ', ', emp_first) as name, ticket_number
    FROM
      project_tickets_table
    LEFT OUTER JOIN
      employees ON project_tickets_table.emp=employees.empl_id
    WHERE
      status in ('ACTI', 'ASSG') ) as p
GROUP BY ROLLUP (name)
ORDER BY cases DESC

注意，as p部件是必需的，但在本例中什么也不做。只要包含p，就可以将as something更改为任何您想要的内容。