Блог

Главная - Вопросы по программированию - SQL-соединение в строке, разделенной запятыми

SQL-соединение в строке, разделенной запятыми

#sql #sql-server #sql-server-2014

Вопрос:

Я использую SQL Server 2014, и у меня есть таблица (пример) , как показано ниже:

 | Hosts                                  | Description |
|------------------------------------------------------|
| 192.168.0.1,192.168.0.2,192.168.0.3    | Group A     |
| 192.168.0.10,192.168.0.13,192.168.0.15 | Group B     |
| 192.168.0.22                           | Group C     |
|------------------------------------------------------|

Я хочу, чтобы эти данные были в новой таблице:

 | Hosts           | Description |
|-------------------------------|
| 192.168.0.1     | Group A     |
| 192.168.0.2     | Group A     |
| 192.168.0.3     | Group A     |
| 192.168.0.10    | Group B     |
| 192.168.0.13    | Group B     |
| 192.168.0.15    | Group B     |
| 192.168.0.22    | Group C     |
|-------------------------------|

Что я сделал:

 -- List comma separated hosts in a new table (this part is working good)

set @abc = (select hosts from example for xml path(''))
set @xyz = (select replace(replace(@abc,'<HOSTS>',','),'</HOSTS>',''))
select * into newtable from split(@xyz, ',')

--My Join

select newtable.item, example.description
from newtable
left join example on newtable.item like '%'   example.hosts   '%'

Но я получаю:

 | Hosts           | Description |
|-------------------------------|
| 192.168.0.1     | NULL        |
| 192.168.0.2     | NULL        |
| 192.168.0.3     | NULL        |
| 192.168.0.10    | NULL        |
| 192.168.0.13    | NULL        |
| 192.168.0.15    | NULL        |
| 192.168.0.22    | Group C     |
|-------------------------------|

В принципе, мое соединение работает только тогда, когда мое значение хоста находится в этой строке в одиночестве. Я ожидал, что это будет решено с помощью моего запроса «‘%’ пример.хосты ‘%'», но нет.
Я попытался использовать ту же логику, используя «перекрестное применение» и «объединение», но безуспешно.

Как вы можете видеть, у меня не так много знаний в SQL, и у меня сложилось впечатление, что мне не хватает чего-то базового, хотя я застрял в этом надолго.

Я рассчитываю на вашу помощь, спасибо!

Комментарии:

1. Почему бы не исправить свой дизайн и не использовать индивидуальный подход? Это и есть настоящее решение здесь.

2. В tsql нет функции «разделения» — так что это должно быть что-то, написанное вами или вашим коллегой. Разделение строк CSV (или любых других с использованием выбранного вами разделителя) является общей темой и имеет множество решений. В текущих версиях sql server для этого используется функция string_split () . Не изобретайте колесо заново.

3. @Larnu Не могли бы вы сообщить мне более подробно о том, как я должен это сделать, как я уже упоминал, к сожалению, мои знания ограничены. Просто чтобы ввести вас в контекст, я выполняю этот запрос через logstash, чтобы интегрировать старое и неподдерживаемое программное обеспечение в свой elasticsearch.

4. Базовый дизайн базы данных означает, что значение представляет атомарное значение @ramirkl ; это означает, что данные с разделителями не хранятся. Создайте несколько таблиц с отношениями от 1 до многих. Почитайте о нормализации. Вы должны, как минимум, стремиться к достижению 3-й нормальной формы; однако данные с разделителями нарушают первую нормальную форму.

5. @SMor забыл упомянуть, что разделение было функцией, которую я должен был добавить после другого поста здесь. Я не могу использовать string_split() в SQL 2014, как я исследовал. Но эта часть работает нормально, я могу перечислить хосты, которые были разделены запятыми.

Ответ №1:

Пожалуйста, попробуйте следующее решение.

Он использует XML и XQuery.

Примечательные моменты:

  • Никаких производных таблиц или CTE.
  • Раздел CData защищает от символов, таких как амперсанд и тому подобное.
  • Выражение XPath содержит text() для максимальной производительности. Особенность SQL Server.

SQL

 -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Hosts VARCHAR(1024), Description VARCHAR(30));
INSERT INTO @tbl (Hosts, Description) VALUES
('192.168.0.1,192.168.0.2,192.168.0.3', 'Group A'),
('192.168.0.10,192.168.0.13,192.168.0.15', 'Group B'),
('192.168.0.22', 'Group C');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT ID, [Description]
    ,x.value('text()[1]', 'VARCHAR(20)') AS Hosts
FROM @tbl
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
          REPLACE(Hosts, @separator, ']]></r><r><![CDATA[')   
          ']]></r></root>' AS XML)) AS t1(c)
    CROSS APPLY c.nodes ('/root/r') AS t2(x);

Выход

  ---- ------------- -------------- 
| ID | Description |    Hosts     |
 ---- ------------- -------------- 
|  1 | Group A     |  192.168.0.1 |
|  1 | Group A     |  192.168.0.2 |
|  1 | Group A     |  192.168.0.3 |
|  2 | Group B     | 192.168.0.10 |
|  2 | Group B     | 192.168.0.13 |
|  2 | Group B     | 192.168.0.15 |
|  3 | Group C     | 192.168.0.22 |
 ---- ------------- --------------

Ответ №2:

Может быть, вам следует сделать что-то вроде приведенного ниже:

 SELECT Description,Split.a.value('.', 'VARCHAR(max)') 'Hosts' 
FROM  
(
     SELECT Description, CAST ('<X>'   REPLACE(Hosts, ',', '</X><X>')   '</X>' AS XML) AS Data 
     FROM example     
) AS A 
CROSS APPLY Data.nodes ('/X') AS Split(a)
Метки: ,