SQL：如何仅选择某列上具有唯一值的行？

Thanks a million everyone for everyone's response. Unfortunately, none of the solutions appear to be working on my end, and my guess is that the example I've provided is messed up.

万分感谢所有人的回应。不幸的是，没有一个解决方案似乎在我的最终工作，我的猜测是我提供的例子搞砸了。

So let me try again.

所以让我再试一次。

My table looks like this:

我的表看起来像这样：

    contract    project activity
row1    1000    8000    10
row2    1000    8000    20
row3    1000    8001    10
row4    2000    9000    49
row5    2000    9001    49
row6    3000    9000    79
row7    3000    9000    78

Basically, the query I'm looking for would return "2000,49" for "contract, activity" because only contract #2000 has one, and ONLY one, unique activity value.

基本上，我正在寻找的查询将返回“2000,49”的“合同，活动”，因为只有合同＃2000只有一个，而且只有一个唯一的活动值。

Again, thanks a million in advance, boroatel

再次，感谢百万提前，boroatel

12 个解决方案

#1

Updated to use your newly provided data:

已更新以使用您新提供的数据：

The solutions using the original data may be found at the end of this answer.

使用原始数据的解决方案可以在本答案的最后找到。

Using your new data:

使用您的新数据：

DECLARE  @T TABLE( [contract] INT, project INT, activity INT )
INSERT INTO @T VALUES( 1000,    8000,    10 )
INSERT INTO @T VALUES( 1000,    8000,    20 )
INSERT INTO @T VALUES( 1000,    8001,    10 )
INSERT INTO @T VALUES( 2000,    9000,    49 )
INSERT INTO @T VALUES( 2000,    9001,    49 )
INSERT INTO @T VALUES( 3000,    9000,    79 )
INSERT INTO @T VALUES( 3000,    9000,    78 )

SELECT DISTINCT [contract], activity FROM @T AS A WHERE
    (SELECT COUNT( DISTINCT activity ) 
     FROM @T AS B WHERE B.[contract] = A.[contract]) = 1

returns: 2000, 49

回报：2000,49

Solutions using original data

解决方案使用原始数据

WARNING: The following solutions use the data previously given in the question and may not make sense for the current question. I have left them attached for completeness only.

警告：以下解决方案使用先前在问题中给出的数据，可能对当前问题没有意义。我只是为了完整而留下了他们。

SELECT Col1, Count( col1 ) AS count FROM table 
GROUP BY col1
HAVING count > 1

This should get you a list of all the values in col1 that are not distinct. You can place this in a table var or temp table and join against it.

这应该会为您提供col1中不相同的所有值的列表。您可以将它放在表var或temp表中并加入它。

Here is an example using a sub-query:

以下是使用子查询的示例：

DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) )

INSERT INTO @t VALUES( 'A', 'B', 'C' );
INSERT INTO @t VALUES( 'D', 'E', 'F' );
INSERT INTO @t VALUES( 'A', 'J', 'K' );
INSERT INTO @t VALUES( 'G', 'H', 'H' );

SELECT * FROM @t

SELECT col1, col2 FROM @t WHERE col1 NOT IN 
    (SELECT col1 FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) > 1)

This returns:

D   E
G   H

And another method that users a temp table and join:

另一种用户临时表和连接的方法：

DECLARE @t TABLE( col1 VARCHAR(1), col2 VARCHAR(1), col3 VARCHAR(1) )

INSERT INTO @t VALUES( 'A', 'B', 'C' );
INSERT INTO @t VALUES( 'D', 'E', 'F' );
INSERT INTO @t VALUES( 'A', 'J', 'K' );
INSERT INTO @t VALUES( 'G', 'H', 'H' );

SELECT * FROM @t

DROP TABLE #temp_table  
SELECT col1 INTO #temp_table
    FROM @t AS t GROUP BY col1 HAVING COUNT( col1 ) = 1

SELECT t.col1, t.col2 FROM @t AS t
    INNER JOIN #temp_table AS tt ON t.col1 = tt.col1

Also returns:

还返回：

D   E
G   H

#2

For MySQL:

对于MySQL：

SELECT contract, activity
FROM table
GROUP BY contract
HAVING COUNT(DISTINCT activity) = 1

#3

I'm a fan of NOT EXISTS

我是NOT EXISTS的粉丝

SELECT DISTINCT contract, activity FROM table t1
WHERE NOT EXISTS (
  SELECT * FROM table t2
  WHERE t2.contract = t1.contract AND t2.activity != t1.activity
)

#4

Try this:

尝试这个：

select 
         contract,
        max (activity) 
from
         mytable 
group by
         contract 
having
         count (activity) = 1

#5

Modified!

改性！

SELECT distinct contract, activity from @t a
WHERE (SELECT COUNT(DISTINCT activity) FROM @t b WHERE b.contract = a.contract) = 1

And here's another one -- shorter/cleaner without subquery

这是另一个 - 没有子查询的更短/更清洁

