念狃 镱驵腩忄螯 纛痼, Guest牋>>牋 骂轵绣汨耱疣鲨 项桉橡噔桦 桤狃囗眍 | 项滹桉囹
埋 纛痼禧 / Microsoft SQL Server 皖恹 蝾镨悟忮蜩螯
 SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
SiNtez_26
Member

悟牦溧:
杨钺龛: 199
埋 镳桠弪!

奏耱 耱嚯觇忄 礤钺躅滂祛耱 恹狃囹 珥圜屙桢 桤 蜞犭桷, 耦铗忮蝰蜮簋 漯筱铎, 爨犟桁嚯铎 蜞犭桷, 珥圜屙棹.
念 皴 镱瘥 桉镱朦珙忄 牦耱囵眍 菀:

SELECT FIELD_1
FROM TABLE 
WHERE FIELD_2 = (SELECT MAX(FIELD_2) FROM TABLE)


软蝈疱耋弪 忸珈铈眍耱 疱嚯桤圉梃 皴泐 玎镳铖 徨 怆铈屙睇 皴脲牝钼, 镱耜铍 玎镨皴 蜞犭桷圊 猁忄弪 铌铍 扈腚桀磬 , 赅 祉 赅驽蝰 (礤 铗痂鲟, 忸珈铈眍, 祉  蝾朦觐 赅驽蝰 :) ), 镱漕犴 玎镳铖 猁忄弪 漕耱囹铟眍 戾潆屙睇.
8 铌 13, 11:16牋牋[14938138]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
Glory
Member

悟牦溧:
杨钺龛: 104751
select top 1 * from table order by field_2 desc
8 铌 13, 11:18牋牋[14938150]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
with ties
Guest
Glory
select top 1 with ties * from table order by field_2 desc
8 铌 13, 11:19牋牋[14938172]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
Glory
Member

悟牦溧:
杨钺龛: 104751
with ties
Glory
select top 1 with ties * from table order by field_2 desc



SiNtez_26
恹狃囹 珥圜屙桢 桤 蜞犭桷
邃桧耱忮眄铄 麒耠

杨钺龛 猁腩 铗疱溧牝桊钼囗: 8 铌 13, 11:21
8 铌 13, 11:20牋牋[14938182]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
prog882
Guest
SELECT * FROM (
SELECT FIELD_1, MAXFIELD_2=MAX(FIELD_2) OVER() FROM TABLE 
) WHERE FIELD_2 = MAXFIELD_2
8 铌 13, 11:20牋牋[14938183]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
SiNtez_26
Member

悟牦溧:
杨钺龛: 199
埋屐 耧囫栳! 暑眈蝠箨鲨 Glory 恹汶滂 磬栳铍邋 镳桠脲赅蝈朦眍, 溧 溴豚弪 蝾朦觐 蝾, 黩 眢骓.
8 铌 13, 11:22牋牋[14938192]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
with ties
Guest
Glory
with ties
镳铒簌屙...


SiNtez_26
恹狃囹 珥圜屙桢 桤 蜞犭桷
邃桧耱忮眄铄 麒耠

桤磬鬣朦睇 玎镳铖-蝾 祛驽 恹溧螯 犷朦 钿眍 玎镨耔
8 铌 13, 11:24牋牋[14938209]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
Glory
Member

悟牦溧:
杨钺龛: 104751
with ties
桤磬鬣朦睇 玎镳铖-蝾 祛驽 恹溧螯 犷朦 钿眍 玎镨耔

青镳铖 噔蝾疣 眢骓 噔蝾痼 疱珞朦蜞 祛泱 猁螯 疣珥. 软圜 猁 玎麇 铐 耱嚯 玎溧忄螯 忸镳铖 ?
8 铌 13, 11:26牋牋[14938224]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
徨 桧溴犟
Guest
SiNtez_26
埋屐 耧囫栳! 暑眈蝠箨鲨 Glory 恹汶滂 磬栳铍邋 镳桠脲赅蝈朦眍, 溧 溴豚弪 蝾朦觐 蝾, 黩 眢骓.


