在SQL Server中查询 [英] Query in SQL Server

问题描述

你好，

谁能帮我写下面的查询.

我有一个表Test，记录就像...

数字电子邮件类型
100 [e-mail] P
100 [e-mail] S
101 [e-mail] P
102 [e-mail] P


我需要如下结果.

数字电子邮件类型
100 [e-mail] P
101 [e-mail] P
102 [e-mail] P


意思是，不应该有重复的记录.因此对于100，我输入P和S.我只需要显示带有相应电子邮件的P记录.

任何人都可以帮忙吗?

谢谢.

Hello,

Can anyone help me on how to write the query for the below.

I have a table Test where records are like...

Number Email Type
100 [e-mail] P
100 [e-mail] S
101 [e-mail] P
102 [e-mail] P


I need the result like below.

Number Email Type
100 [e-mail] P
101 [e-mail] P
102 [e-mail] P


Means, No duplicates records should exist. So here for 100, I have type P and S. I need to just display P record with the corresponding Email.

Can Anyone help on this.

Thanks.

推荐答案

对于SQL2005

For SQL2005

select  a.Number,
        b.Email,
        b.Type
from    (
        select  distinct
                Number
        from    Test
        ) as a
cross
apply   (
        select  top 1
                Email,
                Type
        from    Test
        where   Number = a.Number
        order
        by      case
                    when Type = 'P' then 1
                    when Type = 'S' then 2
                    when Type = 'I' then 3
                    else 10
                end
        ) as b

您的要求不是很清楚-例如，"P"和""S"可能是唯一的类型"?用字母顺序的第一个类型"安全吗?

如果是这样，这可行:

Your requirements are not completely clear - for example, are ''P'' and ''S'' the only "types" there might be? Is it safe to take the alphabetical first "type"?

If so, this works:

select * from test2
 inner join (select number,  min(type) as type
             from test2
             group by number) as v1
             on test2.number = v1.number
             and test2.type = v1.type

斯科特

在下面的查询中使用

Hi, Use below query

Select Number,Email,Type from TableName where Number in (Select min(Number) from TableName group by Number)

它将解决您的问题.

如果您有解决方案，请别忘了标记.:-)

It will solve your problem.

Don''t forget to mark, if it is your solution.:-)

这篇关于在SQL Server中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！