select contract, max(activity) from @t
group by contract
having count(distinct activity) = 1

#6

Utilizing the "dynamic table" capability in SQL Server (querying against a parenthesis-surrounded query), you can return 2000, 49 w/ the following. If your platform doesn't offer an equivalent to the "dynamic table" ANSI-extention, you can always utilize a temp table in two-steps/statement by inserting the results within the "dynamic table" to a temp table, and then performing a subsequent select on the temp table.

利用SQL Server中的“动态表”功能（查询括号括起的查询），您可以返回2000,49 w /以下。如果您的平台没有提供“动态表”ANSI扩展的等效项，则可以通过将“动态表”中的结果插入临时表，然后执行，在两步/语句中始终使用临时表临时表上的后续选择。

DECLARE  @T TABLE(
    [contract] INT,
    project INT,
    activity INT
)

INSERT INTO @T VALUES( 1000,    8000,    10 )
INSERT INTO @T VALUES( 1000,    8000,    20 )
INSERT INTO @T VALUES( 1000,    8001,    10 )
INSERT INTO @T VALUES( 2000,    9000,    49 )
INSERT INTO @T VALUES( 2000,    9001,    49 )
INSERT INTO @T VALUES( 3000,    9000,    79 )
INSERT INTO @T VALUES( 3000,    9000,    78 )

SELECT
    [contract],
    [Activity] =  max (activity)
FROM
    (
    SELECT
        [contract],
        [Activity]
    FROM
        @T
    GROUP BY
        [contract],
        [Activity]
    ) t
GROUP BY
    [contract]
HAVING count (*) = 1

#7

Assuming your table of data is called ProjectInfo:

假设您的数据表名为ProjectInfo：

SELECT DISTINCT Contract, Activity
    FROM ProjectInfo
    WHERE Contract = (SELECT Contract
                          FROM (SELECT DISTINCT Contract, Activity
                                    FROM ProjectInfo) AS ContractActivities
                          GROUP BY Contract
                          HAVING COUNT(*) = 1);

The innermost query identifies the contracts and the activities. The next level of the query (the middle one) identifies the contracts where there is just one activity. The outermost query then pulls the contract and activity from the ProjectInfo table for the contracts that have a single activity.

最里面的查询标识合同和活动。查询的下一级（中间一级）标识只有一个活动的合同。然后，最外层查询从ProjectInfo表中提取具有单个活动的合同的合同和活动。

Tested using IBM Informix Dynamic Server 11.50 - should work elsewhere too.

使用IBM Informix Dynamic Server 11.50进行测试 - 也应该在其他地方工作。

#8

Here is another option using sql servers count distinct:

这是使用sql servers count distinct的另一个选项：

DECLARE  @T TABLE( [contract] INT, project INT, activity INT )
INSERT INTO @T VALUES( 1000,    8000,    10 )
INSERT INTO @T VALUES( 1000,    8000,    20 )
INSERT INTO @T VALUES( 1000,    8001,    10 )
INSERT INTO @T VALUES( 2000,    9000,    49 )
INSERT INTO @T VALUES( 2000,    9001,    49 )
INSERT INTO @T VALUES( 3000,    9000,    79 )
INSERT INTO @T VALUES( 3000,    9000,    78 )



SELECT DISTINCT [contract], activity FROM @T AS A WHERE
    (SELECT COUNT( DISTINCT activity ) 
     FROM @T AS B WHERE B.[contract] = A.[contract]) = 1

#9

SELECT DISTINCT Contract, Activity
FROM Contract WHERE Contract IN (
SELECT Contract 
FROM Contract
GROUP BY Contract
HAVING COUNT( DISTINCT Activity ) = 1 )

#10

Sorry you're not using PostgreSQL...

对不起，你没有使用PostgreSQL ...

SELECT DISTINCT ON contract, activity * FROM thetable ORDER BY contract, activity

SELECT DISTINCT ON合约，活动*来自thetable ORDER BY合约，活动

http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-DISTINCT

Oh wait. You only want values with exactly one...

等一下。你只需要一个值...

SELECT contract, activity, count() FROM thetable GROUP BY contract, activity HAVING count() = 1

SELECT合约，活动，count（）FROM表格GROUP BY合约，活动HAVING count（）= 1

#11

Sorry old post I know but I had the same issue, couldn't get any of the above to work for me, however I figured it out.

对不起老帖子我知道，但我有同样的问题，无法得到任何上述为我工作，但我想出来了。

This worked for me:

这对我有用：

SELECT DISTINCT [column]As UniqueValues FROM [db].[dbo].[table]

SELECT DISTINCT [column] As UniqueValues FROM [db]。[dbo]。[table]

#12

SELECT DISTINCT Col1,Col2 FROM Table GROUP BY Col1 HAVING COUNT( DISTINCT Col1 ) = 1

SELECT DISTINCT Col1，Col2 FROM Table GROUP BY Col1 HAVING COUNT（DISTINCT Col1）= 1

#1