mysql - 仅从一列中删除重复项

Question

所以我会有这样的东西。它的结果是对两个表的查询。

+------------------+------------------------------------+
| character_name   | title                              |
+------------------+------------------------------------+
| derp             | a                                  |
| derp             | b                                  |
| derp             | c                                  | 
| herp             | a                                  |
| herp             | b                                  |
| herp             | c                                  |
+-------------------------------------------------------+

我想要这个

+------------------+------------------------------------+
| character_name   | title                              |
+------------------+------------------------------------+
| derp             | a                                  |
|                  | b                                  |
|                  | c                                  | 
| herp             | a                                  |
|                  | b                                  |
|                  | c                                  |
+-------------------------------------------------------+

这可能吗？

Answer 1

你是这个意思吗？

select case when i.rnk = 1 THEN i.character_name  ELSE '' END as NAME
, i.title
from(
    select *
    , row_number() over (partition by character_name order by title) as rnk
    from t1
) i
order by i.character_name 

名字将获得您在结果中显示的值 1，您隐藏的任何其他内容。

更新 - MySql 版本:

SET @r_name:='';

select name, title FROM (
    select @r_name:=CASE WHEN @r_name = name THEN '' 
    ELSE name
    END AS name
    , @r_name:=name
    , title
    FROM
    t1
) t2