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

    +2

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 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)
  2. SQL / Говнокод #25628

    −2

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 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)
  3. SQL / Говнокод #25627

    −1

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 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)
  4. SQL / Говнокод #25626

    −1

    1. 1
    2. 2
    3. 3
    4. 4
    5. 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)
  5. SQL / Говнокод #25625

    0

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 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)
  6. SQL / Говнокод #25624

    −2

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 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)
  7. SQL / Говнокод #25504

    −101

    1. 1
    SELECT AVG(len) from huis

    bormandyan, 03 Апреля 2019

    Комментарии (21)
  8. SQL / Говнокод #25503

    −102

    1. 1
    SELECT MIN(len) from huis

    bormandyan, 03 Апреля 2019

    Комментарии (5)
  9. SQL / Говнокод #25502

    −102

    1. 1
    SELECT MAX(len) from huis

    bormandyan, 03 Апреля 2019

    Комментарии (3)
  10. SQL / Говнокод #25360

    +2

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    8. 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)