The SQL query from hell:
SELECT count(log_id) as tot, max(log_datetime) as latest,
case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
FROM tblLog
WHERE log_referer LIKE ‘%q=%’GROUP BY case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
as log_refererORDER BY tot desc, case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
Ja meneer, da werkt. Nee meneer, ik ben niet beschaamd. 🙂
Dat geeft bijvoorbeeld voor de referrers sinds vannamiddag ongeveer 15u:
| query | hits | last | |
|---|---|---|---|
| 1. | veronique de kock naakt | 7 | 2003-04-08 19:09:27.0 |
| 2. | veronique de cock | 6 | 2003-04-08 21:52:08.0 |
| 3. | veronique de cock naakt | 4 | 2003-04-08 18:34:21.0 |
| 4. | “julie taton nue” | 2 | 2003-04-08 21:14:26.0 |
| 5. | brahim idool 2003 | 2 | 2003-04-08 18:44:04.0 |
| 6. | brahim van idool 2003 | 2 | 2003-04-08 21:13:44.0 |
| 7. | jolie naaktfotos | 2 | 2003-04-08 19:30:12.0 |
| 8. | julie taton | 2 | 2003-04-08 15:06:14.0 |
| 9. | julie taton nue | 2 | 2003-04-08 20:37:51.0 |
| 10. | monokini | 2 | 2003-04-08 20:09:51.0 |
| 11. | veronique heeft mooie borsten | 2 | 2003-04-08 17:03:54.0 |
| 12. | veronique naakt | 2 | 2003-04-08 15:20:48.0 |
| 13. | “christina aguilera naakt” | 1 | 2003-04-08 14:20:26.0 |
| 14. | “blowjob contest” | 1 | 2003-04-08 18:01:16.0 |
| 15. | “for your thighs only” +angel | 1 | 2003-04-08 20:29:23.0 |
| 16. | “king zog” | 1 | 2003-04-08 15:01:24.0 |
| 17. | “proxy.telenet.be” | 1 | 2003-04-08 16:19:55.0 |
| 18. | +sneeuwwitje+bloot | 1 | 2003-04-08 15:43:48.0 |
| 19. | xp process priority set media player | 1 | 2003-04-08 15:05:44.0 |
| 20. | 50 jaar smurfen gezien | 1 | 2003-04-08 15:31:31.0 |
| 21. | amercom weblog | 1 | 2003-04-08 16:04:19.0 |
| 22. | animal crackers | 1 | 2003-04-08 17:14:31.0 |
| 23. | arabia dolfijn | 1 | 2003-04-08 19:49:50.0 |
| 24. | aunt peg | 1 | 2003-04-08 19:06:00.0 |
| 25. | boygirlbang | 1 | 2003-04-08 17:15:26.0 |
| 26. | boygirlbang password | 1 | 2003-04-08 18:16:07.0 |
| 27. | brahim idool 2003 ze is van mij | 1 | 2003-04-08 16:23:45.0 |
| 28. | de borsten van veronique de kock | 1 | 2003-04-08 16:03:08.0 |
| 29. | de cock naakt | 1 | 2003-04-08 20:17:50.0 |
| 30. | de+cock+veronique | 1 | 2003-04-08 19:54:08.0 |
| 31. | donald knuth | 1 | 2003-04-08 19:08:33.0 |
| 32. | eerste eredame miss belgie 2003 | 1 | 2003-04-08 16:31:42.0 |
| 33. | happiness bbc2 | 1 | 2003-04-08 17:37:48.0 |
| 34. | hemmerechts+dagboek+sex | 1 | 2003-04-08 16:23:38.0 |
| 35. | homesite+ serial number | 1 | 2003-04-08 21:35:27.0 |
| 36. | java + “unknown source” | 1 | 2003-04-08 18:44:56.0 |
| 37. | jolie van big brother naakt | 1 | 2003-04-08 19:25:06.0 |
| 38. | julie taton naked | 1 | 2003-04-08 17:19:09.0 |
| 39. | laserpointer+build | 1 | 2003-04-08 17:11:41.0 |
| 40. | laserpointer+kopen | 1 | 2003-04-08 17:16:12.0 |
| 41. | link:www.wired.com/news/conflict/0,2100,58346,00.html | 1 | 2003-04-08 15:50:23.0 |
| 42. | martine tanghe | 1 | 2003-04-08 20:33:42.0 |
| 43. | martine tanghe naakt | 1 | 2003-04-08 20:42:06.0 |
| 44. | midsommer murders | 1 | 2003-04-08 16:03:32.0 |
| 45. | mike’s apartment +divx | 1 | 2003-04-08 16:20:25.0 |
| 46. | mikesapartment | 1 | 2003-04-08 22:07:21.0 |
| 47. | naakte m-kids | 1 | 2003-04-08 15:47:18.0 |
| 48. | naaktfotos jolie big brother | 1 | 2003-04-08 20:22:11.0 |
| 49. | olsen twins fucking | 1 | 2003-04-08 17:44:11.0 |
| 50. | palace of king of ogoni kingdom | 1 | 2003-04-08 15:56:44.0 |
| 51. | pockey datastor .nl | 1 | 2003-04-08 18:22:06.0 |
| 52. | romeinse+katapult | 1 | 2003-04-08 15:53:20.0 |
| 53. | spekpater begraven | 1 | 2003-04-08 18:24:37.0 |
| 54. | trouw laurent en claire datum | 1 | 2003-04-08 20:29:41.0 |
| 55. | veronique de cock borsten | 1 | 2003-04-08 17:21:43.0 |
| 56. | voetreflexologie gent | 1 | 2003-04-08 21:01:21.0 |
| 57. | what is zog? | 1 | 2003-04-08 19:21:40.0 |
| 58. | yazidis | 1 | 2003-04-08 18:27:23.0 |
| 59. | zoe van gastel | 1 | 2003-04-08 20:16:33.0 |
| 60. | zog | 1 | 2003-04-08 16:56:47.0 |
| 61. | zog albania | 1 | 2003-04-08 17:38:43.0 |
| 62. | zwemsters | 1 | 2003-04-08 15:37:38.0 |
Reacties
Eén reactie op “The SQL query from hell:”
[…] The SQL query from hell: — Michel Vuijlsteke's Weblog jolie naaktfotos, 2, 2003-04-08 19:30:12.0 … naaktfotos jolie big brother, 1, 2003-04-08 20:22:11.0. 49. olsen twins fucking, 1, 2003-04-08 17:44:11.0 … https://blog.zog.org/…/the_sql_query_f.html – 32k function gotoResultItem(itemNumber) { document.forms[“searchform_simple0”].fi.value = itemNumber; document.forms[“searchform_simple0”].submit(); return false; } vorige 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 volgende […]