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

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

−853.2

1

какой нафиг код((((

больше года пишут и поддерживают проект
а как оказалось, в бд на 36 таблиц нет ни одного фореинг кея!
уебаны ((((((((((

guest, 05 Августа 2009

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

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

−844

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100

SELECT
					'X' `TYPEOF`
					,161 AS `ID`
					,pa.ARTICUL
					,p.PRODUCT_NAME
					,p.PRODUCT_INSTRUCTION
					,p.PRODUCT_WEIGHT
					,p.PRODUCT_VOLUME
					,p.PRODUCT_RATING
					,pm.MEASURE
					,b.BRAND_NAME
					,cs.COLL_NAME COLLECTION
					,m.MAN_NAME MANUFACTORER
					,pst.QUANTITY
					,p.TIMESTAMP_X AS LAST_MODIFY
					
					,pts.PRODUCT_NOTE
					,pts.PRODUCT_DESCRIPTION
					,pts.PRODUCT_IMAGE
					,pts.PRODUCT_GALLERY
					,pts.PRODUCT_ACTIVE
					,pts.PRODUCT_ACTIVE_FROM
					,pts.PRODUCT_ACTIVE_TO
					,pts.PRODUCT_NEW
					,pts.PRODUCT_LEADER
					,pts.USER_ID
					,pts.SITE_ID
					,'X' CATEGORIES
					,'X' FEATURES
					,'X' RANGE_APPLICATION
					,'X' RANGE_PRESCRIPTION
					,'X' PRODUCT_PRICE
					,'X' PRICE_CURRENCY
					,'X' PRODUCT_PRICE_GROUP
					,'X' IMAGES
				FROM  `e_products` `p` 
					LEFT JOIN `e_product_to_site` `pts` ON ((`pts`.`PRODUCT_ID`=161))
					LEFT JOIN `e_product_articuls` `pa` ON ((`pa`.`PRODUCT_ID`=161)) 
					LEFT JOIN `e_product_stock` `pst` ON ((`pst`.`PRODUCT_ID`=161)) 
					LEFT JOIN `e_product_to_brand` `ptb` ON ((`ptb`.`BRAND_ID`=161)) 
					LEFT JOIN `e_product_to_collection` `pcl` ON ((`pcl`.`PRODUCT_ID` = 161)) 
					LEFT JOIN `e_product_measure` `pm` ON ((`p`.`PRODUCT_MEASURE` = `pm`.`ID`)) 
					LEFT JOIN `e_brands` `b` ON ((`ptb`.`BRAND_ID`=`b`.`ID`)) 
					LEFT JOIN `e_manufactorers` `m` ON ((`b`.`MAN_ID`=`m`.`ID`)) 
					LEFT JOIN `e_collections` `cs` ON ((`cs`.`ID`=`pcl`.`COLLECTION_ID`)) 
					LEFT JOIN `e_product_prices` `pp` ON ((`pp`.`PRODUCT_ID`=`p`.`ID`))

			WHERE `p`.`ID` IN (161) AND `pts`.`SITE_ID` LIKE 's0' 
				
				UNION 
				SELECT 'categories',`p`.`ID` AS `ID`
					,'X','X','X','X','X','X','X','X','X','X','X','X'
					,'X','X','X','X','X','X','X','X','X','X','X'
				    ,`ptc`.`CATEGORY_ID`
				    ,'X','X','X','X','X','X','X'
				FROM `e_product_to_category` `ptc` 
					LEFT JOIN `e_products` `p` ON ((`p`.`ID`=`ptc`.`PRODUCT_ID`))
					LEFT JOIN `e_product_categories` `pc` ON ((`pc`.`ID`=`ptc`.`CATEGORY_ID`))
					LEFT JOIN `e_catalogs` `c` ON ((`c`.`ID`=`pc`.`CATALOG_ID`)) 
					WHERE `ptc`.`PRODUCT_ID` IN (161)
					 AND `c`.`SITE_ID` LIKE 's0' 
				
				UNION 
				SELECT 'features',`p`.`ID` AS `ID`
					,'X','X','X','X','X','X','X','X','X','X','X','X'
					,'X','X','X','X','X','X','X','X','X','X','X'
					,'X'
				    ,FEATURE_ID 
				    ,'X','X','X','X','X','X'
				FROM `e_product_catalog` `pc` 
					LEFT JOIN `e_products` `p` ON `p`.`ID`=`pc`.`PRODUCT_ID`
					LEFT JOIN `e_product_to_category` `ptc` ON ((`ptc`.`PRODUCT_ID`=`pc`.`PRODUCT_ID`)) 
					LEFT JOIN `e_catalogs` `c` ON ((`c`.`ID`=`ptc`.`CATEGORY_ID`)) 
				WHERE `pc`.`PRODUCT_ID` IN (161) AND `c`.`SITE_ID` LIKE 's0'  
				
				UNION 
				
				SELECT 'rangeapp',`p`.`ID` AS `ID`
					,'X','X','X','X','X','X','X','X','X','X','X','X'
					,'X','X','X','X','X','X','X','X','X','X','X'
					,'X','X'
				    ,`RANGE_APP_ID`
				    ,'X' ,'X','X','X','X'
				FROM `e_product_range_app` `pra` 
					LEFT JOIN `e_products` `p` ON `p`.`ID`=`pra`.`PRODUCT_ID`
				WHERE `PRODUCT_ID` IN (161) 
				
				UNION 
				SELECT 'rangepre',`p`.`ID` AS `ID`
					,'X','X','X','X','X','X','X','X','X','X','X','X'
					,'X','X','X','X','X','X','X','X','X','X','X'
					,'X','X','X'
				    ,`RANGE_PRE_ID`,'X','X','X','X'
				FROM `e_product_range_pre` `prp` 
					LEFT JOIN `e_products` `p` ON `p`.`ID`=`prp`.`PRODUCT_ID`
				WHERE `PRODUCT_ID` IN (161)
				
				UNION 
				SELECT 'prices',`p`.`ID` AS `ID`
					,'X','X','X','X','X','X','X','X','X','X','X','X'

guest, 28 Июля 2009

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

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

−131.5

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100

SELECT   [Worker_list].[Id]                       ,
         [Worker_list.Post_adress]                ,
         [Worker_list.Ident_fiscal]               ,
         [Worker_list.VacationDay]                ,
         [Worker_list].[Name_worker]              ,
         [Worker_list].[Name]                     ,
         [Worker_list].[Firstname]                ,
         [Worker_list].[Middlenamme]              ,
         [Worker_list].[Табельний номер]          ,
         [Worker_list].[workfrom] AS workfromtable,
         (SELECT MIN([time_list].[begin_date])
         FROM    [time_list]
         WHERE   [time_list].[worker]=[worker_list].[id]
         ) AS accountfrom,
         IIF
         (
                  ISNULL([workfromtable]),[accountfrom],[workfromtable]
         )                              AS workfrom           ,
         [Worker_list].[startstockdate] AS startstockdatetable,
         IIF
         (
                  ISNULL([startstockdatetable]),[accountfrom],[startstockdatetable]
         )                               AS startstockdate      ,
         [Worker_list].[startaccrualday] AS startaccrualdaytable,
         [Worker_list].[startusedday]    AS startuseddaytable   ,
         IIF
         (
                  ISNULL([startaccrualdaytable]),0,[startaccrualdaytable]
         ) AS Startaccrualday,
         IIF
         (
                  ISNULL([startuseddaytable]),0,[startuseddaytable]
         ) AS startusedday,
         (SELECT MIN([calendar_all].[Дата])
         FROM    calendar_all
         )                                                                       AS Todayis          ,
         SUM(DATEDIFF("d",[Chas]![Date Begin],DATEADD("d",1,[Chas]![Date end]))) AS UsedDayAfterTable,
         IIF
         (
                  ISNULL([useddayaftertable]),0,[useddayaftertable]
         ) AS UsedDayAfter,
         (SELECT SUM (DATEDIFF("d",Chas.[Date Begin], DATEADD("d",1,Chas.[Date End])))
         FROM    Worker_list AS Worker_list_1
                 INNER JOIN (Reason_list
                         INNER JOIN Chas
                         ON      Reason_list.Id = Chas.Reason)
                 ON      Worker_list_1.Id       = Chas.Worker
         WHERE   Reason_list.List               =0
             AND Worker_list.Id                 =Worker_List_1.Id
             AND chas.[date end]               <=
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
         ) AS timelossafterbeforetable,
         (SELECT SUM (DATEDIFF("d",Chas.[Date Begin], DATEADD("d",1,
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 ))))
         FROM    Worker_list AS Worker_list_1
                 INNER JOIN (Reason_list
                         INNER JOIN Chas
                         ON      Reason_list.Id = Chas.Reason)
                 ON      Worker_list_1.Id       = Chas.Worker
         WHERE   Reason_list.List               =0
             AND Worker_list.Id                 =Worker_List_1.Id
             AND chas.[date begin]              <
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
             AND chas.[date end] >
                 (SELECT MIN([calendar_all].[Дата])
                 FROM    calendar_all
                 )
         ) AS timelossaftertable,
         IIF
         (
                  ISNULL([timelossafterbeforetable]),0,[timelossafterbeforetable]
         ) AS timelossafterbefore,
         IIF
         (
                  ISNULL([timelossaftertable]),0,[timelossaftertable]
         )                                                                              AS timelossafter,
         DATEDIFF("d",[startstockdate],[todayis])-[timelossafterbefore]-[timelossafter] AS workoffafter ,
         INT([workoffafter]                      /365*
         IIF
         (
                  ISNULL([Worker_list].[VacationDay]),GetVacationDayCount(),[Worker_list].[VacationDay]
         )
         )                                                   AS accrualdayafter,
         DATEDIFF("d",[workfrom],[todayis])                  AS workoffall     ,
         [Startaccrualday]        -[startusedday]+[accrualdayafter] AS accrualdayall  ,
         [accrualdayafter]        +[startaccrualday]                AS accrualday     ,
         [startusedday]           +[useddayafter]-StateHoliDay      AS usedday        ,
         [accrualday]             -[usedday]                        AS unusedday      ,
         DATEADD("d",INT([usedday]/
         IIF
         (
                  ISNULL([Worker_list].[VacationDay]),GetVacationDayCount(),[Worker_list].[VacationDay]
         )
         *365)+[timelossafterbefore]+[timelossafter],[workfrom]) AS Usedto,

Запрос количества оставшихся отпусков в кадровой программе

guest, 14 Июля 2009

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

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

−859

1

select * from dual

guest, 13 Июля 2009

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

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

−861.8

1
2
3
4
5
6
7
8
9

select
	....
	if(n.Pinned=1,concat(n.Name,' &lt;img src=/images/icons/897634.gif>'),n.Name) as Name
from
	news n
	left outer join ....
	.....
where
	....

Как вам html-верстка в sql запросах?

guest, 23 Июня 2009

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

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

−128.7

1
2

if(mysql_query('DROP TABLE table_name'))
  echo "таблица существовала ;(";

Проверка таблицы на существование.
Взято самизнаетеоткуда

ЗЫ SQL+PHP

guest, 11 Июня 2009

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

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

−857.4

1
2
3
4
5
6
7
8

select *
from INV_CARD
where to_char (dt_reg, 'MM') < TO_CHAR(SYSDATE, 'MM')
	AND (DT_OUT IS NULL OR to_char (dt_OUT, 'MM') = TO_CHAR(SYSDATE, 'MM'))
	AND ID_FA_TYPE NOT IN (SELECT ID_FA_TYPE FROM FA_TYPE WHERE CODE = '17')
	AND ID_FA_STATUS IN (SELECT ID_FA_STATUS FROM FA.V_FA_STATUS WHERE IS_ACCOUNTING = 1)
	AND STATUS = 1
	AND GLN.ACC_OPER.GET_ACC1_ACC2(ID_AMORT_ACC) LIKE '01%'

(PL/SQL)
очень понравилось сравнение дат, ну и использование констант тоже зачет

guest, 04 Июня 2009

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

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

−852.5

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
43
44
45
46
47

--
--Ada SPEC file
--Universe
--
-- Description: 
--
--
-- 
--
-- 
--
-- 
package Universe is

type UniversalObject is tagged record
	id:integer;
end record;

type Position is record
	x:integer;
	y:integer;
	
	end record;



type VisibleObject is new UniversalObject with record
	mass: integer;
	Name:String (0..60);
	Speed: float; --скорость:)
	OrbitalPosition: Position;
	
end record;



type Ship Is new VisibleObject with record
	Size:integer;
end record;

type Asteroid is new VisibleObject WITH NULL RECORD;


procedure USELECT (Object: in UniversalObject'Class); --Новая инструкция выбора:)


end Universe;

Тут не смешно, но мне кажется, стоит оптимизировать.

И еще: Добавьте языков в список. Не все ковнокодеры - вебмастера.

guest, 03 Июня 2009

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

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

−866.4

1

"select crash.Фамилия from crash where Нарушение=(select select bill.Нарушение from bill where Штраф=(select max(Штраф) from bill));";

guest, 03 Июня 2009

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

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

−866.7

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17

FUNCTION gender_modification (string VARCHAR2, gender VARCHAR2) RETURN VARCHAR2
 IS
   str    VARCHAR2(500) := string;
 BEGIN
    IF gender = 'ж' THEN
	str := REPLACE(str,'одиннадцать','о$$ди$$ннадцать');
    str := REPLACE(str,'один','одна');
    str := REPLACE(str,'два','две');
	str := REPLACE(str,'о$$ди$$ннадцать','одиннадцать');
   END IF;
   IF gender = 'с' THEN
	str := REPLACE(str,'одиннадцать','о$$ди$$ннадцать');
     str := REPLACE(str,'один','одно');
	str := REPLACE(str,'о$$ди$$ннадцать','одиннадцать');
   END IF;
   RETURN str;
 END;

Преобразование "суммы прописью" в заданный род (женский или средний).

guest, 18 Мая 2009