t-sql单表查询基础

t-sql单表查询：

top:

select top n * from 表名 order by [列名]

  

distinct:

 select distinct [列名],[列名] from 表名

  

 where : 

条件运算法：

字母>数字

A=a&&a>0

字符串顺位比较

逻辑运算符：

not>and>or

NULL

任何值与NULl比较结果一定为UNKNOWN：       

NULL只与true/false做运算为true/false

列名<>''，已经刷选掉了NULL

order by

select [字段列表] from 表名 order by [ASC|DESC]

聚集函数：avg()、count()、max()、min()、sum()

null排序最小

  

group by

select sum(amout) sumamout,status from 表名 group by status 

可以把多行合并为一行

聚合结果增加条件,关键字为having

 

关键字执行顺序：

(1)FROM <table> 

(2)WHERE <where_condition> 

(3)GROUP BY <group_by_list> 

(4)HAVING <having_condition> 

(5)SELECT 

(6)DISTINCT 

(7)ORDER BY <order_by_list> 

(8)<TOP_specification> <select_list> 

  

行转列练习：

 

create table a(
 aid int,
 name varchar(10),
 score int
 );
 drop table a;
 select * from a;
 insert into a (aid,name,score)
 values(1,'aaa',90);
 insert into a (aid,name,score)
 values(1,'aab',91);
 insert into a (aid,name,score)
 values(2,'aaa',92);
 insert into a (aid,name,score)
 values(2,'aac',93);
 insert into a (aid,name,score)
 values(3,'aaa',90);
 --第一步
 select 
 aid,
 case name when 'aaa' then score else 0 end as aaa,
 case name when 'aab' then score else 0 end as aab,
 case name when 'aac' then score else 0 end as aac
 from a
 --第二步
 select 
 aid,
 sum(case name when 'aaa' then score else 0 end) as aaa,
 sum(case name when 'aab' then score else 0 end )as aab,
 sum(case name when 'aac' then score else 0 end )as aac
 from a
 group by aid