有table1
PID PNAME
1 A
2 B
3 CTABLE2
CID PID CDATE
1 2 2013-03-18
2 3 2013-03-19
3 2 2013-03-17求按照表二的日期排序查询表一的所有数据
结果:
3 C
2 B
1 Asql
PID PNAME
1 A
2 B
3 CTABLE2
CID PID CDATE
1 2 2013-03-18
2 3 2013-03-19
3 2 2013-03-17求按照表二的日期排序查询表一的所有数据
结果:
3 C
2 B
1 Asql
(
select table1.PID, table1.Pname, CDATE, ROW_NUMBER()OVER(Partition by Pname ORDER BY CDATE desc) as num
from table1
left outer join table2 on table2.PID = table1.PID
)
select PID, Pname
from table3
where num = 1
order by CDATE desc
create table t1
(
PID INT,
PNAME NVARCHAR(1)
)
CREATE TABLE T2
(
CID INT,
PID INT,
CDATE DATETIME
)
INSERT INTO T1 VALUES(1,'A')
INSERT INTO T1 VALUES(2,'B')
INSERT INTO T1 VALUES(3,'C')INSERT INTO T2 VALUES(1,2,'2013-03-18')
INSERT INTO T2 VALUES(2,3,'2013-03-19')
INSERT INTO T2 VALUES(3,2,'2013-03-17')
SELECT DISTINCT PID,PNAME FROM
(
SELECT A.PID,A.PNAME,NUM = ROW_NUMBER() OVER (ORDER BY CDATE DESC)
FROM T1 A LEFT JOIN T2 B ON A.PID = B.PID
) T
ORDER BY PID DESC
/*
PID PNAME
----------- -----
3 C
2 B
1 A(3 行受影响)
create table t1
(
PID INT,
PNAME NVARCHAR(1)
)
CREATE TABLE T2
(
CID INT,
PID INT,
CDATE DATETIME
)
INSERT INTO T1 VALUES(1,'A')
INSERT INTO T1 VALUES(2,'B')
INSERT INTO T1 VALUES(3,'C')INSERT INTO T2 VALUES(1,2,'2013-03-18')
INSERT INTO T2 VALUES(2,3,'2013-03-19')
INSERT INTO T2 VALUES(3,2,'2013-03-17')
SELECT DISTINCT PID,PNAME FROM
(
SELECT A.PID,A.PNAME,NUM = ROW_NUMBER() OVER (ORDER BY CDATE DESC)
FROM T1 A LEFT JOIN T2 B ON A.PID = B.PID
) T
ORDER BY PID DESC
/*
PID PNAME
----------- -----
3 C
2 B
1 A(3 行受影响)
(
PID INT,
PNAME NVARCHAR(1)
)
CREATE TABLE Table2
(
CID INT,
PID INT,
CDATE DATETIME
)
INSERT INTO Table1 VALUES(1,'A')
INSERT INTO Table1 VALUES(2,'B')
INSERT INTO Table1 VALUES(3,'C')INSERT INTO Table2 VALUES(1,2,'2013-03-18')
INSERT INTO Table2 VALUES(2,3,'2013-03-19')
INSERT INTO Table2 VALUES(3,2,'2013-03-17')
Select table1.pid,table1.pname,s.cdate from table1,(select pid,cdate from table2) s where s.pid=table1.pid order by s.cdate
from table1 a
inner join table2 b
on a.PID = b.PID
order by b.CDATE desc
select A.* from A AS table1
RIGHT JOIN B as table2 ON A.PID=B.PID ORDER BY B.CDATE
order by t2.Cdate
select distinct t1.* from table 1 as t1 left outer join table2 as t2 on t1.pid = t2.pid order by t2.Cdate