MS SQL Server
  1. MS SQL Server
  3. Requête SQL compliquée: search d'éléments correspondant à plusieurs foreign keys différentes
Intereting Posts
Comment configurer le triggersur à l'aide de la table temporaire Enregistrements SQL uniquement pendant 3 mois consécutifs SQLAlchemy / pandas to_sql pour SQLServer – CREATE TABLE dans master db Préserver ORDER BY dans SELECT INTO L'instruction de configuration ne peut pas être utilisée dans une transaction user 2 requêtes de mise à jour exécutées en même time SQL Server: comment utiliser UNION avec deux requêtes qui ont les deux une clause WHERE? Comment GROUP BY sans rompre l'ordre? Identifier le format de date d'une string en PHP Attendre la fin de la database de création OU condition dans SQL Pourquoi ai-je une erreur lors de la transmission d'un paramètre à une procédure stockée? Comment soustraire 30 jours de la date actuelle en utilisant SQL Server Générer des scripts de modification à partir d'un projet de database TFS et Visual Studio Ajouter une couleur de ligne en alternance au rapport sur les services de reporting SQL Server

Requête SQL compliquée: search d'éléments correspondant à plusieurs foreign keys différentes

Donc, imaginez que vous avez une table de Products (ID int, Name nvarchar(200)) , et deux autres tables, ProductsCategories (ProductID int, CategoryID int) et InvoiceProducts (InvoiceID int, ProductID int) .

J'ai besoin d'écrire une requête pour produire un set de produits qui correspondent à un set donné d'identifiants de factures et d'identifiants de catégories de sorte que la list de produits corresponde à toutes les catégories spécifiées et toutes les factures spécifiées, sans return au SQL dynamic. Imaginez que je doive find une list de produits dans les deux catégories 1 et 2 et dans les factures 3 et 4.

Pour commencer, j'ai écrit une procédure stockée qui accepte les identifiants de catégorie et les identifiants de facture comme des strings, et les parsing en tables: 

  CREATE PROCEDURE dbo.SearchProducts (@categories varchar(max), @invoices varchar(max)) AS BEGIN with catids as (select cast([value] as int) from dbo.split(@categories, ' ')), invoiceids as (select cast([value] as int) from dbo.split(@invoices, ' ')) select * from products --- insert awesomeness here END

Les différentes solutions que j'ai trouvées sont terribles et fonctionnent less bien. La meilleure chose que j'ai trouvée est de générer une vue composée de jointures gauches de tous les critères, mais cela semble très cher et ne résout pas le problème de la correspondance de toutes les différentes keys spécifiées.

Mise à jour: Ceci est un exemple de requête que j'ai écrit qui donne les résultats attendus. Est-ce que je rate des opportunités d'optimization? Comme les opérations masortingcielles magiques de licorne par les ninjas? 

 with catids as (select distinct cast([value] as int) [value] from dbo.split(@categories, ' ')), invoiceids as (select distinct cast([value] as int) [value] from dbo.split(@invoices, ' ')) select pc.ProductID from ProductsCategories pc (nolock) inner join catids c on c.value = pc.CategoryID group by pc.ProductID having COUNT(*) = (select COUNT(*) from catids) intersect select ip.ProductID from InvoiceProducts ip (nolock) inner join invoiceids i on i.value = ip.InvoiceID group by ip.ProductID having COUNT(*) = (select COUNT(*) from invoiceids)

Pourvu que vous ayez des index uniques sur les deux (ProductID, CategoryID) et (ProductID, InvoiceID) : 

 SELECT ProductID FROM ( SELECT ProductID FROM ProductInvoice WHERE InvoiceID IN (1, 2) UNION ALL SELECT ProductID FROM ProductCategory pc WHERE CategoryID IN (3, 4) ) q GROUP BY ProductID HAVING COUNT(*) = 4

ou, si vos valeurs sont transmises dans des strings CSV : 

 WITH catids(value) AS ( SELECT DISTINCT CAST([value] AS INT) FROM dbo.split(@categories, ' ')) ), ( SELECT DISTINCT CAST([value] AS INT) FROM dbo.split(@invoices, ' ')) ) SELECT ProductID FROM ( SELECT ProductID FROM ProductInvoice WHERE InvoiceID IN ( SELECT value FROM invoiceids ) UNION ALL SELECT ProductID FROM ProductCategory pc WHERE CategoryID IN ( SELECT value FROM catids ) ) q GROUP BY ProductID HAVING COUNT(*) = ( SELECT COUNT(*) FROM catids ) + ( SELECT COUNT(*) FROM invoiceids )

Notez que dans SQL Server 2008 vous pouvez passer des parameters de type table dans les procédures stockées.

Je commencerais par quelque chose comme ça, en utilisant vos valeurs d'ID déposées à partir des parameters. Les tables temporaires peuvent aider avec la vitesse de sous-requête. 

 select p.* from ( select pc.* from catids c inner join ProductsCategories pc on pc.CategoryID = c.value ) catMatch inner join ( select pin.* from invoiceids i inner join ProductsInvoices pin on pin.InvoiceID = i.value ) invMatch on invMatch.ProductID = catMatch.ProductID inner join Products p on p.ID = invMatch.ProductID

ProductCategories doit avoir un index clusterisé sur (CategoryId, ProductId) et InvoiceProducts doit en avoir un (InvoiceId, ProductId) de façon optimale. Cela permettra de find les identifiants de produit donnés CategoryId et InvoiceId en utilisant datatables dans les index clusterisés uniquement.

Vous pouvez utiliser une fonction pour returnner une table de ints avec une string. Google "CsvToInt" et click le premier lien de SqlTeam pour voir le code.

Alors vous pourriez: 

 SELECT * FROM Products WHERE ID IN (SELECT DISTINCT ProductId FROM ProductCategories WHERE CategoryId in dbo.CsvToInt(@categories) ) AND ID IN (SELECT DISTINCT ProductId FROM InvoiceProducts WHERE InvoiceId in dbo.CsvToInt(@invoices) )

Que diriez-vous d'un CTE récursif?

Ajoutez d'abord des numéros de ligne aux tables de critères, puis un pseudo SQL si vous voulez: 

 ;WITH cte AS( Base case: Select productid, criteria from products left join criteria where row_number = 1 if it matches criteria from both row 1s or one is null. UNION ALL Recursive case: Select n+1 criteria row from products left join criteria where row_number = cte.row_number + 1 AND matches criteria from both row_number + 1 or one or the other (but not both) is null ) SELECT * WHERE criteria = maximum id from criteria table.

Cela vous donnera un moyen d'effectuer ET sur plusieurs critères, et devrait bien fonctionner.

Cela a-t-il un sens? J'ai fait quelques trucs assez cool avec des CTE récemment, et je peux élaborer si nécessaire.

Suppression du code cte parce que c'était faux, et pas vraiment utile d'avoir une meilleure solution.

Passez-les en tant que paramètre XML, stockez-les dans une table temporaire et joignez-les.