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

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

0

1

https://ibb.co/rpFfBMM

Просто код не поместится(

silverreve23, 09 Сентября 2020

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

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

+1

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 
    `1000, 1000000 field`
    (SELECT group_concat(band_category_languages.name SEPARATOR ', ')
    FROM calendar_entry_categories
    LEFT JOIN band_category_languages ON band_category_languages.band_category_id = calendar_entry_categories.band_category_id
    WHERE calendar_entry_categories.calendar_entry_id = calendar_entries.id
    AND band_category_languages.language_id = 1
    GROUP BY calendar_entry_categories.calendar_entry_id) AS band_category_string,
    (SELECT entry.id
    FROM calendar_entries AS entry
    WHERE entry.entry_kind_parent_id = calendar_entries.id
    AND entry.profile_type_id = 10869
    AND entry.profile_type = 1) AS move_to_personal_calendar,
    (SELECT count(moved_entry.id)
    FROM calendar_entries AS moved_entry
    WHERE moved_entry.entry_kind_parent_id = calendar_entries.id
    AND moved_entry.status = 3) AS count_moved,
    (SELECT user_images.image
    FROM calendar_entries AS calendar_entries1
    LEFT JOIN user_images ON user_images.id = calendar_entries1.image
    WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
    AND calendar_entries.entry_kind = 4) AS festival_image,
    (SELECT countries1.code
    FROM calendar_entries AS calendar_entries1
    LEFT JOIN locations AS locations1 ON locations1.id = calendar_entries1.location_id
    LEFT JOIN countries AS countries1 ON countries1.id = locations1.country_id
    WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
    AND calendar_entries.entry_kind = 4) AS festival_country_code,
    (SELECT count(*)
    FROM calendar_entry_likes
    WHERE calendar_entry_likes.calendar_entry_id = calendar_entries.id) AS count_of_likes,
    (SELECT count(*)
    FROM calendar_entry_likes
    WHERE calendar_entry_likes.calendar_entry_id = calendar_entries.id
    AND calendar_entry_likes.user_id = 10869 ) AS is_like,
    (SELECT group_concat(entry_type_languages.name SEPARATOR ', ')
    FROM calendar_entry_types
    LEFT JOIN entry_type_languages ON entry_type_languages.entry_type_id = calendar_entry_types.entry_type_id
    WHERE (calendar_entry_types.calendar_entry_id = calendar_entries.id
    OR calendar_entry_types.calendar_entry_id = calendar_entries.entry_kind_parent_id)
    AND entry_type_languages.language_id = 1
    GROUP BY calendar_entry_types.calendar_entry_id) AS entry_types_string,
    (SELECT band_addresses.city_name
    FROM band_addresses
    WHERE band_addresses.band_id = calendar_entries.profile_type_id) AS band_city_name,
    (SELECT countries.code
    FROM band_addresses
    LEFT JOIN countries ON countries.id = band_addresses.country_id
    WHERE band_addresses.band_id = calendar_entries.profile_type_id) AS band_country_code,
    (SELECT group_concat(genre_languages.name SEPARATOR ', ')
    FROM calendar_entry_genres
    LEFT JOIN genre_languages ON genre_languages.genre_id = calendar_entry_genres.genre_id
    WHERE calendar_entry_genres.calendar_entry_id = calendar_entries.id
    AND genre_languages.language_id = 1
    GROUP BY calendar_entry_genres.calendar_entry_id) AS genre_string
FROM `calendar_entries`
LEFT JOIN `locations` ON `locations`.`id` = `calendar_entries`.`location_id`
LEFT JOIN `calendar_entries` AS `festival` ON `festival`.`id` = `calendar_entries`.`entry_kind_parent_id`
LEFT JOIN `locations` AS `festival_location` ON `festival_location`.`id` = ( 
    SELECT calendar_entries1.id
    FROM calendar_entries AS calendar_entries1
    WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
    AND calendar_entries.entry_kind = 4
)
LEFT JOIN `countries` ON `countries`.`id` = `locations`.`country_id`
LEFT JOIN `country_languages` ON `country_languages`.`country_id` = `locations`.`country_id`
LEFT JOIN `states` ON `states`.`id` = `locations`.`state_id`
LEFT JOIN `bands` ON `bands`.`id` = `calendar_entries`.`profile_type_id`
AND `bands`.`soft_delete` = 0
AND `bands`.`status` = 1
LEFT JOIN `band_images` ON `band_images`.`id` = `calendar_entries`.`image`
LEFT JOIN `band_privacy_settings` ON `band_privacy_settings`.`band_id` = `calendar_entries`.`profile_type_id`
WHERE (`bands`.`soft_delete` = 0 OR `bands`.`soft_delete` IS NULL)
AND (`bands`.`status` = 1 OR `bands`.`status` IS NULL)
AND ((
        (SELECT count(*)
        FROM band_administrators
        WHERE band_administrators.band_id = bands.id
        AND band_administrators.user_id = 10869 ) > ? OR `band_privacy_settings`.`band_privacy_level` != ?
    ) AND (
        (SELECT user_type
        FROM users
        WHERE users.id = 10869 ) = ? OR `band_privacy_settings`.`band_privacy_level` != ?
    ) AND (
        (SELECT count(*)
        FROM user_bands
        WHERE user_bands.band_id = bands.id
        AND user_bands.status = 1
        AND user_bands.user_id = 10869 ) > ? OR `band_privacy_settings`.`band_privacy_level` != ?
    ) OR `band_privacy_settings`.`band_privacy_level` IS NULL
) AND (`festival`.`entry_kind` = ? OR `festival`.`id` IS NULL)
AND (date(`calendar_entries`.`date_from`) >= ?)
AND `calendar_entries`.`soft_delete` = ?
AND (`country_languages`.`language_id` = ? OR `country_languages`.`language_id` IS NULL)
AND `calendar_entries`.`entry_kind` IN (?, ?)
AND `calendar_entries`.`event_class` IN (?, ?)
AND `calendar_entries`.`status` = ?
GROUP BY `1000, 1000000 field`
ORDER BY calendar_entries.date_from
LIMIT 1000, 20

Сидить замовник такий не тужить тай думку гадає, а що це я буду платити півтори куска спеціалусту за роботу, як є "профі" за 500 доларів, а тут ше і Вася програміст каже, а шо це ти будеш платити таку суму, як я зроблю дешевше за 300! Настрій хороший, угода укладена, робота кипить. Проходить не так багато часу, як замовник каже, а що це сторінка 2хв грузиться, а Вася каже, то нічо ми то все оптимізуємо! Проходить ще декілька місяців, а результату нема. Тоді замовнек розуміє, що Вася не профі і шукає профі в Х рази дорощого! Не так багато часу пройшло, як профі за 1000 каже, я в два рази оптимізував, більше не реально! Замовник пригнічений, шукає профі, і в один прекрасний момент знаходиться дійсно профі, який по домовленості в зарплату X оптимізував, повикидував, переписав, дописав і в результаті 50 мс при 500000 записів. Підсумок: 300 + 1000 + X != 300 :)

