例子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