SQL Server 2008：前 10 名和不同的在一起

SQL Server 2008: TOP 10 and distinct together

提问by user327301

As the title says, I'm using SQL Server 2008. Apologies if this question is very basic. I've only been using SQL for a few days. Right now I have the following query:

正如标题所说，我使用的是 SQL Server 2008。如果这个问题非常基本，请见谅。我只用了几天 SQL。现在我有以下查询：

SELECT TOP 10 p.id, pl.nm, pl.val, pl.txt_val

from dm.labs pl
join mas_data.patients p    
  on pl.id = p.id
  where pl.nm like '%LDL%'
  and val is not null

What I want to do is use select top n together with distinct values in the id column. Searching through some forums says to use

我想要做的是将 select top n 与 id 列中的不同值一起使用。搜索一些论坛说要使用

SELECT DISTINCT TOP 10 ...

but when I replace the first line with

但是当我用

SELECT DISTINCT TOP 10 p.id, pl.nm, pl.val, pl.txt_val

I get the same results as without the word distinct. What should I be doing to only get to filter out duplicate id entries?

我得到的结果与没有“distinct”这个词的结果相同。我应该怎么做才能过滤掉重复的 id 条目？

Thank you.

谢谢你。

采纳答案by Paul Creasey

The easy option is to use group by and select min/max for all other fields

简单的选择是使用 group by 并为所有其他字段选择 min/max

SELECT TOP 10 
    p.id, 
    max(pl.nm),
    max(pl.val),
    max(pl.txt_val)
from 
    dm.labs pl
join 
    mas_data.patients p    
on 
    pl.id = p.id
  where 
    pl.nm like '%LDL%'
and 
    val is not null
group by 
    p.id

This can get quite tedious for wide table so the other option is to use rank over and partiion

这对于宽表来说可能会变得非常乏味，因此另一种选择是使用 rank over 和 partiion

SELECT TOP 10 
    p.id, 
     pl.nm, 
     pl.val, 
   pl.txt_val, 
    rank() over(partition by p.id order by p.id) as Rank
from 
    dm.labs pl
join 
    mas_data.patients p    
on 
    pl.id = p.id
  where 
    pl.nm like '%LDL%'
and 
    val is not null
and
    Rank = 1

回答by Halim

Try

尝试

SELECT distinct TOP 10 MyId FROM sometable

回答by Vaishnavi Kumar

select top 10 p.id from(select distinct p.id  from tablename)tablename

回答by Paul Sasik

Few ideas:

几个想法：

1. You have quite a few fields in your select statement. Any value being different from another will make that row distinct.
2. TOP clauses are usually paired with WHERE clauses. Otherwise TOP doesn't mean much. Top of what? The way you specify "top of what" is to sort by using WHERE
3. It's entirely possible to get the same results even though you use TOP and DISTINCT and WHERE. Check to make sure that the data you're querying is indeed capable of being filtered and ordered in the manner you expect.

1. 您的 select 语句中有相当多的字段。任何与另一个不同的值都会使该行与众不同。
2. TOP 子句通常与 WHERE 子句配对。否则TOP没有多大意义。顶什么？您指定“顶部内容”的方式是使用 WHERE 进行排序
3. 即使您使用 TOP 和 DISTINCT 以及 WHERE，也完全有可能获得相同的结果。检查以确保您查询的数据确实能够以您期望的方式进行过滤和排序。

Try something like this:

尝试这样的事情：

SELECT DISTINCT TOP 10 p.id, pl.nm -- , pl.val, pl.txt_val
FROM dm.labs pl
JOIN mas_data.patients p    
on pl.id = p.id
where pl.nm like '%LDL%'
and val is not null
ORDER BY pl.nm

Note that i commented out some of the SELECT to limit your result set and DISTINCT logic.

请注意，我注释掉了一些 SELECT 以限制您的结果集和 DISTINCT 逻辑。

回答by cdonner

select top 10 * from
(
    select distinct p.id, ....
)

will work.

将工作。

回答by Jeff

I know this thread is old, but figured I would throw in what came up with since I just ran into this same issue. It may not be efficient, but I believe it gets the job done.

我知道这个线程很旧，但我想我会提出来，因为我刚刚遇到了同样的问题。它可能效率不高，但我相信它可以完成工作。

SELECT TOP 10 p.id, pl.nm, pl.val, pl.txt_val
INTO #yourTempTable
from dm.labs pl 
join mas_data.patients p on pl.id = p.id   
where pl.nm like '%LDL%' and val is not null

select p.id, pl.nm, pl.val, pl.txt_val
from #yourTempTable
where id IN (select distinct id from #yourTempTable)

回答by Jeff

well I wouldn't have expected it, but Halim's SELECT distinct TOP 10 MyId FROM sometable

好吧，我没想到，但是 Halim 的 SELECT 独特的 TOP 10 MyId FROM sometable

is functionally identical to Vaishnavi Kumar's select top 10 p.id from(select distinct p.id from tablename)tablename

在功能上与 Vaishnavi Kumar 的 select top 10 p.id from(select distinct p.id from tablename)tablename 相同

create table #names ([name] varchar(10))
insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('john')
insert into #names ([name]) values ('mark')
insert into #names ([name]) values ('matthew')
insert into #names ([name]) values ('luke')
insert into #names ([name]) values ('peter')

select distinct top 5 [name] from #names

select top 5 * from (select distinct [name] from #names) subquery 

drop table #names

produces the same results for both selects:

为两个选择产生相同的结果：

    name
1   bob
2   jim
3   john
4   luke
5   mark

it's curious that select top 5 distinct is not valid, but select distinct top 5 is and works as you might expect select top 5 distinct to work.

奇怪的是 select top 5 distinct 是无效的，但是 select distinct top 5 是并且可以像您期望的 select top 5 distinct 一样工作。

回答by Hafiz Asad

This is the right answer and you can find 3 heights value from table

这是正确的答案，您可以从表中找到 3 个高度值

SELECT TOP(1)  T.id FROM (SELECT DISTINCT TOP(3) st.id  FROM Table1 AS t1 , Table2 AS t2 WHERE t1.id=t2.id ORDER BY (t2.id) DESC ) T ORDER BY(T.id) ASC

回答by SQLServer

SELECT TOP 14 A, B, C
  FROM MyDatabase
  Where EXISTS 
   (
     Select Distinct[A] FROM MyDatabase
    )

回答by marc_s

You could use a Common Table Expression to get the top 10 distinct ID's and then join those to the rest of your data:

您可以使用 Common Table Expression 来获取前 10 个不同的 ID，然后将它们加入到您的其余数据中：

;WITH TopTenIDs AS
( 
   SELECT DISTINCT TOP 10 id
   FROM dm.labs 
   ORDER BY ......
)
SELECT 
    tti.id, pl.nm, pl.val, pl.txt_val
FROM
    TopTenIDs tti
INNER JOIN
    dm.labs pl ON pl.id = tti.id
INNER JOIN 
    mas_data.patients p ON pl.id = p.id
WHERE
    pl.nm like '%LDL%'
    AND val IS NOT NULL

That should work. Mind you: if you have a "TOP x" clause, you typically also need an ORDER BY clause - if you want the TOP 10, you need to tell the system in what order that "TOP" is.

那应该工作。请注意：如果您有一个“TOP x”子句，您通常还需要一个 ORDER BY 子句 - 如果您想要 TOP 10，您需要告诉系统“TOP”是什么顺序。

PS: why do you even join the "patients" table, if you never select any fields from it??

PS：如果您从不从中选择任何字段，您为什么还要加入“患者”表？