silverreve23, 12 Августа 2020

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

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

+1

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

SET @from = 11836;
SET @to = 11840;

INSERT INTO `sprinter_catalog_tree` (
    `sprinter_catalog_tree`.`level`,
    `sprinter_catalog_tree`.`position`,
    `sprinter_catalog_tree`.`parent_id`,
    `sprinter_catalog_tree`.`catalog`,
    `sprinter_catalog_tree`.`info_id`,
--  ...
)
SELECT
    `sprinter_catalog_tree`.`level`,
    `sprinter_catalog_tree`.`position`,
    @to,
    `sprinter_catalog_tree`.`catalog`,
    `sprinter_catalog_tree`.`info_id`,
    `sprinter_catalog_tree`.`format_id`,
    `sprinter_catalog_tree`.`linked_id`,
    `sprinter_catalog_tree`.`linked_shablon_id`,
--  ...
FROM `sprinter_catalog_tree` where parent_id = @from order by id;

INSERT INTO `sprinter_catalog_tree` (
    `sprinter_catalog_tree`.`level`,
    `sprinter_catalog_tree`.`position`,
    `sprinter_catalog_tree`.`parent_id`,
    `sprinter_catalog_tree`.`catalog`,
    `sprinter_catalog_tree`.`info_id`,
--  ...
)
SELECT
    a.`level`,
    a.`position`,
    (SELECT id from sprinter_catalog_tree as b where b.parent_id = @to and b.name like (SELECT name from sprinter_catalog_tree where id = a.parent_id)),
    a.`catalog`,
    a.`info_id`,
    a.`format_id`,
    a.`linked_id`,
    a.`linked_shablon_id`,
