select a.title,a.username,b.adddate from table a inner join(select max(adddate) as adddate from table_Detail where table_Detail.title=a.title) as b on a.id=b.id
select a.title, a.username, b.adddate from table a, (select title,max(adddate) as adddate from table_Detail group by title) b where a.title=b.title
To daijingjie2002: 謝謝你,不過你的語句有問題哦,是不是應該這樣: select a.title,a.username,b.adddate from table a inner join(select title,max(adddate) as adddate from table_Detail Group By title) b on a.title=b.titleTable的記錄數:150901 TableDetail的記錄數:148994效能: Table 'Table'. Scan count 1, logical reads 5362, physical reads 0, read-ahead reads 0. Table 'Table_Detail'. Scan count 1, logical reads 712, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 1437 ms, elapsed time = 28792 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
To libin_ftsafe(子陌红尘) : 跟樓上的 libin_ftsafe(子陌红尘)的測試環境一樣下,效能報告如下,速度快10秒: Table 'Table'. Scan count 1, logical reads 5362, physical reads 0, read-ahead reads 0. Table 'Table_Detail'. Scan count 1, logical reads 712, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 1516 ms, elapsed time = 24743 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
a.title,
a.username,
b.adddate
from
table a,
(select title,max(adddate) as adddate from table_Detail group by title) b
where
a.title=b.title
謝謝你,不過你的語句有問題哦,是不是應該這樣:
select a.title,a.username,b.adddate from table a inner join(select title,max(adddate) as adddate from table_Detail Group By title) b on a.title=b.titleTable的記錄數:150901
TableDetail的記錄數:148994效能:
Table 'Table'. Scan count 1, logical reads 5362, physical reads 0, read-ahead reads 0.
Table 'Table_Detail'. Scan count 1, logical reads 712, physical reads 0, read-ahead reads 0.SQL Server Execution Times:
CPU time = 1437 ms, elapsed time = 28792 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
跟樓上的 libin_ftsafe(子陌红尘)的測試環境一樣下,效能報告如下,速度快10秒:
Table 'Table'. Scan count 1, logical reads 5362, physical reads 0, read-ahead reads 0.
Table 'Table_Detail'. Scan count 1, logical reads 712, physical reads 0, read-ahead reads 0.SQL Server Execution Times:
CPU time = 1516 ms, elapsed time = 24743 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.