Posted By: jerryiii (Vsude dobre, v CR nejhur) on 'CZdatabases'
Title:     Jeste jednou
Date:      Tue Jun 19 11:03:17 2001

Tak sem tu zas :) mam nasledujici query a potreboval bych se nejak zbavit tech 
vnorenejch selectu, mate nekdo napady jak? Ja sem se SQL vyhybal jak cert 
krizi proto s nim temer neumim:

select [accessed] as [time],
(select count(*) from request r where r.[created] <= req.[accessed] AND 
r.[accessed] > req.[accessed]) AS concurrent
from [request] req
where ([accessed] > '20010501' AND [created] < '20010502')

union

select [created] as [time],
(select count(*) from request r where r.[created] <= req.[created] AND 
r.[accessed] >= req.[created]) AS concurrent
from [request] req
where ([accessed] > '20010501' AND [created] < '20010502')

order by [time] asc, [concurrent] desc

Ucelem tohohle je zjistit kolik bylo v danem okamziku soucasne pripojenych 
uzivatelu, session je definovana jako cas mezi [created] a [accessed] ... 
muzou bejt oboji stejny pokud s ejedna o jeden hit, proto tam nakonci je ten 
order [concurrent] desc, aby mi to dalo prvne 1 a pak 0 pri single hitu (kdyz 
to tam nedam tak to da presne naopak, prvne 0 a pak 1 coz nechci) ... Takze, 
da se nejak zbavit tech vlozenejch selectu? imho tam dost zdrzujou ...
 

          Jerry III

Enough said ...
http://jerryiii.home.sprynet.com/mica/index.htm