--  ...
FROM `sprinter_catalog_tree` as a where parent_id in (SELECT id FROM sprinter_catalog_tree where parent_id = @from) order by id;

INSERT INTO `sprinter_catalog_info`
(`name`,
`eng_name`,
`text`,
--  ..
)
SELECT
    `sprinter_catalog_info`.`name`,
    `sprinter_catalog_info`.`eng_name`,
    `sprinter_catalog_info`.`text`,
    `sprinter_catalog_info`.`short_text`,
--  ...
FROM `sprinter_catalog_info` where id in (SELECT info_id from `sprinter_catalog_tree` where parent_id = @from or parent_id in (SELECT id from `sprinter_catalog_tree` where parent_id = @from));

CREATE temporary table if not exists ids
SELECT id from `sprinter_catalog_tree` where parent_id = @to or parent_id in (SELECT id from `sprinter_catalog_tree` where parent_id = @to);

UPDATE sprinter_catalog_tree as a SET info_id = (SELECT id from sprinter_catalog_info as b where a.name like b.name order by id desc limit 1) where a.id in (SELECT id from ids);

DROP table ids;

Высрал вот такое говно в качестве write-n-throw скрипта.

Дано: есть элементы дерева, хранящиеся в таблице sprinter_catalog_tree, связаны друг с другом через parent_id. Каждому из них соответствует указанный в info_id элемент таблицы sprinter_catalog_info.

Задача: скопировать все вложенные в раздел @from каталога элементы и его подразделы (вложенность не более 1 уровня) в раздел @to, так чтобы у них были новые id, и также скопировать соответствующие им sprinter_catalog_info. Скопированные sprinter_catalog_tree должны указывать на корректные sprinter_catalog_info, id которых заранее не известны.

Вот такое говно получилось, расскажите как надо было?

vistefan, 05 Февраля 2020

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

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

