获取一种且只有一种类型的记录 [英] getting records of one and only one type

问题描述

我花了几个小时试图解决特定的问题，但我不明白.最后，我决定将其发布在这里.这是一些记录，

I was sitting several hours for trying to solve on specific problem but I couldn't get it. Finally, I decided to post it here. Here are some records,

╔════════╦════════════╦═════════╦══════════════════╗
║ AUTOID ║ PERSONNAME ║ FLOWER  ║ Other columns... ║
╠════════╬════════════╬═════════╬══════════════════╣
║      1 ║ Alex       ║ Rose    ║                  ║
║      2 ║ Rene       ║ Rose    ║                  ║
║      3 ║ Rene       ║ Jasmin  ║                  ║
║      4 ║ Darwin     ║ Rose    ║                  ║
║      5 ║ Alex       ║ Rose    ║                  ║
║      6 ║ Darwin     ║ Jasmin  ║                  ║
║      7 ║ Alex       ║ Rose    ║                  ║
║      8 ║ Rene       ║ Jasmin  ║                  ║
║      9 ║ Hello      ║ World   ║                  ║
║     10 ║ Hello      ║ World   ║                  ║
╚════════╩════════════╩═════════╩══════════════════╝

我怎么得到这个结果，在他/她的所有记录中只有一种花的人.

How can I get this result, the person who has only one type of flower on all his/her records.

╔════════════╦════════════╗
║ PERSONNAME ║ FLOWERNAME ║
╠════════════╬════════════╣
║ Alex       ║ Rose       ║
║ Hello      ║ World      ║
╚════════════╩════════════╝

我尝试过的最好的方法是下面的此查询，但仍然无法正常工作.

the best one I tried was this query below, and still not working.

SELECT  DISTINCT t1.PersonName, t1.Flower
FROM    TableName t1 
        INNER JOIN
        (
            SELECT  personname, COUNT(DISTINCT flower) totals
            FROM    tableName
            GROUP   BY personname, Flower
        )  t2 ON t1.personname = t2.personname and
                    t2.totals = 1

有什么想法吗?

推荐答案

您可以在此问题上使用GROUP BY子句，HAVING子句和COUNT()，无需加入子查询

you can use GROUP BY clause, HAVING clause and COUNT() on this problem, no need to join on a subquery

SELECT  PersonName, MAX(Flower) flowerName
FROM    TableName
GROUP   BY PersonName
HAVING  COUNT(DISTINCT Flower) = 1

• SQLFiddle演示
• SQLFiddle Demo

这篇关于获取一种且只有一种类型的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！