mysql_real_escape_string – Страница 6

mysql_real_escape_string / Говнокод #24273

0

1
2
3
4
5
6
7
8
9
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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.05.2018 (Updated 16.05.2018)

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

mysql_real_escape_string / Говнокод #24243

0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.05.2018 (Updated 10.05.2018)

Комментарии (11, +11)

mysql_real_escape_string / Говнокод #24157

0

1
2
3
4
5
6
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.04.2018 (Updated 20.04.2018)

Комментарии (7, +7)

mysql_real_escape_string / Говнокод #23662

0

1

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

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

SeniorShaurman, 18.01.2018 (Updated 01.04.2018)

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

mysql_real_escape_string / Говнокод #23415

0

1

UPDATE `xxx` SET `updated_count` = (UPDATE `yyy` SET `zzz` = 0 WHERE `zzz` = 42)

Минимальный код в моей проблеме. Проблема в том, что UPDATE должен возвращать число изменённых строк, а MYSQL говорит, что тут ошибка. Что же мне теперь делать?

d_fomenok, 16.10.2017 (Updated 31.03.2018)

Комментарии (18, +18)

mysql_real_escape_string / Говнокод #23355

0

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

while exists (
  select 
    C.CONTRAGENT_NAME,
    PR.PHONE 
  from CONTRAGENTS C
    inner join CONTRAGENT_PROPERTIES PR on PR.CONTRAGENT_ID = C.CONTRAGENT_ID
  except select NAME,PHONE from IMPORT_SOURCE)
begin
  declare @IDENTITIES table (CONTRAGENT_ID int)
  insert CONTRAGENTS (
    CONTRAGENT_NAME,
    …)
  output inserted.CONTRAGENT_ID into @IDENTITIES
  select distinct
    NAME,
    …
  from IMPORT_SOURCE

  insert CONTRAGENT_PROPERTIES (
    …,
    PHONE,
    …)
  select
    …,
    S.PHONE
    …
  from @IDENTITIES I
    inner join CONTRAGENTS C on C.CONTRAGENT_ID = I.CONTRAGENT_ID
    cross apply (
      select top 1
      from IMPORT_SOURCE S
        left join (
          select
            C.CONTRAGENT_NAME,
            PR.PHONE 
          from CONTRAGENTS C
            inner join CONTRAGENT_PROPERTIES PR on PR.CONTRAGENT_ID = C.CONTRAGENT_ID
        ) TX on TX.CONTRAGENT_NAME = S.NAME and TX.PHONE = S.PHONE
      where TX.CONTRAGENT_NAME is null)
end

Импорт. В источнике за ключевые поля нужно было взять NAME и PHONE, которые в целевой базе — в разных таблицах. Курсоры — слишком долго.

tucvbif, 23.09.2017 (Updated 31.03.2018)

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

mysql_real_escape_string / Говнокод #23351

0

1

SELECT * FROM order WHERE coalesce(cancelation_date,\'7777-07-07\'::DATE) >= :tdDate

iworld, 22.09.2017 (Updated 31.03.2018)

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

mysql_real_escape_string / Говнокод #23295

0

1
2
3

if length(varrstr(cellphoneidx) != 11) or regexp_instr(varrstr(cellphoneidx), '\d\d\d\d\d\d\d\d\d\d') = 0 then
  return 1;
end if;

Проверка на валидность номера телефона (с вкраплениями pl/sql):
И так везде, где требуется проверка на определенное количество цифр (номер инн, док-ов и т.д.).
Псц, товарищи.

ChezterLP, 24.08.2017 (Updated 31.03.2018)

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

mysql_real_escape_string / Говнокод #23253

0

1
2
3
4
5
6

SUM(
  CAST (
    SUBSTRING(CAST([PercentAmount] AS VARCHAR),1, CHARINDEX ('.',[PercentAmount])-1) 
    + '.'
    + SUBSTRING(CAST([PercentAmount] AS VARCHAR),CHARINDEX('.',[PercentAmount])+1, 2+CHARINDEX ('.',[PercentAmount])) 
AS MONEY))

А как вы достигаете точности в 2 знака после запятой?

dreamca4er, 10.08.2017 (Updated 31.03.2018)

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

mysql_real_escape_string / Говнокод #23214

0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE TABLE [dbo].[TBL_JOUR_PRODUCTION_PLAN_KK_BH](
	[JPPKKBH_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBH_JPKKBH_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBH_WC_ID] [int] NOT NULL,
	[JPPKKBH_DATE] [datetime] NOT NULL,
	[JPPKKBH_QTY] [numeric](30, 5) NOT NULL,
	[JPPKKBH_QTYF] [numeric](30, 5) NOT NULL,
	[JPPKKBH_COMENT] [varchar](8000) NULL,
	[JPPKKBH_DESCRIPTION] [varchar](8000) NOT NULL,
	[JPPKKBH_WHO] [varchar](150) NOT NULL,
	[JPPKKBH_WHO_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBH_WHEN] [datetime] NOT NULL,
(

CREATE TABLE [dbo].[TBL_JOUR_PRODUCTION_PLAN_KK_BH_HISTORY](
	[JPPKKBHH_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBHH_JPPKKBH_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBHH_JPPKKBH_JPKKBH_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBHH_JPPKKBH_WC_ID] [int] NOT NULL,
	[JPPKKBHH_JPPKKBH_DATE] [datetime] NOT NULL,
	[JPPKKBHH_JPPKKBH_QTY] [numeric](30, 5) NOT NULL,
	[JPPKKBHH_JPPKKBH_QTYF] [numeric](30, 5) NOT NULL,
	[JPPKKBHH_JPPKKBH_COMENT] [varchar](8000) NULL,
	[JPPKKBHH_JPPKKBH_DESCRIPTION] [varchar](8000) NOT NULL,
	[JPPKKBHH_JPPKKBH_WHO_ID] [uniqueidentifier] NULL,
	[JPPKKBHH_JPPKKBH_WHEN] [datetime] NOT NULL,
	[JPPKKBHH_OPER] [int] NOT NULL,
	[JPPKKBHH_WHO_ID] [uniqueidentifier] NOT NULL,
	[JPPKKBHH_WHEN] [datetime] NOT NULL,
(

no comments

Kaldblpb, 25.07.2017 (Updated 31.03.2018)

Комментарии (17, +17)