1. SQL / Говнокод #6084

    −863

    1. 1
    2. 2
    3. 3
    WHERE (p.city_id = 1)
       AND ((COALESCE(b.banket_persons,0)=0) OR (b.banket_persons>=20 AND b.banket_persons<=30))
       AND ((COALESCE(b.banket_per_person,0)=0) OR (b.banket_per_person>=500 AND b.banket_per_person<=1000))

    упорный программер явно не подозревал о существовании "is null" и "between"

    NaCilnic, 24 Марта 2011

    Комментарии (5)
  2. SQL / Говнокод #6074

    −860

    1. 1
    2. 2
    3. 3
    4. 4
    update dv_main set dv_main.tp_id = 2 where dv_main.uid in
    (SELECT dv.uid FROM (select * from dv_main) as dv
    LEFT JOIN users u ON (u.uid = dv.uid)
    WHERE dv.tp_id = 1);

    Вот так вот саппорт одного биллинга посоветовал массово сменить тарифный план пользователям...

    NiTr0man, 23 Марта 2011

    Комментарии (3)
  3. SQL / Говнокод #6065

    −855

    1. 1
    2. 2
    $order_by="`name` ASC";
        echo mysql_result(mysql_query("SELECT CONCAT('<ul><li>',GROUP_CONCAT(CONCAT('<a href=',`parents`.`id`,'>',`parents`.`name`,'</a>', IFNULL(`children`.`a`,'')) ORDER BY $order_by SEPARATOR '</li><li>'),'</li><ul>') FROM (SELECT `id`,`name` FROM `cats` WHERE `parent_id`=0) parents LEFT JOIN (SELECT `id`, CONCAT('<ul><li>',GROUP_CONCAT(CONCAT('<a href=',`id`,'>',`name`,'</a>') ORDER BY $order_by SEPARATOR '</li><li>'),'</li></ul>') a, `parent_id` FROM cats GROUP BY `parent_id` HAVING `parent_id`<>0) children ON `parents`.`id`=`children`.`parent_id` ;",$cn),0);

    Выборка категорий и подкатегорий, рабочая ссылка
    http://fradvice.com/test.php

    thefatfox, 22 Марта 2011

    Комментарии (3)
  4. SQL / Говнокод #6000

    −170

    1. 1
    SELECT 6000 FROM GETS

    qbasic, 16 Марта 2011

    Комментарии (14)
  5. SQL / Говнокод #5979

    −851

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    select distinct bk.ID, bk.URL, bk.Date, bk.Name, bk.Description, bk.UserCreatorID from bookmarking_bookmark as bk 
    where bk.Tenant=@Tenant and bk.ID in 
    (select *  from (select b.ID  from bookmarking_bookmark as b  left join  bookmarking_bookmarktag as bt on b.ID=bt.BookmarkID left join bookmarking_tag as t  on bt.TagID=t.TagID 
    left join (select ub.BookmarkID as BookmarkID, ub.Name as UserBookmarkName,ub.Description as UserBookmarkDescription, t.Name as TagName  
    from (select * from bookmarking_userbookmark as ub where ub.UserID=@userID) as ub 
    left join bookmarking_userbookmarktag as ubt  on ub.UserBookmarkID=ubt.UserBookmarkID  
    left join bookmarking_tag as t  on ubt.TagID=t.TagID) as ub on b.ID=ub.BookmarkID 
    where  ( ( lower(b.URL) like lower(@tagNameForUrlWithoutLastSlash0) )  
    or (lower(b.Name) like lower(@tagName0) or lower(b.Name) like lower(@tagNameWithSpace0)) 
    or (lower(b.Description) like lower(@tagName0) or lower(b.Description) like lower(@tagNameWithSpace0))    
    or (lower(t.Name) like lower(@tagName0)) or (lower(t.Name)  like lower(@tagNameWithSpace0)) 
    or (lower(TagName) like lower(@tagName0)) or (lower(TagName) like lower(@tagNameWithSpace0))    
    or (lower(UserBookmarkName) like lower(@tagName0) or lower(UserBookmarkName) like lower(@tagNameWithSpace0))    
    or (lower(UserBookmarkDescription) like lower(@tagName0) or lower(UserBookmarkDescription) like lower(@tagNameWithSpace0)))  
    and b.ID in (
    select b.ID   from bookmarking_bookmark as b  left join  bookmarking_bookmarktag as bt  on b.ID=bt.BookmarkID
    left join bookmarking_tag as t    on bt.TagID=t.TagID 
    left join  (
    select ub.BookmarkID as BookmarkID, ub.Name as UserBookmarkName, ub.Description as UserBookmarkDescription, t.Name as TagName from 
    (select * from bookmarking_userbookmark as ub where ub.UserID=@userID) as ub  
    left join bookmarking_userbookmarktag as ubt on ub.UserBookmarkID=ubt.UserBookmarkID
    left join bookmarking_tag as t on ubt.TagID=t.TagID) as ub on b.ID=ub.BookmarkID
    where  (( lower(b.URL) like lower(@tagNameForUrlWithoutLastSlash1) ) or (lower(b.Name) like lower(@tagName1) or lower(b.Name) like lower(@tagNameWithSpace1)) 
    or (lower(b.Description) like lower(@tagName1) or lower(b.Description) like lower(@tagNameWithSpace1)) or (lower(t.Name) like lower(@tagName1)) 
    or (lower(t.Name)  like lower(@tagNameWithSpace1)) or (lower(TagName) like lower(@tagName1)) or (lower(TagName) like lower(@tagNameWithSpace1))  
    or (lower(UserBookmarkName) like lower(@tagName1) or lower(UserBookmarkName) like lower(@tagNameWithSpace1)) or (lower(UserBookmarkDescription)
    like lower(@tagName1) or lower(UserBookmarkDescription) like lower(@tagNameWithSpace1))) and b.ID in (
    select b.ID from bookmarking_bookmark as b left join bookmarking_bookmarktag as bt on b.ID=bt.BookmarkID left join bookmarking_tag as t on bt.TagID=t.TagID left join
    (select ub.BookmarkID as BookmarkID, ub.Name as UserBookmarkName,  ub.Description as UserBookmarkDescription, t.Name as TagName 
    from (select * from bookmarking_userbookmark as ub where ub.UserID=@userID) as ub 
    left join bookmarking_userbookmarktag as ubt on ub.UserBookmarkID=ubt.UserBookmarkID  left join bookmarking_tag as t on ubt.TagID=t.TagID) as ub on b.ID=ub.BookmarkID
    where (( lower(b.URL) like lower(@tagNameForUrlWithoutLastSlash2) ) or (lower(b.Name) like lower(@tagName2) or lower(b.Name) like lower(@tagNameWithSpace2))
    or (lower(b.Description) like lower(@tagName2) or lower(b.Description) like lower(@tagNameWithSpace2)) or (lower(t.Name) like lower(@tagName2)) or (lower(t.Name)
    like lower(@tagNameWithSpace2)) or....

    среднее время выполнения 30 секунд

    arusanov, 14 Марта 2011

    Комментарии (115)
  6. SQL / Говнокод #5978

    −864

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    SELECT foo.oper_id,
    	                     foo.client_id,
    			     base_clients.name,
    			     base_clients.options,
    			     base_providers.name,
    	  		     base_subproviders.name,
    			     base_clients.address_post,
    			     base_clients.balance,
    			     base_currency_types.descr,
    			     base_clients.descr,
    			     base_clients.client_type,
    			     base_clients.pdn_permission_taken,
    			     base_clients.person_use_srv_as_org,
    	             base_clients.none_client
    		      FROM   (SELECT oper_id,client_id FROM base_clients WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )   AND ( base_clients.client_id = '681843' OR base_clients.personal_id = '681843' OR base_clients.name ~ '[66][88][11][88][44][33]' OR base_clients.address_post ILIKE  '%681843%'OR base_clients.descr ILIKE '%681843%' )  UNION SELECT DISTINCT oper_id,client_id FROM telephone_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM channel_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM dry_pair_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM inet_dynamic_ip_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   
    				UNION 
    				    SELECT DISTINCT sss.oper_id, sss.client_id 
    					FROM inet_fixed_ip_services as sss
    					    left join inet_fixed_ip_group_items as itms on (sss.oper_id = itms.oper_id and sss.user_id = itms.ip_group )
    				    WHERE 
    					 (  sss.oper_id = 'TT'  OR sss.oper_id = 'SC'  OR sss.oper_id = 'TT2'  OR sss.oper_id = 'RC'  )  AND 
    					(  
    					    sss.user_id ILIKE '%681843%'  
    						OR
    					    itms.ip ILIKE '%681843%'  
    					)
    			     UNION SELECT DISTINCT oper_id,client_id FROM unix_shell_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM voip_ip_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM voip_card_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM ani_phone_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'   UNION SELECT DISTINCT oper_id,client_id FROM cable_tv_services WHERE  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  AND user_id ILIKE '%681843%'  UNION select distinct oper_id,client_id from base_client_aon_list where caller_phone = '681843' AND  (  oper_id = 'TT'  OR oper_id = 'SC'  OR oper_id = 'TT2'  OR oper_id = 'RC'  )  ) AS foo 
    		             JOIN 
    			     base_clients ON foo.oper_id=base_clients.oper_id AND 
    			                     foo.client_id=base_clients.client_id 
    			     JOIN 
    			     base_providers ON base_clients.oper_id=base_providers.oper_id 
    			     JOIN 
    			     base_currency_types ON base_clients.oper_id=base_currency_types.oper_id AND 
    			                            base_clients.currency_type=base_currency_types.currency_type
    	                     LEFT JOIN
    			     base_subproviders ON base_clients.oper_id=base_subproviders.oper_id AND
    	                                          base_clients.suboper_id=base_subproviders.suboper_id 
    	              ORDER BY to_number(base_clients.client_id,'999999999') DESC

    Это запрос на простой поиск клиента в биллинге.

    spaceoflabview, 14 Марта 2011

    Комментарии (3)
  7. SQL / Говнокод #5944

    −862

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    select
    PTS.*,
    (select client_type_id from clients where client_id = PTS.client_id) as client_type_id,
    (select name ||''|| address from ats_list
    where id = PTS.ats_id) as ats_name,
    
    (select name ||''|| ip_address from dslam_list
    where id = PTS.dslam_id) as dslam_name,
    
    (select label from point_statuses_view
    where code = PTS.statuscross) as statuscross,
    
    (select typename from asrtypes
    where typename_value = '1' and typename_id = PTS.country_id) as country_name
    
    from points as PTS
    where
    PTS.point_id = 118749

    Ни про LEFT JOIN ни про ORM похоже автор не слышал.

    nyk0r, 10 Марта 2011

    Комментарии (0)
  8. SQL / Говнокод #5930

    −860

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    SELECT i.id AS i__id, i.title AS i__title, i.description AS i__description, i.meta_title AS i__meta_title, i.meta_keywords AS i__meta_keywords, i.meta_description AS i__meta_description, i.lang AS i__lang FROM item_translation i WHERE (i.id IN ('8845'))
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Акция' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Акция' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Розница' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Оптом' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Безнал' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Дилер' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'Кредит' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT p.id AS p__id, p.item_id AS p__item_id, p.type AS p__type, p.currency AS p__currency, p.value AS p__value, p.date AS p__date FROM price p WHERE (p.item_id = '8845' AND p.type = 'A') ORDER BY p.date DESC LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'В корзину' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection
    #
    
    SELECT t.id AS t__id, t.source AS t__source, t.target AS t__target, t.lang AS t__lang FROM translation t WHERE (t.source = 'В корзину' AND t.lang = 'ru') LIMIT 1
    0.00s, "doctrine" connection

    Это часть запросов которые выполняются для генерации главной страницы для одного интернет магазина. И таких запросов: 360 :-)

    aleksey, 09 Марта 2011

    Комментарии (2)
  9. SQL / Говнокод #5849

    −863

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    ...........
    UNION ALL
    (SELECT CONCAT("Помещене: ", rm_pomeshenie.nazvanie, ". Этаж: ", rm_etaj.nomer, ". Комната: ", rm_komnata.nazvanie, ". Раб. место: ", rm_rab_mesto.nazvanie, ". Сист. блок: ", sb_tipi_sist_blokov.nazvanie) AS raspolojenie, u_tip_oborudovaniya.id AS tip_id, u_tip_oborudovaniya.nazvanie AS tip_nazvanie, u_modeli_oborudovaniya.id AS model_id, CONCAT(u_modeli_oborudovaniya.nazvanie, " ", u_modeli_oborudovaniya.parametri) AS model_nazvanie, u_ustroystva.id AS ustr_id, u_ustroystva.seriyniy_nomer AS ustr_seriya, u_ustroystva.dop_svedeniya AS ustr_dop_svedeniya FROM rm_pomeshenie, rm_etaj, rm_komnata, rm_rab_mesto, sb_tipi_sist_blokov, sb_sistemnie_bloki, u_tip_oborudovaniya, u_modeli_oborudovaniya, u_ustroystva WHERE (rm_pomeshenie.id = rm_etaj.id_pomesheniya) AND (rm_etaj.id = rm_komnata.id_etaja) AND (rm_komnata.id = rm_rab_mesto.id_komnati) AND (sb_sistemnie_bloki.id_v_tablice_naznacheniya = rm_rab_mesto.id) AND (sb_sistemnie_bloki.id_tipa = sb_tipi_sist_blokov.id) AND (sb_sistemnie_bloki.imya_tablici_naznacheniya="rm_rab_mesto") AND (u_ustroystva.id_v_tablice_naznacheniya = sb_sistemnie_bloki.id) AND (u_tip_oborudovaniya.id = u_modeli_oborudovaniya.id_tipa_oborudovaniya) AND (u_modeli_oborudovaniya.id = u_ustroystva.id_modeli) AND (u_ustroystva.imya_tablici_naznacheniya = "sb_sistemnie_bloki") ORDER BY tip_nazvanie, model_nazvanie)
    UNION ALL
    ...........

    Выборка устройств. Таких блоков шесть штук и все "склеены" через UNION ALL.
    Наговнокодил от души ))

    nikitasnv, 02 Марта 2011

    Комментарии (6)
  10. SQL / Говнокод #5833

    −183

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    CREATE TABLE `log_event` (
      `id` bigint(20) NOT NULL auto_increment,
      `logtime` datetime default NULL,
      `etype` text,
      `module` text,
      `edata` text,
      `session` text,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `id` (`id`),
      KEY `id_2` (`id`)
    )

    чтобы наверняка

    elw00d, 01 Марта 2011

    Комментарии (6)