戾 磬 耢屮眍 蜞犭梓赍 425.315 玎镨扈
铗耋蝰蜮桢 桧溴犟 镱 镱膻 桤 max
"蝾镱恹" 忄痂囗 镳铊沭噱 "礤牮囫桠铎" 铗 镳铞屙蜞: 58 镳铗桠 42
(  妁 皴疴屦 恹徼疣弪 镟疣腚咫桤 潆 蝾镟).
option (maxdop 1) "蝾镱恹" 忄痂囗 镳铊沭嚯 镳铒铕鲨 72:28

耦钺龛 镳桦铈屙 羿殡. 朽珈屦 - 35Kb
8 铌 13, 11:41牋牋[14938372]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
妙耱333
Member

悟牦溧:
杨钺龛: 3683
SiNtez_26
赅 祉 赅驽蝰 (礤 铗痂鲟, 忸珈铈眍, 祉  蝾朦觐 赅驽蝰 :) ), 镱漕犴 玎镳铖 猁忄弪 漕耱囹铟眍 戾潆屙睇.

篷腓 羼螯 桧溴犟 镱 镱膻 FIELD_2 篑腩忤 "WHERE FIELD_2 = (SELECT MAX(FIELD_2) FROM TABLE)" 皴脲牝桠眍, 蝾 玎镳铖 漕腈屙 铗疣犷蜞螯 猁耱痤. 杨狁蜮屙眍, 蜞觐 驽 耜铕铖螯, 黩 忄痂囗 Glory.

篷腓 桧溴犟 礤 蝾... 忭彗囡眍, 忄 忄痂囗 祛驽 铌噻囹 猁耱疱. 祛驽 戾潆屙礤. 袜漕 耢铗疱螯 觐黻疱蝽簋 耔蝮圉棹 镫囗 玎镳铖钼.
8 铌 13, 11:42牋牋[14938383]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
妙耱333
Member

悟牦溧:
杨钺龛: 3683
徨 桧溴犟
option (maxdop 1) "蝾镱恹" 忄痂囗 镳铊沭嚯 镳铒铕鲨 72:28

桁 麒耠囔 磬漕 铗眍耔螯 铖蝾痤骓铖螯,  驽 礤觇 镱塍-噌耱疣牝睇 "镱矬汔".
戾 磬 蜞犭桷 38 祀. 玎镨皴 "蝾镱恹" 忄痂囗 忸钺 镳铊沭嚯 疣玢痤祉 聍蛤铎 99:1, 眍 疱嚯铖蜩 蜞觐泐 犭桤觐 礤:
SELECT *
FROM TABLE 
WHERE FIELD_2 = (SELECT MAX(FIELD_2) FROM TABLE)

(1 row(s) affected)
Table 'TABLE'. Scan count 2, logical reads 1869742, physical reads 7, read-ahead reads 1863035, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 108290 ms,  elapsed time = 161989 ms.

SELECT TOP 1 * FROM TABLE ORDER BY FIELD_2 DESC

(1 row(s) affected)
Table 'TABLE'. Scan count 1, logical reads 934871, physical reads 4, read-ahead reads 931544, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 199470 ms,  elapsed time = 219584 ms.


耦钺龛 镳桦铈屙 羿殡. 朽珈屦 - 79Kb
8 铌 13, 12:03牋牋[14938625]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
徨 桧溴犟
Guest
妙耱333,

溧 箧!

"top":

Table 'LOG_T'. Scan count 1, logical reads 3281, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.

CPU time = 1469 ms, elapsed time = 1491 ms.

-----------
"not top":

Table 'Worktable'. Scan count 3, logical reads 1322937, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LOG_T'. Scan count 1, logical reads 3281, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

CPU time = 4531 ms, elapsed time = 4723 ms.
8 铌 13, 12:24牋牋[14938773]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
 Re: SELECT 项脲 WHERE 项脲2 = MAX(项脲2)  [new]
妙耱333
Member

悟牦溧:
杨钺龛: 3683
徨 桧溴犟
Table 'Worktable'. logical reads 1322937

泥 箧, 镱腠 镥鬣朦 :)
8 铌 13, 12:30牋牋[14938821]     悟忮蜩螯 | 骤蜩痤忄螯 杨钺螯 祛溴疣蝾痼
埋 纛痼禧 / Microsoft SQL Server 悟忮蜩螯