SQL: regroupement de 2 tables 1 avec une jointure, union, et alors?
J'ai 5 tables:
clients
id - nom
p_orders
id - id_customer - code - date
p_items
id - id_order - description - prix
et h_orders et h_items, qui sont exactement de la copie de p_orders et p_items.
Lorsque la p_ tables de rejoindre un grand nombre de lignes, je me déplace le plus ancien de la h_ tables.. ils ont dû, comme l'histoire.
Donc, mon problème est: comment faire pour récupérer les données à partir de l'p_ tables et h_ de les considérer comme un seul et unique tableau?
Par exemple, je veux récupérer le nombre de commandes de chaque client, et le prix total (de toutes les commandes du client), et j'utilise cette requête:
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
ORDER BY
customer.id
il fonctionne juste pour un ensemble de tables (p_ ou h_)..mais je les veux tous les deux.
J'ai essayé d'utiliser une UNION:
(
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
)
UNION
(
SELECT
customer.id,
customer.name,
count(DISTINCT h_orders.id) AS num_orders,
sum(h_items.price) AS total_money
FROM
customer
INNER JOIN h_orders ON h_orders.id_customer = customer.id
INNER JOIN h_items ON h_items.id_order = h_orders.id
GROUP BY
customer.id,
customer.name,
h_orders.id_customer
)
ORDER BY id ASC
Celui-ci fonctionne, mais si un client des commandes à la fois dans la p_ tables et dans le h_ tables, je vais avoir 2 lignes pour ce client avec 2 différents num_orders et total_money (respectivement en provenance de p_ tables et h_ tables)
J'ai essayé d'ajouter un GROUPE à l'id de l'extérieur de l'union:
(
--SELECT 2
)
UNION
(
--SELECT 1
)
GROUP BY id
ORDER BY id ASC
mais l'échec de la requête avec d'ERREUR: erreur de syntaxe sur ou près de "GROUPE" à caractère 948, semblent comme GROUPE PAR ne peut pas être utilisé de cette façon.
Toute suggestion?
EDIT:
Pour uriDium, oui, toutes les tables ont la colonne id comme clé primaire, et la visée des champs (aka p_orders.id_customer) sont des clés étrangères.
Ici le test db structure de vidage (j'ai ajouté quelques indices et les clés étrangères après la création de la table, mais je ne pense pas que cela signifie quelque chose):
CREATE TABLE customer (
id serial NOT NULL,
name character(50)
);
CREATE TABLE p_orders (
id serial NOT NULL,
id_customer integer NOT NULL,
date date DEFAULT now(),
code character(5)
);
CREATE TABLE p_items (
id serial NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE TABLE h_orders (
id integer NOT NULL,
id_customer integer NOT NULL,
date date,
code character(5)
);
CREATE TABLE h_items (
id integer NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE UNIQUE INDEX id_h_orders ON h_orders USING btree (id);
CREATE INDEX id_h_o_c ON h_orders USING btree (id_customer);
CREATE UNIQUE INDEX id_items_h ON h_items USING btree (id);
CREATE INDEX id_ordinr_dsve ON h_items USING btree (id_order);
ALTER TABLE ONLY customer
ADD CONSTRAINT customer_pkey (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT p_orders_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_items
ADD CONSTRAINT p_items_pkey PRIMARY KEY (id);
ALTER TABLE ONLY stats
ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY p_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;
OriginalL'auteur Strae | 2009-05-22
Vous devez vous connecter pour publier un commentaire.
Vous devriez probablement créer des vues sur les deux tableaux:
Maintenant vous pouvez simplement adhérer à ces points de vue. J'ai inclus les types (P & H) de sorte que vous savez ce que le "id" de la colonne se réfère désormais à la. Si les identifiants dans vos deux tables ("h" et "p" peut avoir les doublons, puis vous aurez à se joindre à la table Commandes à droite dans la All_Order_Items vue. Sinon, vous aurez beaucoup de mal à intégrer entre les deux points de vue. Nous espérons que votre id colonnes sont conçus de façon intelligente et pas seulement auto-incrmenting ou des colonnes d'identité.
"Nous espérons que votre id colonnes sont conçus de façon intelligente et pas seulement auto-incrmenting ou des colonnes d'identité." L'identité id sont une façon intelligente de la conception dans la plupart des applications d'entreprise.
Que voulez-vous dire exactement, HLGEM? Vous pensez que je fais mal (ou inefficent) l'utilisation de l'id? veuillez expliquer.. je suis ici pour apprendre!
Puisque vous êtes en gardant les valeurs d'ID, vous devez être en bonne forme
Il semble que les id régime est "correctement" conçu: l'auto-incrément de la p*, puis conservé lors du passage à la h*. Lire "correctement", bien sûr, comme "comment je le ferais"... 😉
OriginalL'auteur Tom H
Vous pouvez essayer ceci:
OriginalL'auteur Jimmie R. Houts
Créer une vue avec l'union des deux requêtes, mais sans les fonctions d'agrégation. Utilisez l'Union de Tous que le même enregistrement n'est pas dans les deux tables, et vous n'avez pas besoin du serveur de perdre du temps à la recherche pour voir que.Vous probaly ont d'autres moments, vous souhaitez accéder à deux tables dans une requête.
Puis écrire votre requête à l'aide de la vue.
afficher le code serait quelque chose comme (vous pouvez d'autres champs pour d'autres objectifs:
puis l'appel à votre requête serait quelque chose comme (rien de tout cela est l'objet de tests peuvent avoir besoin d'ajustement)
OriginalL'auteur HLGEM
Autant que je sache, SQL Server doit automatiquement en éliminant les doublons. À l'aide de l'UNION TOUT va inclure les doublons. J'imagine que SQL Server serait d'utiliser les clés primaires comme un moyen de travailler sur ce qu'est un doublon. Sont les clés primaires sur ces tables composé du même type de données et est ID 1 dans votre p table ID 1 dans votre h?
Oui - Édité avec la base de données de test tructure
OriginalL'auteur uriDium
La façon la plus simple de faire ce que vous êtes à la recherche à serait de créer des vues (dire "a_orders" et "a_items"). Le point de vue voudrais juste être définie comme:
Si vous supprimez des lignes de a_orders que vous insérez à h_orders (donc un ordre donné ne serait pas dans les deux tableaux), il serait un peu plus efficace d'utiliser de l'UNION, au lieu de l'UNION.
OriginalL'auteur Eric Petroelje
Merci pour toutes les réponses, les gars..
À la fois les points de vue de manière "et le" sous-requête voie " par Jimmie R. Hrs fonctionne parfaitement, peut-être les vues sont tout simplement plus pratique à utiliser.. et les deux devraient prendre le même temps (ou pas?)
Donc je vais marquer comme meilleure réponse, le premier sur les points de vue.
De toute façon, si je peux, puis-je vous demander si la structure et les indices que j'ai utilisé sont une bonne ou pourrait être optimisée?
OriginalL'auteur Strae