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

    −9

    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)
  2. 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

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

    0

    1. 1
    2. 2
    //Список категорий записан в строках с разделителем вида "23", "11||12" или даже "3||8||12||43||23"
    SELECT id, pagetitle FROM modx_site_content WHERE categores LIKE "%|[[*id]]" OR categores LIKE "%|[[*id]]|%" OR categores LIKE "[[*id]]|%" OR categores="[[*id]]"

    FODD, 29 Мая 2018

    Комментарии (49)
  6. SQL / Говнокод #24334

    −1

    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
    SELECT DISTINCT comment_author
    FROM  `wp_comments` 
    WHERE comment_content LIKE  '%и т.д%'
    ORDER BY comment_date
    
    https://ideone.com/78q77K
    
    
    SELECT DISTINCT comment_author
    FROM  `wp_comments` 
    WHERE comment_content LIKE  '%итд%'
    ORDER BY comment_date
    
    https://ideone.com/bB9w3t

    Тут сразу палятся файки roskomgovno aka SemaReal aka barop aka huesto aka guestinho aka guesto

    minusinho, 28 Мая 2018

    Комментарии (23)
  7. SQL / Говнокод #24273

    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
    "SELECT " +
                        "2 AS recType," +
                        "1 AS cnt," +
                        "''  AS vndName," +
                        "'' AS catName," +
                        "off.orderBy1S AS orderBy1S," +
                        "off._id AS _id," +
                        "off.vendorCode AS vendorCode," +
                        "off.price AS price," +
                        "off.currencyId AS currencyId," +
                        "off.thumbnail AS thumbnail," +
                        "off.name AS name," +
                        "IFNULL(crt.quantity,0) AS quantityItem," +
                        "off.queryRest AS queryRest," +
                        "off.valueRest AS valueRest," +
                        "off.lastChangedRest AS lastChangedRest," +
                        "off.price_uah_ir AS price_uah_ir," +
                        "IFNULL(vlt.[rate],0) AS rate " + //валютный курс для currencyId товара
                        "FROM tOffer AS off " +
                        "LEFT JOIN tCart AS crt ON off.vendorCode=crt.offerId " +
                        "LEFT JOIN tCurrency AS vlt ON off.currencyId=vlt.valute " +
    
                        "WHERE  off.vendorName='" + BRAND_NAME + "' AND off.categoryId=" + CATEGORY_ID + " AND  (off.name LIKE " + nameFilter + ") " +
                        "UNION " +
                        "SELECT " +
                        "1," +
                        "COUNT(*)," +
                        "MAX(vnd.name)," +
                        "IFNULL(cat.name,'')," +
                        "0," +
                        "0," +
                        "0," +
                        "0," +
                        "''," +
                        "''," +
                        "vnd.name," +
                        "0," +
                        "0," +
                        "''," +
                        "''," +
                        "0," +
                        "0 " +
                        "FROM tOffer AS off " +
                        "LEFT JOIN tVendor AS vnd ON off.vendorName=vnd.name " +
                        "INNER JOIN tCategory AS cat ON off.categoryId=cat._id " +
                        "WHERE  off.vendorName='" + BRAND_NAME + "' AND off.categoryId=" + CATEGORY_ID + " AND  (off.name LIKE " + nameFilter + ") " +
                        "GROUP BY vnd.name HAVING COUNT(*)>0 " +
                        "UNION " +
                        "SELECT " +
                        "3," +
                        "COUNT(*)," +
                        "MAX('Другой товар')," +
                        "''," +
                        "0," +
                        "0," +
                        "0," +
                        "0," +
                        "''," +
                        "''," +
                        "'Другой товар'," +
                        "0," +
                        "0," +
                        "''," +
                        "''," +
                        "0," +
                        "0 " +
                        "FROM tOffer AS off " +
                        "WHERE  off.vendorName<>'" + BRAND_NAME + "' AND  (off.name LIKE " + nameFilter + ") " +
                        "GROUP BY 'Другой товар' HAVING COUNT(*)>0 " +
                        "UNION " +
                        "SELECT " +
                        "4," +
                        "1," +
                        "''," +
                        "''," +
                        "off.orderBy1S," +
                        "off._id," +
                        "off.vendorCode," +
                        "off.price," +
                        "off.currencyId," +
                        "off.thumbnail," +
                        "off.name," +
                        "IFNULL(crt.quantity,0)," +
                        "off.queryRest," +
                        "off.valueRest," +
                        "off.lastChangedRest," +
                        "off.price_uah_ir," +
                        "IFNULL(vlt.[rate],0) " + //валютный курс для currencyId товара
                        "FROM tOffer AS off " +
                        "LEFT JOIN tCart AS crt ON off.vendorCode=crt.offerId " +
                        "LEFT JOIN tCurrency AS vlt ON off.currencyId=vlt.valute " +
                        "WHERE  off.vendorName<>'" + BRAND_NAME + "' AND  (off.name LIKE " + nameFilter + ") " +
                        "ORDER BY  recType,orderBy1S ";

    Комменты говорят, что это поиск по бренду и названию

    makesense, 16 Мая 2018

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

    +1

    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
    select
      x1.airport, x1.amount, x1.exempt
    from
      Taxes x1
    where
      x1.code = 'departure'
      and x1.airport in (
        select
          xx1.airport
        from
          (select
            yx1.airport
          from
            Taxes yx1
          where
            yx1.airport = x1.airport
            and yx1.code = x1.code
          group by
            yx1.airport, yx1.amount, yx1.exempt
          ) xx1
        group by xx1.airport
        having count(xx1.airport) > 1
      )
    ;

    Давно я не копался в SQL. Вот что нагородил :(

    Суть такова: у аэропортов есть departure tax, который может быть разный в зависимости от некоторых критериев (нерелевантно каких).
    А может быть и одинаковый. Но все равно аэропорт может иметь несколько рядков в БД (с одинаковым amount). А еще может быть exempt = 'X',
    что тождественно amount = 0.

    Задача: выбрать все рядки с departure tax для аэропортов у которых taxamt/exempt разный в зависимости от некоторых других критериев.

    Elvenfighter, 10 Мая 2018

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

    −2

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    7. 7
    SELECT clips.NAME, clips.DATE_OUT, clips.AUTHOR, clips.TOPIC, clips.ANNOT
    FROM clips
    WHERE (clips.NAME like ("*" + [Forms]![ПОИСК]!clips_NAME + "*") Or [Forms]![ПОИСК]!clips_NAME = "") And
    (clips.topic like ("*" + [Forms]![ПОИСК]!topics_NAME + "*") Or [Forms]![ПОИСК]!topics_NAME = "" Or  [Forms]![ПОИСК]!topics_NAME Is Null) And
    (clips.Author like ("*" + [Forms]![ПОИСК]!Authors_NAME + "*") Or [Forms]![ПОИСК]!Authors_NAME = "" Or  [Forms]![ПОИСК]!Authors_NAME Is Null) And
    (clips.ANNOT like ("*" + [Forms]![ПОИСК]!ANNOT + "*") Or [Forms]![ПОИСК]!ANNOT = ""Or [Forms]![ПОИСК]!ANNOT Is Null) And
    ((clips.Date_out <=  [Forms]![ПОИСК]!Date_max And clips.Date_out >=  [Forms]![ПОИСК]!Date_min) Or clips.Date_out Is Null);

    База данных на Access.
    Переделка другой БД, в которой зачем-то все авторы хранились в отдельной таблице, а в основной таблице - айдишники. Можно ведь просто прописать авторов в основную таблицу и не париться.

    Steve_Brown, 20 Апреля 2018

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

    +1

    1. 1
    SELECT a.*, ads.*, CASE ads.operation_id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 3 END AS sort

    Когда очень надо поменять местами порядок операций

    SeniorShaurman, 18 Января 2018

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