實用SQL：訪問來源統計

因為要統計網站訪問日誌的來源，於是寫了以下SQL，查詢的結果基本OK，還有部分特殊的網域名稱及效率有待以後改善。

select substring(count_from_url,charindex('.',count_from_url)+1,<br />(case when (charindex('/',count_from_url,9))=0<br />then len(count_from_url)-charindex('.',count_from_url)<br />when (charindex('/',count_from_url,9))>0<br />then charindex('/',count_from_url,9)-charindex('.',count_from_url)-1<br />end)),<br />count(0) aa<br />from table_count where some_expression<br />group by<br />substring(count_from_url,charindex('.',count_from_url)+1,<br />(case when (charindex('/',count_from_url,9))=0<br />then len(count_from_url)-charindex('.',count_from_url)<br />when (charindex('/',count_from_url,9))>0<br />then charindex('/',count_from_url,9)-charindex('.',count_from_url)-1<br />end))<br />order by aa desc