String split colonne et de se joindre à une autre table

Disons que j'ai 2 tables comme ceci :

Offres D'Emploi:

+----+------------+------------+
| ID |    Name    | Categories |
+----+------------+------------+
|  1 | Programmer | 1,2        |
|  2 | Analyst    | 3          |
+----+------------+------------+

Catégories:

+----+-----------------+
| ID |      Name       |
+----+-----------------+
|  1 | Programming     |
|  2 | Web Programming |
|  3 | Analysis        |
+----+-----------------+

Nous avons une chaîne de split qui prend une chaîne de caractères, un séparateur et retourne une table, mon problème est que je ne suis vraiment pas sûr de savoir comment intégrer la table dans ma requête pour joindre les offres d'emploi et les catégories.

Ma pensée est que ce serait quelque chose comme ceci :

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    CAT.[CategoryName]      AS CategoryName,
    CAT.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
LEFT JOIN
(
    SELECT 
        O.[ID]              AS OfferID,
        C.[CategoryID]      AS CategoryID,
        C.[Name]            AS Name
    FROM
        (
            SELECT *
            FROM [dbo].[Split](O.[Categories], ',')
        ) AS CJ
    LEFT JOIN
        [Categories] AS C
        ON C.CategoryID = CJ.items  
) AS CAT ON CAT.OfferID = O.[ID]

Actuellement, j'ai deux erreurs en disant:

  • multi-part identifier O.[ID] cannot be bound
  • multi-part identifier O.[Categories] cannot be bound
  • incorrect syntax near AS (dernière ligne)

Donc clairement le problème est de savoir comment je construis mon sous-requête.

fonctions définies par l'utilisateur comme ceci sur des ensembles de données plus va vous donner de mauvaises performances. envisager la création d'une nouvelle table JobOfferCategories (JobOfferId, "CategoryId") au lieu de la colonne catégories
Malheureusement je suis limité sur le côté technique de la base de données héritage, et ils n'ont pas l'intention de changer de sitôt. Actuellement, ils font une extraction sur joboffers et de toutes les catégories et de l'analyser en C# côté. Essayer d'optimiser un peu les choses et de les inclure fulltext sur DB côté de sorte que le résultat de cette sélection sera fondamentalement un point de vue.
Remarque: à partir dans les SS 2016, il y a un construit en fonction appelée STRING_SPLIT

OriginalL'auteur Erick | 2015-10-09

  1. 11

    Vous permet de simplifier considérablement ce à quelque chose comme cela.

    SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    c.[CategoryName]      AS CategoryName,
    c.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
outer apply [dbo].[Split](O.[Categories], ',') s
left join Categories as C on c.CategoryID = s.Items

    La préoccupation que j'ai, est de votre coupleur. Si il n'y a plus qu'une seule instruction select de la performance va souffrir horriblement. Pour une bonne explication de divers séparateurs disponibles, vous pouvez visiter cet article.

    http://sqlperformance.com/2012/07/t-sql-queries/split-strings

    Bon lien. Notez également que, à compter SS 2016, il y a un construit en fonction appelée STRING_SPLIT

    OriginalL'auteur Sean Lange

  2. 3

    À partir de SQL SERVER 2016, nous pouvons utiliser sql intégré la fonction STRING_SPLIT
    comme ci-dessous :

    SELECT * FROM JobOffers as j 
outer apply STRING_SPLIT(j.[Categories], ',') s
left join dbo.Categories as c on c.CategoryID =s.value

    OriginalL'auteur Diwas Poudel

  3. 2

    Si vous pouvez vivre avec une ligne par catégorie puis cela va fonctionner:

    select jo.*, c.name as category
from joboffers jo join
     categories c
     on ',' + jo.categories + ',' like '%,' + cast(c.id) + ',%';

    Ré-assembler dans une chaîne de caractères est douloureux dans SQL Server (mais tout à fait possible).

    Remarque: vous avez vraiment, vraiment mauvaise structure de données. Donc, il doit être corrigé comme mentionné dans un commentaire. Pourquoi est-il mauvais?

    • Stocker des numéros de chaînes de caractères.
    • Vous avez des id qui n'ont pas une relation de clé étrangère de la table de référence.
    • Vous êtes stocker plusieurs valeurs dans un champ unique.

    OriginalL'auteur Gordon Linoff