SQL Server选择不同的地狱 [英] SQL Server select distinct hell

问题描述

我陷入了sql问题.我有一个表，其中有许多重复的条目，其列名如下:-

I'm stuck on a problem with sql. I have a table with many duplicate entries with column names like:-

eventnumber housenumber value1 value2

这些列名都不是主键，因为有很多重复项.我想做的是通过不同的门牌号选择另一个表，但是我似乎在复制整个表，我正在使用:-

None of these column names are the primary key as there are many duplicates. What I would like to do is select into another table by distinct housenumber but I seem to get the whole table copied across, I'm using:-

Select * into temp_table from schedule_temp where housenumber in (select distinct housenumerb from schedule_temp)

现在我将其分解一下，如果我这样做了:-

Now I broke it down a bit and if I do:-

Select distinct housenumber into temp from schedule_temp group by housenumber

我得到了一个具有唯一门牌号的表...但是然后我如何使用这个唯一表来驱动另一个选择，该选择从temp中选择门牌号，而仅从schedule_temp中获得一个门牌号的实例?希望有道理.

I get a table with the unique housenumbers... but then how could I use this unique table to drive another select that picks housenumbers from temp and only gets one instance of the housenumber from schedule_temp? Hope that makes sense.

如果您能挽救我的理智，啤酒就在我身上.

Beers are on me if you can save my sanity.

推荐答案

首先，我将数据放入具有自动增量ID的表中

First I'd get the data into a table with an auto increment id

因此创建一个具有id，eventnumber，housenumber，value1，value2的表，其中id是一个自动编号.

So create a table with id,eventnumber,housenumber,value1, value2 where id is an auto number.

然后

Insert Into NewTemp(eventnumber,housenumber,value1, value2)
Select eventnumber,housenumber,value1, value2 From schedule_temp

然后此查询应为您每间房子#1行

Then this query should guve you 1 row per house #

Select nt.* From NewTemp nt
Join (select max(id) as id, housenumber from NewTemp Group By housenumber) t on t.id=nt.id

这篇关于SQL Server选择不同的地狱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！