C#基础之SQL笔记4
数据分组：
1.查询的字段只能是所要分的[组的属性](如聚合函数 avg()某一组的平均值 max()某一组的最大值等,也可以是属于某一组的某个特殊成员属性等),以及要用来分组的字段,即某一组的过滤信息，且不可缺少。
2.聚合函数不能出现在 where 子句中，但可以出现在 having 子句中，且 having 子句中只能包含分组的字段和聚合函数，即having也只能存放某一组的过滤信息。
3.having是对分组后的信息的过滤用的列和select中能用的列是一样的，having无法代替where。
限制结果集的范围：
例： select top 3 * from T_Employee (只取前3条数据)
去掉重复数据：
例：select distinct FDepartment,FSubCompany from T_Tmployee;
联合（Union）:
联合的查询语句中的查询的字段数目要一致
select FName,FAge,'工资' from T_TempEmployee
union
select FName,FAge,FSalary from T_Employee
select FName,FName,FAge,FDepartment from T_Employee
union
select FIdCardNumber,FName,FAge,'临时工，无部门' from T_TempEmployee
union会自动去除重复行，用union all可以去除重复行
select FName from T_Employee
union all
select FName from T_TempEmployee
流控制函数：
ISNULL()函数判断是否为空
SELECT ISNULL(FName,'佚名') as 姓名 FROM T_Employee;
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
ELSE defaultreturnvalue
END
例：
select FName,
(
case FLevel
when 1 then '普通客户'
when 2 then '会员'
when 2 then 'VIP'
else '未知客户类型'
end
) as 客户类型
from T_Customer
-----------------------------------------
select FName
(
case
when FSalary<2000 then '低收入'
when FSalary>=2000 then '中等收入'
else '高收入'
end
) as 收入水平
from T_Employee
-----------------------------------------
select FNumber
(
case
when FSalary<2000 then FAmount
else 0
end
) as 收入，
(
case
when FAmount<0 then ABS(FAmount)
else 0
end
) as 支出
from T_Test1
-----------------------------------------
把下表：
Name Score
拜仁 胜
奇才 胜
湖人 胜
拜仁 负
拜仁 负
奇才 胜
按下面格式输出：
Name 胜 负
拜仁 1 2
奇才 1 0
湖人 2 0
**************
select Name,
Sum(
case Score
when N'胜' then 1
else 0
end
) as 胜,
Sum(
case Score
when N'负' then 1
else 0
end
)as 负
from T_Scores
group by Name
join表的连接：
例：
select o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customs as c on o.CustomerId=c.Id
where c.Age>15
子查询：
例：
select * from
(
select * from T_Orders
) as o1
1.单值子查询
select 1 as f1,2,(select min(FYearPublicshed) from T_Book),
(select max(FYeayPublished) from T_Book) as f4
select 1 as f1,(select min(FYearPublished),max(FYearPublished) from T_Book)
2.单列多值子查询
select * from T_Reader
where FYearOfJoin in
(select distinct FYearPublished from T_Book)
select * from
(
select row_number() over(order by Fsalary desc) as rownum,
FNumber,FName,FSalary,FAge from T_empoyee
) as e1
where e1.rownum>=3 and e1.rownum<=5;