+2

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 SQL_NO_CACHE IT.itemCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.ean),",", 1) as ean,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.artCode),",", 1) as artCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.description),",", 1) as description,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.siteName),",", 1) as siteName,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.familyName),",", 1) as familyName,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.familyCode),",", 1) as familyCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyCode),",", 1) as SSfamilyCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyName),",", 1) as SSfamilyName,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorCode),",", 1) as sectorCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorName),",", 1) as sectorName,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusCode),",", 1) as radiusCode,
                                SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusName),",", 1) as radiusName,
                                FI.areaName,
                                FI.areaCode,
                                ROUND(SUM(IT.price), 2) as price,
                                SUM(IT.quantity) as theoreticalQty,
                                IFNULL(FI.countedQty, 0) as countedQty,
                                ROUND(SUM(IT.quantity) * ROUND(SUM(IT.price), 2), 2) as theoreticalQtyValue,
                                ROUND(IFNULL(FI.countedQty, 0) * ROUND(SUM(IT.price), 2), 2) as countedQtyValue,
                                IFNULL(FI.countedQty, 0) - SUM(IT.quantity) as stockGapQty,
                                ROUND((IFNULL(FI.countedQty, 0) - SUM(IT.quantity)) * SUM(IT.price), 2) as stockGapValue,
                                DATE_FORMAT(ST.lastImport, "%d-%m-%Y %H:%i:%s") as dateOfLastImport,
                                DATE_FORMAT(NOW(), "%d-%m-%Y %H:%i:%s") as dateOfExport
                        FROM Items as IT
                        LEFT JOIN ( 
                            SELECT MIN(FI.ean) as ean, SUM(FI.quantity) as countedQty, IT.itemCode,
                            GROUP_CONCAT(DISTINCT AR.name) as areaName,
                            GROUP_CONCAT(DISTINCT AR.code) as areaCode
                            FROM FoundItems as FI
                            INNER JOIN Items as IT ON FI.ean = IT.ean
                            LEFT JOIN Areas as AR ON AR.id = FI.areaId
                            
                            INNER JOIN Stores as ST ON ST.id = IT.storeId
                            
                            WHERE eventId = ${args.eventId}
                            AND IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
                            GROUP BY IT.itemCode
                        ) as FI ON IT.itemCode = FI.itemCode
                        INNER JOIN Stores as ST ON ST.id = IT.storeId
                        WHERE IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
                        GROUP BY IT.itemCode, FI.areaName, FI.areaCode, FI.countedQty, dateOfLastImport

а що ця конструкція вміє, а ета що, про та ще й ето потрібно впихнути сюди

silverreve23, 09 Января 2020

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

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

−2

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

SELECT country, QTY, MIN(launched)
FROM (SELECT country, launched, COUNT(name) QTY
 FROM Classes c JOIN 
 Ships s ON c.class = s.class
 GROUP BY country,launched
 HAVING COUNT(name) = (SELECT MAX(qty) 
 FROM (SELECT country,launched,COUNT(name) qty
 FROM Classes c1 JOIN 
 Ships s1 ON c1.class = s1.class
 WHERE country = c.country
 GROUP BY country,launched
 )e
 )
 )T
GROUP BY t.qty, t.country;

-2 sql ex rating
я так понял необходимо ещё как-то добавить проверку на NULL?

5252mmr, 20 Мая 2019

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

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

−1

01
02
03
04
05
06
07
08
09
10
11

select Outcomes.ship,count(*)
from Outcomes
where outcomes.result!='sunk'
group by ship
HAVING count(*)>=2
union
select ships.name,count(*)
from ships
where ships.launched!=NULL
group by name
HAVING count(*)>=2

-9 sql ex...

неверно на тренировочной

коммент :
парсить имя на '%NAME%' ? или как ?

5252mmr, 20 Мая 2019

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

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

−1

1
2
3
4
5

select distinct pass_in_trip.trip_no, (pass_in_trip.trip_no/5+pass_in_trip.trip_no/25+pass_in_trip.trip_no/125+pass_in_trip.trip_no/625+pass_in_trip.trip_no/3125+pass_in_trip.trip_no/15625+pass_in_trip.trip_no/78125)
from pass_in_trip
union
select distinct trip.trip_no, (trip.trip_no/5+trip.trip_no/25+trip.trip_no/125+trip.trip_no/625+trip.trip_no/3125+trip.trip_no/15625+trip.trip_no/78125)
from Trip

-12 рейтинговое sql ex... на тренировочной что-то не так...
наверно что-то связанное с NULL ?
добавить проверку ?

5252mmr, 20 Мая 2019

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

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

0

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

