MS SQL Server
  1. MS SQL Server
  3. requête complexe sur la combinaison des données de deux tables
Intereting Posts
Quel est le meilleur moyen de stocker et de récupérer des adresses postales en utilisant une database SQL Server et le framework .NET? Comment résoudre datatables string ou binary serait tronqué dans la procédure stockée SQL Server Syntaxe incorrecte près du mot key "groupe" essayant de lier plusieurs tables dans SQL Server Comment returnner une string de caractères spécifique Obtenir la list des éléments et leurs valeurs à partir d'un fragment xml non typé dans T-SQL Le debugging n'affiche pas la version actuelle de la procédure stockée Créer un nouvel user pour l'instance locale de SQL Server Comment sélectionner différentes valeurs de ligne dans une colonne différente Authentification SQL Server ou security embeddede? Sql server étrange Incrément d'identité Pourquoi ces valeurs Datetime renvoient-elles le même Checksum & Checksum_Agg? Comment puis-je le rendre plus unique? Où puis-je stocker un mot de passe crypté sensible dans un travail d'agent SQL Server? L'ajout de contraintes est DDL ou DML Comment vérifier si une table existe déjà dans la database? Requête pour faire défiler une table et insert de manière incrémentale par groupe dans une autre

requête complexe sur la combinaison des données de deux tables

Récemment j'ai eu la request suivante. Il y a deux tables. 

-- lots of items declare @items table(id varchar(10), pieces integer) -- boxes declare @boxes table(num varchar(10), capacity integer) insert @items(id, pieces) select 'l1', 5 union all select 'l2', 12 union all select 'l3', 8 insert @boxes(num, capacity) select 'o1',2 union all select 'o2', 8 union all select 'o3', 2 union all select 'o4', 5 union all select 'o5', 9 union all select 'o6', 5 -- list all pairs of items-boxes. So that item will be put in what order -- example: o1-l1, o2-l1, o2-l2, o3-l2, ...

S'il vous plaît laissez-moi vous expliquer de manière impérative: Il y a deux tables. Un avec des objects un autre avec des boîtes. Nous devons mettre tous les éléments dans des boîtes de la manière suivante:

Nous prenons le premier item l1 et le premier box o1. L'article l1 a 5 pièces et la capacité de la boîte o1 est 2. Nous ne pouvons mettre que 2 pièces maximum dans la boîte o1. Nous créons donc la première rangée: 

 o1-l1

Nous avons rempli la boîte o1. passer à la case suivante o2. Il a la capacité de 8 et nous avons l'item l1 qui a 3 pièces à gauche. Mettre les morceaux de gauche de l1 dans la case o2 et ainsi nous créons le deuxième logging: 

 o2-l1

Nous avons mis toutes les pièces de l'object l1 dans les boîtes. Passer à l'élément suivant l2. Il a les 12 pièces. Et nous avons 5 capacité restante dans la case o2. Nous mettons donc 5 pièces de l2 dans o2 et créons le prochain logging: 

 o2-l2

Ensuite, nous prenons la boîte suivante dans l'ordre et la création de l'logging suivant: 

 o3-l2

Et de cette façon, nous générons les rangées jusqu'à ce que nous "mettions" tous les morceaux d'objects dans les boîtes. La requête qui en résulte devrait être quelque chose comme: 

 o1-l1 o2-l1 o2-l2 o3-l2 ...

Il pourrait être résolu de manière impérative en T-SQL avec le CURSEUR et d'autres choses, ce qui n'est pas bon en termes de performances. Y a-t-il une requête SQL qui pourrait générer la sortie désirée?

Ilya Sh, je ne pouvais pas vérifier si votre propre réponse à votre question était correcte. Mais voici mon approche.

A l'origine je pensais que cela pourrait être résolu avec une requête récursive, mais c'est là que toutes les boîtes sont de taille égale et garanties pour être plus grandes que les articles, et les articles sont indivisibles mais de taille variable (de sorte que la seule item est s'il peut être emballé dans la boîte actuelle, ou doit aller dans la case suivante).

Dans ce cas, nous avons des groupes d'éléments (une ligne spécifiant un type d'élément et une quantité) dans lesquels les éléments sont égaux en taille mais les groupes sont divisibles, et chaque groupe d'items peut donc être réparti sur un certain nombre de cases, et chaque boîte peut contenir des parties d'un certain nombre de groupes d'articles, dans une relation plusieurs-à-plusieurs entre des boîtes et des groupes d'articles.

