MySQL查询以在两列中显示重复项

Question

我需要计算 A 列中有重复记录的行数，B 列必须为 1 并且也必须重复。

SELECT COUNT(*) as num
FROM myTable
WHERE (columnB = 1 GROUP BY columnB HAVING COUNT(id_myTable) > 1) AND (columnA IN (SELECT columnA FROM myTable GROUP BY columnA HAVING COUNT(id_myTable) > 1))
ORDER BY columnC ASC`

假设您在 MySQL 中有这些名为 myTable 的表:

/---------------------------------/
|      id    | ColumnA | Column B |
|---------------------------------|
|          1 | Andre   |        1 |
|          2 | Joao    |        2 |
|          3 | Maria   |        1 |
|          4 | Joao    |        1 |
|          5 | Andre   |        1 |
|          6 | Maria   |        1 |
|          7 | Andre   |        2 |
/---------------------------------/

结果必须是 4，因为只有 id 1、3、5 和 6 的两列都是重复的，并且 columnB 的条件必须始终等于 1。

我的 PHP 代码给出结果:

$query = "select sum(c) from (select count(1) as c from myTable group by columnA, columnB having count(1) > 1 and columnB = 1) t";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[c];
$total_rows = $total_pages;

Answer 1

select sum(c) from (
                     select count(1) as c from myTable  
                     group by ColumnA, ColumnB 
                     having count(1) > 1 and ColumnB=1
                    ) t