使用Mysql获取重复的所有术语的名称

Question

我正在编码的东西，以获得所有重复的术语，其中有重复的名称使用mysql查询。我目前的问题是，我在一个单一的事件中获取记录，我需要具有相同名称的所有数据。

我正在运行这个查询。

SELECT a.*,COUNT(name) AS occurrences 
            FROM wp_terms AS a 
            INNER JOIN wp_term_taxonomy AS b ON a.term_id = b.term_id  
            WHERE b.taxonomy = "ticker"
            GROUP BY a.name HAVING occurrences > 1'

当前结果

[0] => stdClass Object
        (
            [term_id] => 13872
            [name] => AAV
            [slug] => aav
            [term_group] => 0
            [occurrences] => 2
        )

预期结果

[0] => stdClass Object
            (
                [term_id] => 13872
                [name] => AAV
                [slug] => aav
                [term_group] => 0
                [occurrences] => 2
            )
    [1] => stdClass Object
            (
                [term_id] => 13873
                [name] => AAV
                [slug] => aav-2
                [term_group] => 0
                [occurrences] => 2
            )

对此有什么想法吗？

Answer 1

您可以使用窗口函数获取匹配的行：

SELECT t.*
FROM (SELECT t.*, 
             COUNT(*) OVER (PARTITION BY t.name) as cnt
      FROM wp_terms t JOIN
           wp_term_taxonomy tt
           ON t.term_id = tt.term_id  
      WHERE tt.taxonomy = 'ticker'
    ) t
WHERE cnt > 1
ORDER BY cnt DESC, name;

编辑：

在旧版本的MySQL中有多种方法。一个是：

SELECT t.*, 
       (SELECT COUNT(*)
        FROM wp_term_taxonomy tt
        WHERE tt.term_id = t.term_id AND
              tt.taxonomy = 'ticker'
    ) t
FROM wp_terms t
HAVING cnt > 1
ORDER BY cnt DESC, name;