- 1
- 2
- 3
- 4
- 5
create table user.profile (
...
gender boolean,
...
)
Нашли или выдавили из себя код, который нельзя назвать нормальным, на который без улыбки не взглянешь? Не торопитесь его удалять или рефакторить, — запостите его на говнокод.ру, посмеёмся вместе!
−154.8
create table user.profile (
...
gender boolean,
...
)
Очевидно, true - это мужик :D
−863.7
Нашёл в проекте
SELECT DISTINCT sale_date, SUM(sale_price), buyer FROM ...
GROUP BY sale_date, buyer;
DISTINCT здесь лишний
−863
select ...
from ...
where ...
and NVL2(l_date_from, trunc(mmt.transaction_date), l_date_from) >= NVL(l_date_from, l_date_from)
and NVL2(l_date_to, trunc(mmt.transaction_date), l_date_to) <= NVL(l_date_to, l_date_to)
...
из реально работающей системы
−866.9
CREATE TABLE [Battles] (
[name] [varchar] (20) NOT NULL ,
[date] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Classes] (
[class] [varchar] (50) NOT NULL ,
[type] [varchar] (2) NOT NULL ,
[country] [varchar] (20) NOT NULL ,
[numGuns] [tinyint] NULL ,
[bore] [real] NULL ,
[displacement] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Ships] (
[name] [varchar] (50) NOT NULL ,
[class] [varchar] (50) NOT NULL ,
[launched] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Outcomes] (
[ship] [varchar] (50) NOT NULL ,
[battle] [varchar] (20) NOT NULL ,
[result] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Battles] ADD
CONSTRAINT [PK_Battles] PRIMARY KEY CLUSTERED
(
[name]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Classes] ADD
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED
(
[class]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ships] ADD
CONSTRAINT [PK_Ships] PRIMARY KEY CLUSTERED
(
[name]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Outcomes] ADD
CONSTRAINT [PK_Outcomes] PRIMARY KEY CLUSTERED
(
[ship],
[battle]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ships] ADD
CONSTRAINT [FK_Ships_Classes] FOREIGN KEY
(
[class]
) REFERENCES [dbo].[Classes] (
[class]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Outcomes] ADD
CONSTRAINT [FK_Outcomes_Battles] FOREIGN KEY
(
[battle]
) REFERENCES [dbo].[Battles] (
[name]
)
GO
Это с sql-ex.ru , база "корабли"
Может, упражнения там интересные,
но сама база истинный говнокод. Почему?
Строчка
"В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships."
делает её говнокодом.
−166.2
SELECT DISTINCT B.TYPE_SID, B.ID BANNER_ID, B.WEIGHT BANNER_WEIGHT,
B.SHOWS_FOR_VISITOR, B.FIX_CLICK, B.FIX_SHOW, B.KEYWORDS BANNER_KEYWORDS,
C.ID CONTRACT_ID, C.WEIGHT CONTRACT_WEIGHT, C.KEYWORDS CONTRACT_KEYWORDS
FROM b_adv_type T
INNER JOIN b_adv_banner B ON ( B.ACTIVE='Y' and B.TYPE_SID = T.SID
and B.STATUS_SID = 'PUBLISHED'
and (B.FOR_NEW_GUEST is null or B.FOR_NEW_GUEST='N')
and (ifnull(B.MAX_SHOW_COUNT,0)>ifnull(B.SHOW_COUNT,0)
or ifnull(B.MAX_SHOW_COUNT,0)=0) and (ifnull(B.MAX_CLICK_COUNT,0)>ifnull(B.CLICK_COUNT,0)
or ifnull(B.MAX_CLICK_COUNT,0)=0) and (ifnull(B.MAX_VISITOR_COUNT,0)>ifnull(B.VISITOR_COUNT,0)
or ifnull(B.MAX_VISITOR_COUNT,0)=0) and (B.DATE_SHOW_FROM<=now() or B.DATE_SHOW_
FROM is null or length(B.DATE_SHOW_FROM)<=0) and (B.DATE_SHOW_TO>=now()
or B.DATE_SHOW_TO is null or length(B.DATE_SHOW_TO)<=0))
INNER JOIN b_adv_banner_2_site BS ON ( BS.BANNER_ID = B.ID and BS.SITE_ID = 'ru')
INNER JOIN b_adv_contract C ON ( C.ID = B.CONTRACT_ID and C.ACTIVE='Y'
and (ifnull(C.MAX_SHOW_COUNT,0)>ifnull(C.SHOW_COUNT,0)
or ifnull(C.MAX_SHOW_COUNT,0)=0) and (ifnull(C.MAX_CLICK_COUNT,0)>ifnull(C.CLICK_COUNT,0)
or ifnull(C.MAX_CLICK_COUNT,0)=0) and (ifnull(C.MAX_VISITOR_COUNT,0)>ifnull(C.VISITOR_COUNT,0)
or ifnull(C.MAX_VISITOR_COUNT,0)=0) and (C.DATE_SHOW_FROM<=now() or C.DATE_SHOW_
FROM is null or length(C.DATE_SHOW_FROM)<=0) and (C.DATE_SHOW_TO>=now() or C.DATE_SHOW_TO is null
or length(C.DATE_SHOW_TO)<=0))
INNER JOIN b_adv_contract_2_site CS ON ( CS.CONTRACT_ID = B.CONTRACT_ID and CS.SITE_ID = 'ru')
INNER JOIN b_adv_contract_2_type CT ON ( CT.CONTRACT_ID = C.ID
and (CT.TYPE_SID = 'ALL' or CT.TYPE_SID = T.SID))
INNER JOIN b_adv_banner_2_weekday BW ON ( BW.BANNER_ID = B.ID
and BW.C_WEEKDAY='SUNDAY' and BW.C_HOUR = '11')
INNER JOIN b_adv_contract_2_weekday CW ON ( CW.CONTRACT_ID = C.ID
and CW.C_WEEKDAY='SUNDAY' and CW.C_HOUR = '11')
LEFT JOIN b_adv_banner_2_group UG1 ON ( (UG1.BANNER_ID = B.ID and UG1.GROUP_ID in (2,1,21)) )
LEFT JOIN b_adv_banner_2_page BP1 ON ( BP1.BANNER_ID = B.ID and BP1.SHOW_ON_PAGE='Y')
LEFT JOIN b_adv_banner_2_page BP2 ON ( BP2.BANNER_ID = B.ID and BP2.SHOW_ON_PAGE='N'
and '/e-store/books/index.php?SECTION_ID=145' like concat(BP2.PAGE, '%'))
LEFT JOIN b_adv_contract_2_page CP1 ON ( CP1.CONTRACT_ID = C.ID and CP1.SHOW_ON_PAGE='Y')
LEFT JOIN b_adv_contract_2_page CP2 ON ( CP2.CONTRACT_ID = C.ID and CP2.SHOW_ON_PAGE='N'
and '/e-store/books/index.php?SECTION_ID=145' like concat(CP2.PAGE, '%'))
LEFT JOIN b_adv_banner_2_stat_adv BA ON BA.BANNER_ID = B.ID
LEFT JOIN b_adv_banner_2_country BC ON BC.BANNER_ID = B.ID WHERE T.ACTIVE = 'Y'
and BP2.ID is null and CP2.ID is null
and (BP1.ID is null or '/e-store/books/index.php?SECTION_ID=145' like concat(BP1.PAGE, '%'))
and (CP1.ID is null or '/e-store/books/index.php?SECTION_ID=145' like concat(CP1.PAGE, '%'))
and (BA.STAT_ADV_ID is null or BA.STAT_ADV_ID='0')
and (BC.COUNTRY_ID is null or BC.COUNTRY_ID='N0')
and ( (B.SHOW_USER_GROUP = 'Y' and UG1.GROUP_ID is not null)
or (B.SHOW_USER_GROUP <> 'Y' and UG1.GROUP_ID is null))
ORDER BY B.TYPE_SID desc, C.ID desc
SQL-запрос из Битрикса
http://ekimoff.ru/165/
−867.6
SELECT SQL_CALC_FOUND_ROWS *,cached_login as login,cached_name as uname,comment_count as ccount FROM posts p WHERE exists (select * from tags tg, tegi_zapisi tx where tg.alt_title like 'post' and tg.id_tag=tx.id_tag and tx.id_post=p.id_post) and exists (select * from tags tg, tegi_zapisi tx where tg.alt_title like 'opentomsk' and tg.id_tag=tx.id_tag and tx.id_post=p.id_post) and hide<>1 ORDER BY p.date desc, p.time desc LIMIT 210,10
выбираем все посты с определёнными тегами или без них
удивляемся, почему тормозит пропорционально количеству тегов
−853.2
какой нафиг код((((
больше года пишут и поддерживают проект
а как оказалось, в бд на 36 таблиц нет ни одного фореинг кея!
уебаны ((((((((((
−844
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'
−131.5
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,
Запрос количества оставшихся отпусков в кадровой программе
−859
49512
select * from dual