PHP MySQL的如何获得的行数，其中列是完全独特的

Question

我有一个表，看起来像这样

 
id   +  kID  
-------------------------- 
0   |   3 
1   |   6 
2   |   7 
3   |   6 
4   |   7 
5   |   5 

我想要做的是找到行的量，其中kID只发生一次。所以在这种情况下，变量的值应该是2，因为kID: 3 and 5 occurs only once所以我试图计算，而忽略了一切。我真的很难过，谢谢你的帮助。

Answer 1

这将表明，仅出现一次kID S：

SELECT kID, COUNT(kID) 
FROM table 
GROUP BY kID 
HAVING COUNT(kID) < 2 

结果

| KID | COUNT(KID) | 
-------------------- 
| 3 |   1 | 
| 5 |   1 |

See the demo

然后得到这些的总数：

SELECT Count(*) AS count 
FROM (SELECT kid, 
       Count(kid) 
     FROM tbl 
     GROUP BY kid 
     HAVING Count(kid) < 2) a 

结果

| COUNT | 
--------- 
|  2 | 

See the demo

Answer 2

试试这个

SELECT 
    id, 
    count(kID) as `Count` 
FROM mytable as t 
GROUP BY kID 
HAVING Count = 1 

Answer 3

如何

select count(*) from 
(select kid, count(*) from table group by kid having count(*) = 1) 

Answer 4

你可以做到以下几点：

select count(*) from 
(
    select kID, COUNT(*) [c] from tableName 
    group by kID 
) t 
where t.c = 1 

Answer 5

SELECT kID, 
COUNT(kID) 
FROM tableName 
GROUP BY kID 
HAVING COUNT(kID) = 1 

Answer 6

你可以用子选择这样做。这应该工作，但可能不是非常有效：

SELECT id, kID, COUNT(1) FROM (SELECT COUNT(1),kID FROM TABLE 
           GROUP BY kID 
           HAVING COUNT = 1) 

Answer 7

另一种方法来做到这一点。这将工作只要(id)是表的主键或有上(kid, id)唯一约束：

SELECT COUNT(*) AS cnt 
FROM 
    (SELECT NULL 
    FROM tableX 
    GROUP BY kid 
    HAVING MIN(id) = MAX(id) 
) AS g ; 

在SQL-Fiddle

上(kid, id)的索引将提高工作效率测试 - 也只有一个COUNT()将完成，而不是2.