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

    −101

    1. 1
    SELECT AVG(len) from huis

    bormandyan, 03 Апреля 2019

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

    −102

    1. 1
    SELECT MIN(len) from huis

    bormandyan, 03 Апреля 2019

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

    −102

    1. 1
    SELECT MAX(len) from huis

    bormandyan, 03 Апреля 2019

    Комментарии (3)
  6. 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)
  7. SQL / Говнокод #24861

    −10

    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
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    product_id '14' 
     product_id '17' 
     product_id '18' 
     product_id '19' 
     product_id '20' 
     product_id '21' 
     product_id '22' 
     product_id '25' 
     product_id '28' 
     product_id '29' 
     product_id '30' 
     product_id '31' 
     product_id '32' 
     product_id '33' 
     product_id '34' 
     product_id '35' 
     product_id '36' 
     product_id '37' 
     product_id '38' 
     product_id '39' 
     product_id '40' 
     product_id '41' 
     product_id '42' 
     product_id '152' 
     product_id '166' 
     product_id '181' 
     product_id '183' 
     product_id '184' 
     product_id '187' 
     product_id '188' 
     product_id '189' 
     product_id '190' 
     product_id '191' 
     product_id '192' 
     product_id '193' 
     product_id '194' 
     product_id '195' 
     product_id '196' 
     product_id '197' 
     product_id '198' 
     product_id '200' 
     product_id '202' 
     product_id '203' 
     product_id '803' 
     product_id '805' 
     product_id '806' 
     product_id '807' 
     product_id '808' 
     product_id '809' 
     product_id '810'

    snegoviktlt, 04 Октября 2018

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

    0

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    https://ru.wikipedia.org/wiki/Хранимая_процедура#Возможности_программирования
    Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция
    (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой
    машины, после чего при вызове хранимой процедуры происходит прямое выполнение её
    скомпилированного объектного кода.

    Можно сделать вирусню, живущую в этих самых хранимых процедурах в машинном коде и инфицирующую другие БД с хранимыми процедурами

    j123123, 26 Сентября 2018

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

    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
    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
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 88
    89. 89
    90. 90
    91. 91
    92. 92
    93. 93
    94. 94
    95. 95
    96. 96
    97. 97
    SELECT *
    FROM
      (SELECT CASE
       WHEN (CASE
    		WHEN datediff(DAY,date,
    						(SELECT top (1) date
    						FROM Battles AS P2
    						WHERE P2.date > P1.date
    						ORDER BY P2.date))/365 = 0 THEN 'Y y.,'
    		ELSE cast((datediff(DAY,date,
    								(SELECT top (1) date
    								FROM Battles AS P2
    								WHERE P2.date > P1.date
    								ORDER BY P2.date))/365) AS varchar(20)) + ','
    	END + CASE
    				WHEN (datediff(DAY,date,
    								(SELECT top (1) date
    								FROM Battles AS P2
    								WHERE P2.date > P1.date
    								ORDER BY P2.date))/30 - (datediff(DAY,date,
    																	(SELECT top (1) date
    																	FROM Battles AS P2
    																	WHERE P2.date > P1.date
    																	ORDER BY P2.date))/365) * 12) = 0 THEN 'M m.'
    				ELSE cast((datediff(DAY,date,
    									(SELECT top (1) date
    									FROM Battles AS P2
    									WHERE P2.date > P1.date
    									ORDER BY P2.date))/30 - (datediff(DAY,date,
    																		(SELECT top (1) date
    																			FROM Battles AS P2
    																			WHERE P2.date > P1.date
    																			ORDER BY P2.date))/365) * 12) AS varchar(20))
    			END) = 'Y y.,M m.' THEN ''
          ELSE (CASE
            WHEN datediff(DAY,date,
                            (SELECT top (1) date
                             FROM Battles AS P2
                             WHERE P2.date > P1.date
                             ORDER BY P2.date))/365 = 0 THEN 'Y y.,'
            ELSE cast((datediff(DAY,date,
                                  (SELECT top (1) date
                                   FROM Battles AS P2
                                   WHERE P2.date > P1.date
                                   ORDER BY P2.date))/365) AS varchar(20)) + ','
        END + CASE
         WHEN (datediff(DAY,date,
                          (SELECT top (1) date
                           FROM Battles AS P2
                           WHERE P2.date > P1.date
                           ORDER BY P2.date))/30 - (datediff(DAY,date,
                                                               (SELECT top (1) date
                                                                FROM Battles AS P2
                                                                WHERE P2.date > P1.date
                                                                ORDER BY P2.date))/365) * 12) = 0 THEN 'M m.'
         ELSE cast((datediff(DAY,date,
                               (SELECT top (1) date
                                FROM Battles AS P2
                                WHERE P2.date > P1.date
                                ORDER BY P2.date))/30 - (datediff(DAY,date,
                                                                    (SELECT top (1) date
                                                                     FROM Battles AS P2
                                                                     WHERE P2.date > P1.date
                                                                     ORDER BY P2.date))/365) * 12) AS varchar(20))  END)
      END AS 'Возраст',
      date AS date1,
         (SELECT top (1) date
          FROM Battles AS P2
          WHERE P2.date > P1.date
          ORDER BY P2.date) AS date2
       FROM Battles AS P1
       WHERE
           (SELECT top (1) date
            FROM Battles AS P2
            WHERE P2.date > P1.date
            ORDER BY P2.date) IS NOT NULL) AS t
    UNION
      (SELECT CASE
       WHEN (CASE
                 WHEN datediff(DAY,date,CONVERT (date, GETDATE()))/365 = 0 THEN 'Y y.,'
                 ELSE cast((datediff(DAY,date,CONVERT (date, GETDATE()))/365) AS varchar(20)) + ','
             END + CASE
                       WHEN (datediff(DAY,date,CONVERT (date, GETDATE()))/30 - (datediff(DAY,date,CONVERT (date, GETDATE()))/365) * 12) = 0 THEN 'M m.'
                       ELSE cast((datediff(DAY,date,CONVERT (date, GETDATE()))/30 - (datediff(DAY,date,CONVERT (date, GETDATE()))/365) * 12) AS varchar(20))
                   END) = 'Y y.,M m.' THEN ''
       ELSE (CASE
                 WHEN datediff(DAY,date,CONVERT (date, GETDATE()))/365 = 0 THEN 'Y y.,'
                 ELSE cast((datediff(DAY,date,CONVERT (date, GETDATE()))/365) AS varchar(20)) + ','
             END + CASE
                       WHEN (datediff(DAY,date,CONVERT (date, GETDATE()))/30 - (datediff(DAY,date,CONVERT (date, GETDATE()))/365) * 12) = 0 THEN 'M m.'
                       ELSE cast((datediff(DAY,date,CONVERT (date, GETDATE()))/30 - (datediff(DAY,date,CONVERT (date, GETDATE()))/365) * 12) AS varchar(20))
                   END)
        END, date, CONVERT (date, GETDATE())
       FROM Battles AS T1
       WHERE
           (SELECT top (1) date    FROM Battles AS T2
            WHERE T2.date > T1.date ORDER BY T2.date) IS NULL );

    [email protected]

    akimachan, 12 Сентября 2018

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

    +2

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    select * from
    (
    select case when (case when datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365 = 0 then 'Y y.,' else cast((datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) as varchar(20)) + ',' end + case when (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/30 - (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) * 12) = 0 then 'M m.' else cast((datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/30 - (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) * 12) as varchar(20)) end) = 'Y y.,M m.' then '' else (case when datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365 = 0 then 'Y y.,' else cast((datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) as varchar(20)) + ',' end + case when (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/30 - (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) * 12) = 0 then 'M m.' else cast((datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/30 - (datediff(day,date,(select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date))/365) * 12) as varchar(20)) end) end as 'Возраст', date as date1, (select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date) as date2 from Battles as P1 where (select top (1) date from Battles as P2 where P2.date > P1.date order by P2.date) is not null) as t
    union
    (select case when (case when datediff(day,date,CONVERT (date, GETDATE()))/365 = 0 then 'Y y.,' else cast((datediff(day,date,CONVERT (date, GETDATE()))/365) as varchar(20)) + ',' end + case when (datediff(day,date,CONVERT (date, GETDATE()))/30 - (datediff(day,date,CONVERT (date, GETDATE()))/365) * 12) = 0 then 'M m.' else cast((datediff(day,date,CONVERT (date, GETDATE()))/30 - (datediff(day,date,CONVERT (date, GETDATE()))/365) * 12) as varchar(20)) end) = 'Y y.,M m.' then '' else (case when datediff(day,date,CONVERT (date, GETDATE()))/365 = 0 then 'Y y.,' else cast((datediff(day,date,CONVERT (date, GETDATE()))/365) as varchar(20)) + ',' end + case when (datediff(day,date,CONVERT (date, GETDATE()))/30 - (datediff(day,date,CONVERT (date, GETDATE()))/365) * 12) = 0 then 'M m.' else cast((datediff(day,date,CONVERT (date, GETDATE()))/30 - (datediff(day,date,CONVERT (date, GETDATE()))/365) * 12) as varchar(20)) end) end, date, CONVERT (date, GETDATE()) from Battles as T1 where (select top (1) date from Battles as T2 where T2.date > T1.date order by T2.date) is null
    );

    [email protected]

    akimachan, 12 Сентября 2018

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