select model,(CAST(substring(model, 1, 1) as int)+CAST(substring(model, 2, 1) as int)+CAST(substring(model, 3, 1) as int)+CAST(substring(model, 4, 1) as int)) as qty
from product
where  
(substring(model, 1, 1) like '0' or substring(model, 1, 1) like '1' or substring(model, 1, 1) like '2' 
or substring(model, 1, 1) like '3' or substring(model, 1, 1) like '4' or substring(model, 1, 1) like '5' 
or substring(model, 1, 1) like '6' or substring(model, 1, 1) like '7' or substring(model, 1, 1) like '8' 
or substring(model, 1, 1) like '9')
 AND (substring(model, 2, 1) like '0' or substring(model, 2, 1) like '1' or substring(model, 2, 1) like '2' 
 or substring(model, 2, 1) like '3' or substring(model, 2, 1) like '4' or substring(model, 2, 1) like '5' 
 or substring(model, 2, 1) like '6' or substring(model, 2, 1) like '7' or substring(model, 2, 1) like '8' 
 or substring(model, 2, 1) like '9') 
 AND (substring(model, 3, 1) like '0' or substring(model, 3, 1) like '1' or substring(model, 3, 1) like '2' 
or substring(model, 3, 1) like '3' or substring(model, 3, 1) like '4' or substring(model, 3, 1) like '5'
 or substring(model, 3, 1) like '6' or substring(model, 3, 1) like '7' or substring(model, 3, 1) like '8' 
 or substring(model, 3, 1) like '9') 
 AND (substring(model, 4, 1) like '0' or substring(model, 4, 1) like '1' or substring(model, 4, 1) like '2'
 or substring(model, 4, 1) like '3' or substring(model, 4, 1) like '4' or substring(model, 4, 1) like '5' 
 or substring(model, 4, 1) like '6' or substring(model, 4, 1) like '7' or substring(model, 4, 1) like '8' 
 or substring(model, 4, 1) like '9')



select model, 
ASCII(SUBSTRING(model,1,1))+ 
ASCII(SUBSTRING(model,2,1))+ 
ASCII(SUBSTRING(model,3,1))+ 
ASCII(SUBSTRING(model,4,1)) 
-48*4 as qty from Product
тоже не помогло.

sql ex рейтинговое 4. показывает что на тренировочной что-то не правильно.
лайк же вроде проверяет '1' как цифру а не как строку.
любые хинты как тут

5252mmr, 20 Мая 2019

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

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

−2

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22

select trip_no,concat(CAST(trip_no &1024 aS bit),CAST(trip_no &512 aS bit),CAST(trip_no &256 aS bit), CAST(trip_no &128 aS bit),CAST(trip_no &64 aS bit),
cast(trip_no & 32 as bit)
 , cast(trip_no & 16 as bit) 
    , cast(trip_no & 8 as bit) 
    , cast(trip_no & 4 as bit) 
    , cast(trip_no & 2 as bit)  
    ,cast(trip_no & 1 as bit)) as trip_no_bit
from pass_in_trip
where trip_no<=2048 and trip_no>=1024

union 

select trip_no,concat(CAST(trip_no &1024 aS bit),CAST(trip_no &512 aS bit),CAST(trip_no &256 aS bit), CAST(trip_no &128 aS bit),CAST(trip_no &64 aS bit),
cast(trip_no & 32 as bit)
 , cast(trip_no & 16 as bit) 
    , cast(trip_no & 8 as bit)
    , cast(trip_no & 4 as bit) 
    , cast(trip_no & 2 as bit)  
    ,cast(trip_no & 1 as bit))  as trip_no_bit
from trip
where trip_no<=2048 and trip_no>=1024
union

В конкате первый аргумент concat(CAST(trip_no &1024 aS bit),
&1024 имеется ввиду

а внизу проверка where trip_no<=2048 and trip_no>=1024 - правильно ли ? или where trip_no<=1024and trip_no>=512 должно быть ?

и есть ли более профитный вариант решения рейтинговой задачи sqlex`a ? а то какой-то говнокод получается около 11к строк а там лимит 8к...
или лучше заменить на %2 и /2)%2 , /2)/2)%2 ????

5252mmr, 20 Мая 2019

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

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

−101

1

SELECT AVG(len) from huis

bormandyan, 03 Апреля 2019

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

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

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

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

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

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

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

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

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

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

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

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