mssql sqlserver select查询关键字举例应用


摘要:
下文讲述select关键字的常见用法,如下所示:
实验环境:sql server 2008 R2


下文从select的各种用法上分析讲解”select关键字”的用法:
查询、去重、汇总、过滤、别名、虚拟列、新增统计列、计算列

   ---查询一个表中所有字段的数据
   select * from [数据表]
   ---查询数据表上指定字段的数据
   select [列1],[列2],[列3] from [数据表]
   ---查询数据表上的数据带查询条件 
    select * from [数据表]  where  [查询条件]
    
    ---去重 distinct 的写法 
      select distinct [列名] from [数据表]
    
    ---多字段去重
     select distinct [列名1],[列名2] from [数据表]
    
    ---去重写法2
    select * from (
      select *,row_number() over(partition by [分组列]) as keyId from [数据表] 
                  ) a where keyId =1
     
    ---群组汇总写法
    select [列名1],sum([列名2]) as [汇总列] from [数据表]
   
    ---汇总写法2
     select distinct * from (
    select [列名1],sum([列名2]) over(partition by [列名1]) as [汇总列] from [数据表] ) as t 
   
    ---过滤写法
    在数据表查询后面加上where条件,则可对数据表数据进行过滤
   
    ---表列名和列别名的写法
    select [列名1] as [列别名],[列名2] from [数据表] as [表别名]

    ---虚拟列的写法
   select [列1],[列2],[列3],888 as [虚拟列] from [数据表] 
       
 
    ---新增统计列
       select [列1],[列2],[列3],sum([列4])  over() as [统计列] from [数据表] 
     
    ---根据“列2”进行群组,形成新的统计列
       select [列1],[列2],[列3],sum([列4])  over(partition by [列2]) as [统计列] from [数据表] 

      ---计算列
        select [列1],[列2],[列3],[列2]+[列3] as [计算列] from [数据表] 
      
      ---列之间组合
        select [列1],[列2],[列3],[列5]+[列6] as [组合列] from [数据表]