SQL 笔试题

例子1：

现在有一个表 T_Order   ID(varchar(256)), ID为GUID       UserID(BigInt),      Price decimal(32,12),     OrderDate(Datetime)  OrderDate为可重复的DateTime

CREATE TABLE T_Order(
	[Id] [varchar](256) NOT NULL,
	[UserId] [bigint] NOT NULL,
	[Price] [decimal](32, 12) NOT NULL,
	[OrderDate] [datetime] NOT NULL
) 

INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,15000,'2018-01-20')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,15000,'2018-01-20')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,16000,'2018-01-20')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),2,17500,'2018-01-20')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),2,14000,'2018-02-19')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),2,19000,'2018-09-13')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,12000,'2018-09-16')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,18000,'2018-10-10')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),3,13500,'2018-11-17')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),3,25000,'2018-12-19')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),2,36000,'2018-12-19')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,48000,'2019-03-26')
INSERT INTO T_Order(Id,UserId,Price,OrderDate)VALUES(newid(),1,10500,'2019-04-30')

1>查询出每个用户最后一个订单的所有信息

思路：先在在每一行上加上一个行id，取名rowid 然后，然后根据UserId分组，取最大行的rowid的那条数据，这样就查询出了每个用户最后一个订单的rowid  这个表我们取名叫o ，然后再根据再在T_Order表中为每一行新增一个rowid，再来与 o表中的UserId和rowid来进行比对 这样就获取到了每个用户的最后一个订单的所有信息了

select t.Id,t.UserId,t.Price,t.OrderDate from (
select Row_Number() over(order by OrderDate asc) as rowid,* from T_Order) as t

join

(select o.UserId,MAX(o.rowid) as rowid from(
select Row_Number() over(order by OrderDate asc) as rowid,* from T_Order) as o
group by o.UserId) as oo

on t.rowid=oo.rowid and t.UserId=oo.UserId

order by UserId

2>查询出2018年份订单总金额前2名的UserId,订单总金额，并按照订单总金额排序

select top 2 t.UserId,t.TotalPrice  from (

select o.UserId, Sum(o.Price)as TotalPrice from ( 
select * from T_Order where OrderDate between '2018-01-01' and  '2018-12-31')as o
group by o.UserId ) as t

order by t.TotalPrice desc

3>删除重复的订单记录，并只保留订单中时间最小的一条，重复订单判断规则是 UserID,Price相同即认为重复

delete from T_Order where Id in(

select Id from T_Order a join (
select UserId,Price, MAX(OrderDate) as mindate from T_Order 
group by UserId,Price having count(1)>1 ) b
on a.OrderDate=b.mindate and a.Price=b.Price and a.UserId=b.UserId)

例子2

请分析下左表T_KC，写SQL实现下右表的效果

select top 3 * from (

select kind,max(code) as code from t_kc
group by kind) as t

order by t.code desc

例子3

有如下EMP表

1：统计各个部门的人数

select e.Department,count(e.Department)as Department  from EMP as e
group by e.Department

2：统计各个部门人数，且部门人数大于3

select e.Department,count(e.Department)as TotalNumber from EMP as e
group by e.Department
having count(e.Department)>3

3:性别转换

select Name,(case Sex when 'A' then '男'  when 'B' then '女' end) AS Sex,Department,Salary from EMP

4：工资Salary排名前三的人

select top 3 * from EMP 
order by Salary desc

5：工资Salary大于平均值的人

select * from EMP where Salary>(select AVG(Salary) from EMP)

或者写个存储过程

create proc Proc_Screen
as
declare @avgSalary int
select @avgSalary=AVG(Salary) from EMP
select * from EMP where Salary>@avgSalary

exec Proc_Screen

例子4

查询A表中存在ID字段值重复3次以上的记录

select id,name from A where id in(

select id from A group by id 
having count(id)>=3)

例子5

查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列

create proc proc_paging(@currentpage int,@pagesize int)
as
select top (@pagesize) ,ID,Name from (
select ROW_NUMBER() over(order by ID)as rowid,ID,Name from  A) t
where t.rowid>(@pagesize*(@currentpage-1))

exec proc_paging 4,10