Ma pensée est que chaque boîte, selon sa capacité, a un certain nombre de "slots" individuels (c.-à-d. Un volume d'espace) qui peuvent recevoir des articles individuels.

La façon dont j'ai abordé la solution consiste à utiliser une «table de nombres» pour augmenter les quantités de chaque boîte / groupe d'articles dans des cases et des éléments individuels – une rangée par boîte et une rangée par article. Sur ma machine j'ai une table appelée zx_numbers – mais j'ai inclus le code ci-dessous qui, à des fins d'illustration, élimine la dépendance sur cette table.

Une fois que nous avons normalisé datatables de cette manière – en étendant les boîtes dans leurs locations individuels, et en élargissant les groupes d'articles et les quantités récapitulatives en éléments individuels – chaque case et élément du lot entier est numéroté séquentiellement, puis les deux sont simplement joints sur ce numéro de séquence.

J'ai utilisé une FULL OUTER JOIN pour conserver les locations / éléments sans correspondance. Cela nous donne une solution très générale et adaptable au problème, que nous pouvons ensuite traiter de diverses manières pour get datatables spécifiques que nous voulons (dans ce cas, juste un résumé des combinaisons boîte-groupe d'articles).

La façon dont j'ai écrit la requête actuellement, les boîtes avec l'espace non rempli (ou les groupes d'articles qui laissent un rest après que toutes les boîtes ont été complètement remplies) sont laissées dans les résultats, et placées à la fin, mais elles peuvent être filtrées si non requirejses. 

 WITH item_groups(item_group_id, group_qty) AS ( select 'l1', 5 union all select 'l2', 12 union all select 'l3', 8 --union all select 'l4', 8 ) ,boxes(box_id, capacity) AS ( select 'o1',2 union all select 'o2', 8 union all select 'o3', 2 union all select 'o4', 5 union all select 'o5', 9 union all select 'o6', 5 ) ,zx_numbers(zx_number) AS ( --SELECT * FROM dbo.zx_numbers --I have a dedicated numbers table on my machine, but I've substituted a --manual sequence generator for the purposes of a self-contained demonstration SELECT (ones.n) + (10 * tens.n) + (100 * hundreds.n) AS zx_number FROM --range 0 to 999 (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS ones(n) ,(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS tens(n) ,(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS hundreds(n) ) ,items AS ( SELECT item_groups.* ,zx_number AS group_item_number ,ROW_NUMBER() OVER (ORDER BY item_group_id, zx_number) AS batch_item_number FROM item_groups INNER JOIN zx_numbers ON (zx_number BETWEEN 1 AND item_groups.group_qty) ) ,box_slots AS ( SELECT boxes.* ,zx_number AS box_slot_number ,ROW_NUMBER() OVER (ORDER BY box_id, zx_number) AS batch_slot_number FROM boxes INNER JOIN zx_numbers ON (zx_number BETWEEN 1 AND boxes.capacity) ) ,box_item_matches AS ( SELECT COALESCE(bxsl.batch_slot_number, itms.batch_item_number) AS slot_number ,bxsl.box_id ,bxsl.capacity ,bxsl.box_slot_number ,itms.item_group_id ,itms.group_qty ,itms.group_item_number FROM box_slots AS bxsl FULL OUTER JOIN items AS itms ON (bxsl.batch_slot_number = itms.batch_item_number) ) --SELECT * FROM box_item_matches SELECT box_id ,item_group_id FROM box_item_matches GROUP BY box_id, item_group_id ORDER BY IIF(box_id IS NULL OR item_group_id IS NULL, 1, 0) --ie NULLS LAST ,box_id ,item_group_id

Merci pour le conseil de commentaires la requête devrait ressembler à quelque chose comme 

 select f.id, b.num from (select z.*, sum(z.qty) over (order by num) as cap from @orders z ) b join (select f.*, sum(f.qty) over (order by id) as filler from @lots f ) f on f.filler - f.qty < b.cap and f.filler > b.cap - b.qty order by f.id, b.num