如何从MSSQL中的多个记录中获取唯一值 [英] How to get unique value from multiple record in MSSQL

问题描述

我在MSSQL中的[Location]表下面。

I have below [Location] table in MSSQL.

Location	Value
loc1      	value1    
loc1      	value2    
loc2      	value1    
loc2      	value2    
loc3      	value2    
loc3      	value3    
loc4      	value3    
loc4      	value4    

我想要明确的Value值，如下所示o / p：

I want distinct value of Value like below o/p:

Location	Value
loc1      	value1    
loc1      	value2    
loc3      	value3    
loc4      	value4 

我的尝试：

What I have tried:

select Location,value from Location
group by Location,value
order by location

推荐答案

create table #Location (
	Location varchar(10),
	Value	varchar(20)
	)


insert into #Location select 'loc1','value1'    
insert into #Location select 'loc1','value2'    
insert into #Location select 'loc2','value1'    
insert into #Location select 'loc2','value2'    
insert into #Location select 'loc3','value2'    
insert into #Location select 'loc3','value3'    
insert into #Location select 'loc4','value3'    
insert into #Location select 'loc4','value4'


select Min(Location) as Location, Value from #Location group by Value

结果：< br $>

Results:

Location	Value
loc1	value1
loc1	value2
loc3	value3
loc4	value4

根据下面其他字段的评论，包括ID字段，这是一个扩展的示例/解决方案：

Based on comments below of additional fields including an ID field, here is an expanded example/solution:

create table #Location (
	ID int IDENTITY(1,1),
	Location varchar(10),
	Value	varchar(20),
	FieldA datetime 
	)


insert into #Location(Location, Value, FieldA) select 'loc1','value1',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc1','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc2','value1',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc2','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc3','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc3','value3',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc4','value3',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc4','value4',GETDATE()


Select ID, LocSumm.Location, LocSumm.Value, FieldA from #Location L
	inner join (
select Min(Location) as Location, Value from #Location group by Value) as LocSumm
on L.Location = LocSumm.Location and
	L.Value = LocSumm.Value
order by LocSumm.Location, LocSumm.Value

基于此查询的新结果集：

And the new result set based on this query:

ID	Location	Value	FieldA
1	loc1	value1	2018-09-24 09:48:45.700
2	loc1	value2	2018-09-24 09:48:45.700
6	loc3	value3	2018-09-24 09:48:45.700
8	loc4	value4	2018-09-24 09:48:45.703

SQL SELECT DISTINCT Statement [ ^ ]

问题是双重的，第一个是微不足道的：

The problem is twofold, and the first one is trivial:

Location	Value
loc1      	value1    
loc1      	value2    
loc3      	value3    
loc4      	value4    

大概应该是

presumably should be

Location	Value
loc1      	value1    
loc2      	value2    
loc3      	value3    
loc4      	value4    

如果没有，那就毫无意义了。

然后你有第二个更大的问题。

当你使用 GROUP BY位置，值你没有告诉SQL按任何有用的分组 - 你说的是两个列的值必须相同才能成为一个组：所以loc1，value1和loc1，value2在不同的组中。



你需要什么要对它们进行分组，是指定一个列Location作为GROUP BY，然后使用聚合函数选择剩余数据（MIN，MAX，SUM，AVERAGE ...）这可能会有所帮助： SQL GROUP By和列'名称'无效选择列表因为......错误 [ ^ ]



你想做的事情有点复杂，因为你得到的结果并不明显你想要：假设loc1和loc2都有相同的值，但是在输出中选择一个不同的值会使你很难准确地计算出你想要做的事情......

If not, then it makes no sense at all.
Then you have the second, rather bigger problem.
When you use GROUP BY Location, ValueYou are not telling SQL to group by anything useful - you are saying that the values both columns must be the same to be a single group: so "loc1, value1" and "loc1, value2" are in separate groups.

What you have to do to group them, is to specify one column "Location" as the GROUP BY, and then use an aggregate function to select the remaining data (MIN, MAX, SUM, AVERAGE ...) This may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

What you want to do is a little complicated, because it's not at all obvious why you get the results you want: given that loc1 and loc2 both have the same Values, but select a different Value in the output makes it extremely difficult to work out exactly what you are trying to do ...

这篇关于如何从MSSQL中的多个记录中获取唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！