SQL — Говнокод.ру

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

−863

1
2
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)

SQL / Говнокод #6074

−860

1
2
3
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)

SQL / Говнокод #6065

−855

1
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)

SQL / Говнокод #6000

−170

1

SELECT 6000 FROM GETS

qbasic, 16 Марта 2011

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

SQL / Говнокод #5979

−851

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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)

SQL / Говнокод #5978

−864

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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)

SQL / Говнокод #5944

−862

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
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)

SQL / Говнокод #5930

−860

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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)

SQL / Говнокод #5849

−863

1
2
3
4
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)

SQL / Говнокод #5833

−183

01
02
03
04
05
06
07
08
09
10
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)

Говнокод: по колено в коде.

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

SQL / Говнокод #6074

SQL / Говнокод #6065

SQL / Говнокод #6000

SQL / Говнокод #5979

SQL / Говнокод #5978

SQL / Говнокод #5944

SQL / Говнокод #5930

SQL / Говнокод #5849

SQL / Говнокод #5833