select distict id,name,sex,tiem,price from table1,table2 where table1.id=table2.1id order by price
select T1.id,T1.name,T1.sex,T.time,T.price from table1 T1, ( select id,time,price from table2 a where not exists(select 1 from table2 b where a.id=b.id and a.time<b.time) )T where T1.id=T.id order by T.price
create table tb(id int,name varchar(10),sex varchar(10)) insert into tb select 1,'张三','男' union all select 2,'李四','男' union all select 3,'王五','男' union all select 4,'马六','女'create table tb2(id1 int,tiem datetime, Price int) insert into tb2 select 1,'2009-6-14',1200 union all select 1,'2009-6-12',1250 union all select 1,'2009-7-5 ',1100 union all select 1,'2009-7-24',1140 union all select 2,'2009-7-18',1080 union all select 2,'2009-6-24',1680 union all select 2,'2009-6-9 ',1630 union all select 2,'2009-6-20',1105 union all select 4,'2009-6-2 ',1205 union all select 4,'2009-6-15',800 union all select 4,'2009-7-3 ',1720 select id,name,sex,price from tb2 A join tb B on A.id1=B.id where not exists (select 1 from tb2 where tb2.id1=A.id1 and tb2.tiem>A.tiem) order by id desc/*----------- ---------- ---------- ----------- 4 马六 女 1720 2 李四 男 1080 1 张三 男 1140(所影响的行数为 3 行)*/
select id,name,sex,price from tb2 A join tb B on A.id1=B.id where not exists (select 1 from tb2 where tb2.id1=A.id1 and tb2.tiem>A.tiem) order by id desc
SELECT A.id,A.name,A.sex,price FROM table1 A INNER JOIN TABLE2 B ON a.id=b.id WHERE NOT EXISTS(SELECT 1 FROM table2 WHERE id=b.id AND tiem<a.tiem and tiem>getdate() ) AND tiem>GETDATE() order by price
SELECT A.id,A.name,A.sex,price FROM table1 A INNER JOIN TABLE2 B ON a.id=b.id WHERE NOT EXISTS(SELECT 1 FROM table2 WHERE id=b.id AND tiem<b.tiem and tiem>getdate() ) AND tiem>GETDATE() order by price
SET NOCOUNT ON DECLARE @table1 TABLE(id INT, [name] NVARCHAR(10), sex NVARCHAR(10)) INSERT @TABLE1 SELECT 1,N'张三' ,'男' INSERT @TABLE1 SELECT 2,N'李四', '男' INSERT @TABLE1 SELECT 3,N'王五','男' INSERT @TABLE1 SELECT 4,N'马六','女' DECLARE @table2 TABLE([1id] INT,tiem DATETIME, Price INT) INSERT @TABLE2 SELECT 1, '2009-6-14' , 1200 INSERT @TABLE2 SELECT 1,'2009-6-12' , 1250 INSERT @TABLE2 SELECT 1,'2009-7-5' , 1100 INSERT @TABLE2 SELECT 1 , '2009-7-24' , 1140 INSERT @TABLE2 SELECT 2 , '2009-7-18' , 1080 INSERT @TABLE2 SELECT 2 , '2009-6-24' , 1680 INSERT @TABLE2 SELECT 2 , '2009-6-9' , 1630 INSERT @TABLE2 SELECT 2 , '2009-6-20' , 1105 INSERT @TABLE2 SELECT 4 , '2009-6-2' , 1205 INSERT @TABLE2 SELECT 4 , '2009-6-15' , 800 INSERT @TABLE2 SELECT 4, '2009-7-3' , 1720 SELECT A.*,B.tiem ,B. Price FROM @TABLE1 A ,( SELECT * FROM @TABLE2 T WHERE tiem IN(SELECT TOP 1 tiem FROM @TABLE2 WHERE [1id]=T.[1id] ORDER BY DATEDIFF(HH,GETDATE(),TIEM)))B WHERE A.ID=B.[1ID] /*id name sex tiem Price ----------- ---------- ---------- ------------------------------------------------------ ----------- 1 张三 男 2009-06-12 00:00:00.000 1250 2 李四 男 2009-06-09 00:00:00.000 1630 4 马六 女 2009-06-02 00:00:00.000 1205*/
select T1.id,T1.name,T1.sex,T.time,T.price from table1 T1, ( select id, time = Min(time) from table2 a GROUP BY id )T where T1.id=T.id and t1.time = t.time order by T.price
借用数据: create table tb(id int,name varchar(10),sex varchar(10)) insert into tb select 1,'张三','男' union all select 2,'李四','男' union all select 3,'王五','男' union all select 4,'马六','女'create table tb2(id1 int,tiem datetime, Price int) insert into tb2 select 1,'2009-6-14',1200 union all select 1,'2009-6-12',1250 union all select 1,'2009-7-5 ',1100 union all select 1,'2009-7-24',1140 union all select 2,'2009-7-18',1080 union all select 2,'2009-6-24',1680 union all select 2,'2009-6-9 ',1630 union all select 2,'2009-6-20',1105 union all select 4,'2009-6-2 ',1205 union all select 4,'2009-6-15',800 union all select 4,'2009-7-3 ',1720 SELECT * FROM tb A INNER JOIN tb2 B ON a.id=b.id1 WHERE NOT EXISTS(SELECT 1 FROM tb2 WHERE id1=b.id1 AND tiem<b.tiem AND tiem>GETDATE()) AND tiem>GETDATE() ORDER BY price--result /*id name sex id1 tiem Price ----------- ---------- ---------- ----------- -------------------------------------------------- ----------- 4 马六 女 4 2009-06-15 00:00:00.000 800 2 李四 男 2 2009-06-20 00:00:00.000 1105 1 张三 男 1 2009-06-12 00:00:00.000 1250(所影响的行数为 3 行) */
declare @t1 table(id int,name varchar(20),sex varchar(3)) insert into @t1 select 1,'张三','男' union all select 2,'李四','男' union all select 3,'王五','男' union all select 4,'马六','女'
declare @t2 table(id int,item datetime,price money) insert into @t2 select 1,'2009-06-14',1200 union all select 1,'2009-06-12',1250 union all select 1,'2009-07-05',1100 union all select 1,'2009-07-24',1140 union all select 2,'2009-07-18',1080 union all select 2,'2009-06-24',1680 union all select 2,'2009-06-09',1630 union all select 2,'2009-06-20',1150 union all select 4,'2009-06-02',1250 union all select 4,'2009-06-15',850 union all select 4,'2009-07-03',1750 select * from @t1 a inner join (select id,max(item)item from @t2 group by id) b on a.id=b.id inner join @t2 c on b.id=c.id and b.item=c.item order by price desc
create table TB1(id int,name varchar(10),sex varchar(10)) insert into tb1 select 1,'张三','男' union all select 2,'李四','男' union all select 3,'王五','男' union all select 4,'马六','女'create table TB2(id int,time datetime, Price int) insert into tb2 select 1,'2009-6-14',1200 union all select 1,'2009-6-12',1250 union all select 1,'2009-7-5 ',1100 union all select 1,'2009-7-24',1140 union all select 2,'2009-7-18',1080 union all select 2,'2009-6-24',1680 union all select 2,'2009-6-9 ',1630 union all select 2,'2009-6-20',1105 union all select 4,'2009-6-2 ',1205 union all select 4,'2009-6-15',800 union all select 4,'2009-7-3 ',1720 SELECT A.*,[time]=CONVERT(NVARCHAR(10),B.[time],120),B.Price FROM ( SELECT * FROM TB2 A WHERE [time]>getdate() AND NOT EXISTS ( SELECT 1 FROM TB2 B WHERE B.id=A.id AND B.[time]>getdate() AND DATEDIFF (d,getdate(),B.[time])<DATEDIFF (d,getdate(),A.[time]) ) )AS B JOIN TB1 A ON A.id=B.id ORDER BY B.Priceid name sex time Price ----------- ---------- ---------- ---------- ----------- 4 马六 女 2009-06-15 800 2 李四 男 2009-06-20 1105 1 张三 男 2009-06-12 1250 (3 行受影响)
-- 修改下 select * from @t1 a inner join (select id,max(item)item from @t2 group by id) b on a.id=b.id inner join @t2 c on b.id=c.id and b.item=c.item order by price
from table1 T1,
(
select id,time,price
from table2 a
where not exists(select 1 from table2 b where a.id=b.id and a.time<b.time)
)T
where T1.id=T.id
order by T.price
create table tb(id int,name varchar(10),sex varchar(10))
insert into tb select
1,'张三','男' union all select
2,'李四','男' union all select
3,'王五','男' union all select
4,'马六','女'create table tb2(id1 int,tiem datetime, Price int)
insert into tb2 select
1,'2009-6-14',1200 union all select
1,'2009-6-12',1250 union all select
1,'2009-7-5 ',1100 union all select
1,'2009-7-24',1140 union all select
2,'2009-7-18',1080 union all select
2,'2009-6-24',1680 union all select
2,'2009-6-9 ',1630 union all select
2,'2009-6-20',1105 union all select
4,'2009-6-2 ',1205 union all select
4,'2009-6-15',800 union all select
4,'2009-7-3 ',1720 select id,name,sex,price from tb2 A join tb B on A.id1=B.id
where not exists (select 1 from tb2 where tb2.id1=A.id1 and tb2.tiem>A.tiem)
order by id desc/*----------- ---------- ---------- -----------
4 马六 女 1720
2 李四 男 1080
1 张三 男 1140(所影响的行数为 3 行)*/
where not exists (select 1 from tb2 where tb2.id1=A.id1 and tb2.tiem>A.tiem)
order by id desc
WHERE NOT EXISTS(SELECT 1 FROM table2 WHERE id=b.id AND tiem<a.tiem and tiem>getdate() ) AND tiem>GETDATE()
order by price
WHERE NOT EXISTS(SELECT 1 FROM table2 WHERE id=b.id AND tiem<b.tiem and tiem>getdate() ) AND tiem>GETDATE()
order by price
DECLARE @table1 TABLE(id INT, [name] NVARCHAR(10), sex NVARCHAR(10))
INSERT @TABLE1 SELECT 1,N'张三' ,'男'
INSERT @TABLE1 SELECT 2,N'李四', '男'
INSERT @TABLE1 SELECT 3,N'王五','男'
INSERT @TABLE1 SELECT 4,N'马六','女'
DECLARE @table2 TABLE([1id] INT,tiem DATETIME, Price INT)
INSERT @TABLE2 SELECT 1, '2009-6-14' , 1200
INSERT @TABLE2 SELECT 1,'2009-6-12' , 1250
INSERT @TABLE2 SELECT 1,'2009-7-5' , 1100
INSERT @TABLE2 SELECT 1 , '2009-7-24' , 1140
INSERT @TABLE2 SELECT 2 , '2009-7-18' , 1080
INSERT @TABLE2 SELECT 2 , '2009-6-24' , 1680
INSERT @TABLE2 SELECT 2 , '2009-6-9' , 1630
INSERT @TABLE2 SELECT 2 , '2009-6-20' , 1105
INSERT @TABLE2 SELECT 4 , '2009-6-2' , 1205
INSERT @TABLE2 SELECT 4 , '2009-6-15' , 800
INSERT @TABLE2 SELECT 4, '2009-7-3' , 1720
SELECT A.*,B.tiem ,B. Price FROM @TABLE1 A ,(
SELECT * FROM @TABLE2 T WHERE tiem IN(SELECT TOP 1 tiem FROM @TABLE2 WHERE [1id]=T.[1id] ORDER BY DATEDIFF(HH,GETDATE(),TIEM)))B
WHERE A.ID=B.[1ID]
/*id name sex tiem Price
----------- ---------- ---------- ------------------------------------------------------ -----------
1 张三 男 2009-06-12 00:00:00.000 1250
2 李四 男 2009-06-09 00:00:00.000 1630
4 马六 女 2009-06-02 00:00:00.000 1205*/
from table1 T1,
(
select id, time = Min(time)
from table2 a
GROUP BY id
)T
where T1.id=T.id
and t1.time = t.time
order by T.price
借用数据:
create table tb(id int,name varchar(10),sex varchar(10))
insert into tb select
1,'张三','男' union all select
2,'李四','男' union all select
3,'王五','男' union all select
4,'马六','女'create table tb2(id1 int,tiem datetime, Price int)
insert into tb2 select
1,'2009-6-14',1200 union all select
1,'2009-6-12',1250 union all select
1,'2009-7-5 ',1100 union all select
1,'2009-7-24',1140 union all select
2,'2009-7-18',1080 union all select
2,'2009-6-24',1680 union all select
2,'2009-6-9 ',1630 union all select
2,'2009-6-20',1105 union all select
4,'2009-6-2 ',1205 union all select
4,'2009-6-15',800 union all select
4,'2009-7-3 ',1720 SELECT * FROM tb A INNER JOIN tb2 B ON a.id=b.id1
WHERE NOT EXISTS(SELECT 1 FROM tb2 WHERE id1=b.id1 AND tiem<b.tiem AND tiem>GETDATE()) AND tiem>GETDATE()
ORDER BY price--result
/*id name sex id1 tiem Price
----------- ---------- ---------- ----------- -------------------------------------------------- -----------
4 马六 女 4 2009-06-15 00:00:00.000 800
2 李四 男 2 2009-06-20 00:00:00.000 1105
1 张三 男 1 2009-06-12 00:00:00.000 1250(所影响的行数为 3 行)
*/
insert into @t1
select 1,'张三','男' union all
select 2,'李四','男' union all
select 3,'王五','男' union all
select 4,'马六','女'
declare @t2 table(id int,item datetime,price money)
insert into @t2
select 1,'2009-06-14',1200 union all
select 1,'2009-06-12',1250 union all
select 1,'2009-07-05',1100 union all
select 1,'2009-07-24',1140 union all
select 2,'2009-07-18',1080 union all
select 2,'2009-06-24',1680 union all
select 2,'2009-06-09',1630 union all
select 2,'2009-06-20',1150 union all
select 4,'2009-06-02',1250 union all
select 4,'2009-06-15',850 union all
select 4,'2009-07-03',1750
select * from @t1 a inner join
(select id,max(item)item from @t2 group by id) b on a.id=b.id inner join @t2 c on
b.id=c.id and b.item=c.item order by price desc
insert into tb1
select 1,'张三','男' union all
select 2,'李四','男' union all
select 3,'王五','男' union all
select 4,'马六','女'create table TB2(id int,time datetime, Price int)
insert into tb2
select 1,'2009-6-14',1200 union all
select 1,'2009-6-12',1250 union all
select 1,'2009-7-5 ',1100 union all
select 1,'2009-7-24',1140 union all
select 2,'2009-7-18',1080 union all
select 2,'2009-6-24',1680 union all
select 2,'2009-6-9 ',1630 union all
select 2,'2009-6-20',1105 union all
select 4,'2009-6-2 ',1205 union all
select 4,'2009-6-15',800 union all
select 4,'2009-7-3 ',1720
SELECT A.*,[time]=CONVERT(NVARCHAR(10),B.[time],120),B.Price
FROM
(
SELECT * FROM TB2 A WHERE [time]>getdate()
AND NOT EXISTS
(
SELECT 1 FROM TB2 B
WHERE B.id=A.id AND B.[time]>getdate()
AND DATEDIFF (d,getdate(),B.[time])<DATEDIFF (d,getdate(),A.[time])
)
)AS B JOIN TB1 A ON A.id=B.id
ORDER BY B.Priceid name sex time Price
----------- ---------- ---------- ---------- -----------
4 马六 女 2009-06-15 800
2 李四 男 2009-06-20 1105
1 张三 男 2009-06-12 1250
(3 行受影响)
-- 修改下
select * from @t1 a inner join
(select id,max(item)item from @t2 group by id) b on a.id=b.id inner join @t2 c on
b.id=c.id and b.item=c.item order by price