Значения, разделенные запятыми, в поле базы данных
У меня есть таблица продуктов. Каждая строка в этой таблице соответствует одному продукту и идентифицируется уникальным идентификатором. Теперь каждый продукт может иметь несколько "кодов", связанных с этим продуктом. Например:
Id | Code
----------------------
0001 | IN,ON,ME,OH
0002 | ON,VI,AC,ZO
0003 | QA,PS,OO,ME
То, что я пытаюсь сделать, это создать хранимую процедуру, чтобы я мог передавать коды типа "ON, ME" и возвращать каждый продукт, содержащий код "ON" или "ME". Поскольку коды разделены запятыми, я не знаю, как я могу разбить их и выполнить поиск. Возможно ли использование только TSQL?
Изменить: это критически важная таблица. Я не имею права изменять его.
Ответы
Ответ 1
Вы должны хранить коды в отдельной таблице, так как у вас много отношений. Если вы их отделите, вы легко сможете проверить.
Можно было бы сделать в виде системы, которую у вас есть сейчас, но для этого потребуется текстовый поиск столбцов, при этом будет выполняться несколько поисков для каждой строки, что будет иметь большие проблемы с производительностью по мере роста ваших данных.
Если вы попытаетесь спуститься по текущему пути:
Вам придется разбить вашу входную строку, потому что ничто не гарантирует, что коды на каждой записи находятся в одном и том же порядке (или смежных) в качестве входного параметра. Тогда вам нужно будет сделать
Code LIKE '%IN%'
AND Code Like '%QA%'
с дополнительной инструкцией для каждого кода, который вы проверяете. Очень неэффективно.
Идея UDF ниже также является хорошей идеей. Однако, в зависимости от размера ваших данных и частоты запросов и обновлений, у вас могут быть и проблемы.
можно ли создать дополнительную таблицу, которая нормализована, которая будет синхронизирована по расписанию (или на основе триггера) для запроса?
Ответ 2
Сначала сделаем исходную таблицу следующей:
Id | Value
-----+------
0001 | IN
0001 | ME
0001 | OH
0001 | ON
0002 | AC
0002 | ON
0002 | VI
0002 | ZO
0003 | ME
0003 | OO
0003 | PS
0003 | QA
Это выполняется путем разбора значений, разделенных запятыми, на строки. Затем используйте ключевое слово CROSS APPLY, чтобы присоединиться к исходной таблице, чтобы получить идентификатор. Следующий шаг - просто запросить этот CTE.
create function FnSplitToTable
(
@param nvarchar(4000)
)
returns table as
return
with
Num(Pos) as -- list of positions, numbered from 1 to 4000, largest nvarchar
(
select cast(1 as int)
union all
select cast(Pos + 1 as int) from Num where Pos < 4000
)
select substring(@Param, Pos,
charindex(',', @Param + ',', Pos) - Pos) as Value
from Num where Pos <= convert(int, len(@Param))
and substring(',' + @Param, Pos, 1) = ','
go
create proc ProcGetProductId
(
@Codes nvarchar(4000)
)
as
with
Src
(
Id,
Code
)
as
(
select '0001', 'IN,ON,ME,OH'
union all
select '0002', 'ON,VI,AC,ZO'
union all
select '0003', 'QA,PS,OO,ME'
),
Parse as
(
select
s.Id,
f.Value
from
Src as s
cross apply
FnSplitToTable(s.Code) as f
)
select distinct
p.Id
from
Parse as p
join
FnSplitToTable(@Codes) as f
on
p.Value = f.Value
option (maxrecursion 4000)
go
exec ProcGetProductId 'IN,ME' -- returns 0001 & 0003
Ответ 3
Все остальные, кажется, очень хотят сказать вам, что вы не должны этого делать, хотя я не вижу никаких явных объяснений, почему бы и нет.
Помимо нарушения правил нормализации, причина в том, что вы будете выполнять сканирование по всем строкам, так как вы не можете иметь индекс для отдельных "значений" в этом столбце.
Проще говоря, для механизма базы данных нет способа сохранить какой-то быстрый список, какие строки содержат код "AC", если вы не разбиваете его на отдельную таблицу или не помещаете в столбец самостоятельно.
Теперь, если у вас есть другие критерии в операторах SELECT, которые ограничивают количество строк до некоторого количества управляемых номеров, то, возможно, это будет нормально, но в противном случае я бы, если можно, попытался избежать этого решения и сделать что другие уже сказали вам, разделите его на отдельную таблицу.
Теперь, если вы застряли в этом проекте, вы можете выполнить поиск, используя следующий тип запроса:
...
WHERE ',' + Code + ',' LIKE '%,AC,%'
Это будет:
- Соответствует 'ON, VI, AC, ZO'
- Не соответствует 'ON, VI, TAC, ZO'
Я не знаю, является ли последний подходящим вариантом в вашем случае, если у вас есть только 2-буквенные коды, вы можете использовать только это:
...
WHERE Code LIKE '%AC%'
Но опять же, это произойдет ужасно, если вы не ограничите количество строк, используя другие критерии.
Ответ 4
Несмотря на то, что все предыдущие плакаты верны в отношении нормализации вашей схемы db, вы можете делать то, что хотите, с помощью "Table-Valued UDF", который берет строку с разделителями и возвращает таблицу с одной строкой за значение в строке... Вы можете использовать эту таблицу, как и любую другую таблицу в вашей сохраненной процедуре, присоединиться к ней и т.д.... это решит вашу проблему...
Здесь ссылка на такой UDF: FN_Split UDF
Хотя в статье говорится о ее использовании для передачи разделенного списка значений данных в хранимый процесс, вы можете использовать тот же UDF для работы с разделительной строкой, хранящейся в столбце существующей таблицы....
Ответ 5
То, как вы храните данные, нарушает правила нормализации. В каждом поле должно храниться только одно атомное значение. Вы должны хранить каждый элемент в одной строке.
Ответ 6
Более 1-летний вопрос, но все же думал, что это будет полезно. Вы можете использовать функцию FIND_IN_SET MySql. Я не уверен, поддерживают ли другие СУБД или нет.
Вы можете использовать эту функцию следующим образом:
SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0
Ответ 7
Это может быть невозможно, если вы застряли в разработке этой базы данных, но было бы намного проще поместить коды в отдельные записи в другую таблицу:
ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)
Таблица может выглядеть так:
ProductID Code
-----------------
0001 IN
0001 ON
0001 ME
...
Запрос будет выглядеть примерно так (вам придется каким-то образом передавать коды, либо как отдельные переменные, либо, может быть, разделенные запятыми строки, которые вы разделили в proc):
select ProductID
from ProductCode
where Code in ('ON', 'ME')
Ответ 8
Я согласен с другими плакатами здесь, что вы должны внимательно изучить нормализацию схемы, но я также знаю, что ярлыки являются частью жизни.
Здесь примерная функция, написанная на диалекте Sybase, которая делает то, что вы делаете:
ALTER FUNCTION "DBA"."f_IsInStringList"( IN @thisItem char(2), IN @thisList varchar(4000) )
RETURNS INTEGER
DETERMINISTIC
BEGIN
DECLARE is_member bit;
DECLARE LOCAL TEMPORARY TABLE tmp (thisItem char(2)) ;
DECLARE @tempstring varchar(10);
DECLARE @count integer;
IF LENGTH(TRIM(@thisList)) > 0 THEN
WHILE LENGTH(TRIM(@thisList)) > 0 LOOP
-- loop over comma-separated list and stuff members into temp table
IF LOCATE ( @thisList, ',' , 1) > 0 THEN
SET @count = LOCATE ( @thisList, ',' , 1);
SET @tempstring = SUBSTRING ( @thisList, 1,@count-1 );
INSERT INTO tmp ( thisItem ) VALUES ( @tempstring );
SET @thisList = STUFF ( @thisList, 1, @count, '' )
ELSE
INSERT INTO tmp ( thisItem ) VALUES ( @thisList );
SET @thisList = NULL;
END IF;
END LOOP ;
END IF;
IF EXISTS (SELECT * FROM tmp WHERE thisItem = @thisItem ) THEN
SET is_member = 1;
ELSE
SET is_member = 0 ;
END IF ;
RETURN is_member;
END
Затем вы можете создать простой запрос, чтобы проверить, происходит ли значение в вашей разделимой запятой строке:
select * from some_table t
WHERE f_IsInStringList('OR', t.your_comma_separated_column) = 1 OR
f_IsInStringList('ME', t.your_comma_separated_column) = 1
Ответ 9
1-й шаг: код для создания функции
<font face="Courier New" size="2">
<font color = "blue">CREATE</font> <font color = "blue">FUNCTION</font> <font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[Udflistofids]</b></font> <font color = "maroon">(</font>
<br/><font color = "green"><i>-- Add the parameters for the function here</i></font>
<br/><font color = "#8000FF">@ListOfIDs</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/><font color = "green"><i>--, @IDsSeperationChar as varchar(5) = ','</i></font>
<br/><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "maroon">returns</font> <font color = "#8000FF">@TabListOfIDs</font> <font color = "blue">TABLE</font> <font color = "maroon">(</font>
<br/> <font color = "green"><i>-- Add the column definitions for the TABLE variable here</i></font>
<br/> <font color = "maroon">id</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "blue">AS</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "green"><i>-- Fill the table variable with the rows for your result set</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Pos</font> <font color = "blue">AS</font> <font color = "black"><i>INT</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ID</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "blue">AS</font> <font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">5</font><font color = "maroon">)</font> <font color = "silver">=</font> <font color = "red">','</font>
<br/>
<br/> <font color = "green"><i>--SET @ListOfIDs = REPLACE( @ListOfIDs, @IDsSeperationChar, ',')</i></font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">IF</font> <font color = "fuchsia"><i>Replace</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "silver">,</font> <font color = "red">''</font><font color = "maroon">)</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">WHILE</font> <font color = "#8000FF">@Pos</font> <font color = "silver">></font> <font color = "black">0</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ID</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>LEFT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "#8000FF">@Pos</font> <font color = "silver">-</font> <font color = "black">1</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@ID</font> <font color = "silver"><></font> <font color = "red">''</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">INSERT</font> <font color = "blue">INTO</font> <font color = "#8000FF">@TabListOfIDs</font>
<br/> <font color = "maroon">(</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid1</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid2</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid3</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid4</font><font color = "silver">,</font>
<br/> <font color = "maroon">uniqueid5</font><font color = "maroon">)</font>
<br/> <font color = "blue">VALUES</font> <font color = "maroon">(</font><font color = "#8000FF">@ID</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID1</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID2</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID3</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID4</font><font color = "silver">,</font>
<br/> <font color = "#8000FF">@UniqueID5</font><font color = "maroon">)</font> <font color = "green"><i>--Use Appropriate conversion</i></font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@ListOfIDs</font> <font color = "silver">=</font> <font color = "fuchsia"><i>RIGHT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font> <font color = "silver">-</font>
<br/> <font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ID</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font><font color = "maroon">)</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "blue">SET</font> <font color = "#8000FF">@Pos</font> <font color = "silver">=</font> <font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font> <font color = "silver">+</font> <font color = "#8000FF">@IDsSeperationChar</font> <font color = "silver">+</font> <font color = "red">'%'</font><font color = "silver">,</font> <font color = "#8000FF">@ListOfIDs</font>
<br/> <font color = "maroon">)</font>
<br/> <font color = "green"><i>--SET @Pos = CHARINDEX(@IDsSeperationChar, @ListOfIDs, 1)</i></font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">RETURN</font>
<br/> <font color = "blue">END</font>
<br/>
<br/><font color = "maroon">go</font>
</font>
**2nd Step : Code to get the result**
<font face="Courier New" size="2">
<font color = "blue">DECLARE</font> <font color = "#8000FF">@udvMax</font> <font color = "black"><i>NVARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/>
<br/><font color = "blue">SELECT</font> <font color = "#8000FF">@udvMax</font> <font color = "silver">=</font> <font color = "red">''</font> <font color = "silver">+</font> <font color = "fuchsia"><i>Substring</i></font><font color = "maroon">(</font> <font color = "maroon">(</font> <font color = "blue">SELECT</font> <font color = "red">' Union '</font> <font color = "silver">+</font>
<br/> <font color = "red">'Select * from dbo.udfListOfIDs('''</font> <font color = "silver">+</font>
<br/> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">id</font> <font color = "silver">+</font> <font color = "red">''', '''</font> <font color = "silver">+</font> <font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font> <font color = "silver">+</font>
<br/> <font color = "red">''', null,null,null )'</font> <font color = "blue">FROM</font> <font color = "maroon">tmpu</font> <font color = "blue">FOR</font> <font color = "maroon">xml</font> <font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">''</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">,</font> <font color = "black">7</font><font color = "silver">,</font>
<br/> <font color = "black">200000</font><font color = "maroon">)</font>
<br/> <font color = "silver">+</font>
<br/> <font color = "red">' Order by UniqueID1, UniqueID2, UniqueID3, UniqueID4, UniqueID5, ID'</font>
<br/>
<br/><font color = "green"><i>--Select @udvMax</i></font>
<br/><font color = "blue">EXECUTE</font> <font color = "#FF0080"><b>Sp_executesql</b></font>
<br/> <font color = "#8000FF">@udvMax</font>
</font>
**** Может быть, вам может понадобиться добавить свои критерии в select statement на втором этапе. **
Надеюсь, это поможет вам.
В JP
Ответ 10
Хотя в вашем случае простенький LIKE будет работать, вот решение, как разобрать строки, разделенные запятыми Нормализация таблицы (разделять поля, разделенные запятыми, на отдельные записи).
Ответ 11
если вы хотите сделать это с помощью php и mysql
это может быть любое число ключевых слов без ограничений
$var = explode(',',"ahmad,sayeed,asmal,babu");
$query = "SELECT * FROM post WHERE post_tags LIKE '%a%' ";
$query1=NULL;
foreach($var as $value)
{
$query1.= " OR post_tags LIKE '%$value%' ";
}
echo "$query $query1";
ВЫВОД:
SELECT * FROM post WHERE post_tags LIKE '% a%' ИЛИ post_tags LIKE '% ahmad%' ИЛИ post_tags LIKE '% sayeed%' ИЛИ post_tags LIKE '% asmal%' ИЛИ post_tags LIKE '% babu%'
