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

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 / Говнокод #25503

−102

1

SELECT MIN(len) from huis

bormandyan, 03 Апреля 2019

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

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

−102

1

SELECT MAX(len) from huis

bormandyan, 03 Апреля 2019

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

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

+2

1
2
3
4
5
6
7
8

CREATE TABLE `test` (
    `id` INT(11) unsigned NOT NULL AUTO_INCREMENT,
     `parent` INT(10) unsigned NOT NULL DEFAULT '0',
      `name` VARCHAR(50),
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
INSERT INTO test (id, parent, name) values (1, 0, '10'),(2, 1, '21'),(4, 3, '43'),(5, 0, '50'),(6, 5, '65'),(7, 6, '76'),(8, 7, '87'),(9, 8, '98');
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4, t5.name as lev5, t6.name as lev6, t7.name as lev7 FROM `test` AS t1 LEFT JOIN test AS t2 ON t2.`parent` = t1.`id`  LEFT JOIN test AS t3 ON t3.`parent` = t2.`id`  LEFT JOIN test AS t4 ON t4.`parent` = t3.`id` LEFT JOIN test AS t5 ON t5.`parent` = t4.`id` LEFT JOIN test AS t6 ON t6.`parent` = t5.`id` LEFT JOIN test AS t7 ON t7.`parent` = t6.`id`;

взять последнего парента с седьмого колена!
я просто похлопаю :)

websbkinfo, 07 Февраля 2019

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

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

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

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

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

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

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

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

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

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

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

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