SQL语句之语法汇总(三)(二)
记录了比赛成绩，创建代码已经给出（大家可以参考），根据该表，完成以下题目
--创建T_Scores
create table T_Scores(FName nvarchar(50) ,score nvarchar(50))
insert into T_Scores(FName,score)values('Tom','胜');
insert into T_Scores(FName,score)values('Tom','负');
insert into T_Scores(FName,score)values('Lucy','负');
insert into T_Scores(FName,score)values('Lucy','负');
insert into T_Scores(FName,score)values('Jerry','胜');
eg_1.输出新表，如果胜为1，负为0
select FName,
(
case score
when N'胜' then 1
else 0
end
)as '胜',
( www.2cto.com
case score
when N'负' then 1
else 0
end
)as '负'
from T_Scores
eg_2.输出格式(统计胜负)
FName 胜 负
Tom 1 1
Lucy 0 2
Jerry 1 0
select FName,
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 FName
3).有一张表记录了正负值（正表示收入，负表示指出），创建代码已经给出（大家可以参考），根据该表，完成以下题目
--创建表 T_Order
create table T_Order(FNumber varchar(50) not null,FAmount int not null);
insert into T_Order (FNumber,FAmount)values('Rk1',10);
insert into T_Order (FNumber,FAmount)values('Rk2',20);
insert into T_Order (FNumber,FAmount)values('Rk3',-30);
insert into T_Order (FNumber,FAmount)values('Rk4',-10);
统计出收支情况，字段为 单号 收入 支出
select FNumber as 单号,
(
case
when FAmount>0 then FAmount
else 0
end
)as 收入,
(
case
when FAmount<0 then abs(FAmount)
else 0
end
)as 支出
from T_Order
www.2cto.com
六. 表连接join...on
6.1 代码：
--常规定义：
select T_Orders.BillNo,T_Customers.name
from T_Orders join T_Customers on T_Orders.CustomerId=T_Customers.Id;
--可以定义如此，书写更简单：
select o.BillNo as 订单号,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id;
6.2分析
参考下方两个定义的表格，实现下方要求的操作。
--T_Customers表创建
create table T_Customers(
Id int not null,
Name nvarchar(50) collate Chinese_PRC_CI_AS null,
Age int null
);
insert into T_Customers(Id,Name,Age)values(1,N'tom',10);
insert into T_Customers(Id,Name,Age)values(2,N'jerry',15);
insert into T_Customers(Id,Name,Age)values(3,N'john',22);
insert into T_Customers(Id,Name,Age)values(4,N'lily',18);
insert into T_Customers(Id,Name,Age)values(5,N'lucy',18);
--T_Orders表创建
create table T_Orders(
Id int not null,
BillNo nvarchar(50) collate Chinese_PRC_CI_AS null,
CustomerId int null --看做T_Customers表的外键（虽然实际关系并未建立）!
);
insert into T_Orders(Id,BillNo,CustomerId)values(1,N'001',1);
insert into T_Orders(Id,BillNo,CustomerId)values(2,N'002',1);
insert into T_Orders(Id,BillNo,CustomerId)values(3,N'003',3);
insert into T_Orders(Id,BillNo,CustomerId)values(4,N'004',2);
insert into T_Orders(Id,BillNo,CustomerId)values(5,N'005',2);
insert into T_Orders(Id,BillNo,CustomerId)values(6,N'006',5);
insert into T_Orders(Id,BillNo,CustomerId)values(7,N'007',4);
insert into T_Orders(Id,BillNo,CustomerId)values(8,N'008',5);
内容示意图
要求及答案：
--显示所有订单号对应的姓名、年龄：
select o.BillNo as 订单号,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id;
--显示15岁以上顾客对应的订单号、年龄、年龄：
select o.BillNo,c.Name,c.Age www.2cto.com
from T_Orders as o join T_Customers as c on o.Customer