set nocount on
use tempdb
goif object_id('Customers') is not null
drop table Customers
goif object_id('Orders') is not null
drop table Orders
gocreate table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
)insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion')create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
)insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null求每个客户的最近两个订单(假设orderid可以表示时间顺序)
环境是SQLServer2000
use tempdb
goif object_id('Customers') is not null
drop table Customers
goif object_id('Orders') is not null
drop table Orders
gocreate table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
)insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion')create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
)insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null求每个客户的最近两个订单(假设orderid可以表示时间顺序)
环境是SQLServer2000
Customers a,Orders b
where a.customerid=b.customerid
and b.orderid in(select top 2 orderid from Orders where customerid=b.customerid
order by orderid desc)
---照着写..
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
drop table Customers
go if object_id('Orders') is not null
drop table Orders
go create table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
) insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion') create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
) insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null
select a.*
from orders a
,orders b
where a.customerid = b.customerid
group by a.orderid,a.customerid
having count(case when a.orderid >= b.orderid then 1 else null end) <=2
/*
orderid customerid
----------- ----------
1 FRNDO
2 FRNDO
3 KRLOS
4 KRLOS
6 MRPHS
*/
use tempdb
goif object_id('Customers') is not null
drop table Customers
goif object_id('Orders') is not null
drop table Orders
gocreate table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
)insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion')create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
)insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY CUSTOMERID ORDER BY ORDERID DESC) 'ID'
,*
FROM ORDERS) T WHERE ID <=2
/*
ID orderid customerid
-------------------- ----------- ----------
1 7 NULL
1 2 FRNDO
2 1 FRNDO
1 5 KRLOS
2 4 KRLOS
1 6 MRPHS
*/
drop table Customers
go if object_id('Orders') is not null
drop table Orders
go create table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
) insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion') create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
) insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null --select * from Customers--select * from Ordersselect b.*,a.city from Customers a,Orders b where a.customerid=b.customerid and orderid=(select max(orderid) from Orders where customerid=b.customerid)
drop table Customers,Orders
/*orderid customerid city
----------- ---------- ----------
6 MRPHS Zion
5 KRLOS Madrid
2 FRNDO Madrid*/
drop table Customers
go if object_id('Orders') is not null
drop table Orders
go create table Customers
(
customerid char(5) not null primary key,
city varchar(10) not null
) insert into Customers values('FISSA','Madrid')
insert into Customers values('FRNDO','Madrid')
insert into Customers values('KRLOS','Madrid')
insert into Customers values('MRPHS','Zion') create table Orders
(
orderid int not null primary key,
customerid char(5) null references Customers(customerid)
) insert into Orders
select '1','FRNDO'
union
select '2','FRNDO'
union
select '3','KRLOS'
union
select '4','KRLOS'
union
select '5','KRLOS'
union
select '6','MRPHS'
union
select '7',null select * from Orders o
where (select count(*) from Orders where customerid=o.customerid and orderid>=o.orderid)<=2
orderid customerid
----------- ----------
1 FRNDO
2 FRNDO
4 KRLOS
5 KRLOS
6 MRPHS
7 NULL(6 行受影响)
where (select count(*) from Orders where customerid=o.customerid and orderid>=o.orderid)<=2