SQL练习题（1）

数据库开发

1. 查询每一年加之前年份的积累销售额。比如查询2003年的，就是将2003的销售金额加上以前的销售金额。

原始数据：

id ta_year ta_num

1 2001 500
2 2002 300
3 2003 600

查询后的数据：

ta_year 销售金额

2001 500
2002 800
2003 1400

相关SQL:

--build the table
create table testA
(
id int identity(1,1) not null primary key,
ta_year char(10) not null,
ta_num int not null
)
go

-- insert the records
insert into testA values('2001',500)
insert into testA values('2002',300)
insert into testA values('2003',600)

-- here comes the selection
select b.ta_year, sum(a.ta_num) 销售金额
from testA a, testA b   -- 自身连接的技巧
where a.ta_year

2. 写一个SQL语句，取出表S中第11~20条记录（SQL Server，以自动增长的ID作为主键，ID可能不连续）[分页查询]

select top 10 *
from tb
where id not in (select top 10 id
                 from tb)

select top 10 *
from tb
where id > (select max(id)
            from(select top 10 id
                 from tb) as A)

select *  --或列出实际所需列名
from(select *, row_number() over(order by id) row_num
     from tb)A
where row_num between 11 and 20

注意：

最后一种方法，不能写成：select *, row_number() over(order by SNO) row_num from SC where row_num between 11 and 20

3.行列转置。

假设有张学生成绩表(tba)如下：

姓名 课程 分数

张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94

查询后变成：

姓名 语文 数学 物理

李四 74 84 94
张三 74 83 93

相关SQL：

-- build the table
create table tba(姓名 varchar(10),课程 varchar(10),分数 int)
go

--insert the records
insert into tba values('张三' , '语文' , 74)
insert into tba values('张三' , '数学' , 83)
insert into tba values('张三' , '物理' , 93)
insert into tba values('李四' , '语文' , 74)
insert into tba values('李四' , '数学' , 84)
insert into tba values('李四' , '物理' , 94)
go

-- here comes the selection
select 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tba
group by 姓名

4.两张表如下：

T1：

id department

1 设计
2 市场
3 售后

T2：

id depID name

1 1 张三
2 1 李四
3 2 王五
4 3 彭六
5 4 陈七

写一条sql语句，使查询结果如下：

id depID department name

1 1 设计 张三
2 1 设计 李四
3 2 市场 王五
4 3 售后 彭六
5 4 未知部门 陈七

语句如下：

select T2.id, depID, department = IsNull(department,'未知部门'), [name]
from T1 right join T2 on T1.id = T2.depID

**注：**ISNULL ( check_expression , replacement_value )，表示check_expression若为空就返回replacement_value，否则就返回check_expression。

左外连接表示左边所有信息均列出，右外同理。

5.取出分组后的前两条记录[分组后的前几项]

qu co je

A 1 3
A 2 4
A 4 2
A 6 9
B 1 4
B 2 5
B 3 6
C 3 4
C 6 7
C 2 3

查询结果应为：

qu co je

A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4

即按 qu 分组后，再取 je 最大的前两列

select qu,co,je
from hard a
where je in(select top 2 je
            from hard
            where qu = a.qu
            order by je desc)
order by qu,je desc

select qu,co,je
from hard a
where (select count(*)
       from hard
       where qu = a.qu and je >= a.je) <= 2 -- n则为前n条记录

后一种比前一种效率要高

相似类型再来一例（2013-9-16加）

year product sales

2005 a 700
2005 b 550
2005 c 600
2006 a 340
2006 b 500
2007 a 220
2007 b 350

year product sales

2005 a 700
2006 b 500
2007 b 350

四种方法：

-- 方法一：
select a.year,a.product, a.sales
from @t a,(select year,max(sales) max_sales from @t group by year)b
where a.year = b.year and a.sales = b.max_sales
order by a.year

-- 方法二：
select year,product,sales from
(
select 
    year,product,sales,
    row_number() over(partition by year order by sales desc) Rank_Num
from @t
)A
where Rank_Num = 1

-- 方法三：
select
    year,product,sales
from @t a
where (select count(*) from @t where year = a.year and sales >= a.sales) <= 1 -- n則為前n項

-- 方法四：
select a.year,a.product, a.sales
from @t a
where not exists(select 1 from @t where year=a.year and sales > a.sales)

上述四种方法，效率依次升高。