使用Select Sql语句 [英] Using Select Sql statement

问题描述

我有一个这样的桌子.

ID名称
1 Samy
1萨米
1山姆
2约翰
3安妮


我将ID取不同的ID和相关名称.但是对于ID 1，它有3个不同的名称.在这种情况下，我可以从这三个名称中取一个.在这种情况下，如何编写Select sql语句.我正在使用Sql Server女士.

解决方案

尝试一下此操作

 选择 [Id]，
        [名称] =(选择 顶部  1  [名称] 来自 MyTable 其中 [Id] = a.[Id] 作者 [名称])
来自(
        选择 不同
                [ID]
        来自 MyTable
           
 
当我向您提出问题时，如果您想从特定名称的多个名称中仅获取一个名称，那么您可以尝试使用给定的语法

select Top 1 ID,Name from TableName where ID=''Searching-data'' order by NewID()
 

当您使用给定语法满足您的要求时，如下所述

select Top 1 ID,Name from TableName where ID=''1'' order by NewID()
 

您将得到类似
的输出
 ID  名称 

1 any_one_out_of(Samy，Sammy& Sam)
 
 




这将是一个糟糕的桌子设计.但是，当您已经有了数据时，可以尝试使用此方法



 选择 id，名称 FROM ( OVER 
    ( PARTITION   BY  id  ORDER   BY 名称) AS 排名
 FROM  yourTableName) as  RankedTable  WHERE  Rank = 1 
 
 
 
I have a table like this.



Id          Name     

1            Samy

1            Sammy

1            Sam

2            John

3            Anne





I wan''t to take distinct Id''s and relevant names for the ID. But for the Id 1 has 3 different names. In any case like this I can take any one name out of those 3 names. How can I write a Select sql statment for this case. I''m using Ms Sql server.
解决方案
Try this

select  [Id],
        [Name] = (select top 1 [Name] from MyTable where [Id] = a.[Id] order by [Name])
from    (
        select  distinct
                [Id]
        from    MyTable
        ) a

As I am getting you question you want to get only one Name out of many Name for specific ID if so then you can try the given syntax

select Top 1 ID,Name from TableName where ID=''Searching-data'' order by NewID()
 

when you use given syntax for your requirement as given below 

select Top 1 ID,Name from TableName where ID=''1'' order by NewID()


you will get output like

ID    Name 

1        any_one_out_of(Samy,Sammy & Sam)

Hi,



This would be a poor design of the table. However as you got data already you may try this



SELECT id,name FROM (SELECT id, name,RANK() OVER
    (PARTITION BY id ORDER BY name) AS Rank
FROM yourTableName  ) as  RankedTable WHERE Rank=1


                        
这篇关于使用Select Sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！