如何仅从结果集中选择常用属性？

Lets say I have a table with columns such as:

可以说我有一个表格,如:

• ID
• Name
• City
• State
• ZIP

I need to write a query that will return only one row. This row will include City, State, and ZIP, but I only want a field to have a value if all values in the results set are the same, otherwise I want the field to be null.

我需要编写一个只返回一行的查询。此行将包括City,State和ZIP,但如果结果集中的所有值都相同,我只希望字段具有值,否则我希望该字段为null。

For example, if every record has the same State, then State would be in the result returned. If just one of the results has a different state, I want the field to be null. Is something like this possible in SQL Server 2005?

例如,如果每条记录都具有相同的State,则State将返回结果。如果只有一个结果具有不同的状态,我希望该字段为空。在SQL Server 2005中是否可以这样?

Basically, I want a query like this:

基本上,我想要一个像这样的查询:

SELECT City, State, ZIP
FROM Users
WHERE ID IN(1,2,3,4,5,6)

But only return a single row, with the specs I described above.

但只返回一行,我在上面描述的规格。

2 个解决方案

#1

SELECT
    CASE WHEN COUNT(DISTINCT city) = 1 THEN MAX(city) ELSE NULL END AS city,
    CASE WHEN COUNT(DISTINCT state) = 1 THEN MAX(state) ELSE NULL END AS state,
    CASE WHEN COUNT(DISTINCT zip) = 1 THEN MAX(zip) ELSE NULL END AS zip
FROM Users
WHERE ID IN(1,2,3,4,5,6)

After other answer:

其他答案后:

SELECT
    CASE WHEN COUNT(DISTINCT NULLIF(city,'**NULL**')) = 1 THEN MAX(city) ELSE NULL END AS city,
    CASE WHEN COUNT(DISTINCT NULLIF(state,'**NULL**')) = 1 THEN MAX(state) ELSE NULL END AS state,
    CASE WHEN COUNT(DISTINCT NULLIF(zip,'**NULL**')) = 1 THEN MAX(zip) ELSE NULL END AS zip
FROM Users
WHERE ID IN(1,2,3,4,5,6)

#2

If columns are not nullable, or if you want to ignore nulls then gbn's answer is correct. If, however, you need to treat null values as different values, try this:

如果列不可为空,或者如果要忽略空值,那么gbn的答案是正确的。但是,如果需要将空值视为不同的值,请尝试以下操作:

CASE WHEN COUNT(DISTINCT city) = 1 
      AND COUNT(city) = COUNT(*) 
     THEN MAX(city) ELSE NULL END AS city