1
votes

Combinez plusieurs tables pour générer une vue

J'ai 4 tables, 3 pour l'argent entrant, 1 pour l'argent sortant

credittable1

uid | date | credit | debit | balance amount

credittable2

uid | amnt | dat
1   | 120  |12-04-2016
2   | 80   |25-02-2017
...and so on

credittable3

uid | amnt | dat
1   | 70   |03-03-2016
2   | 10   |21-03-2017
...and so on

débittable

uid | amnt | dat
1   | 60   |01-03-2016
2   | 20   |22-02-2017
...and so on

Je dois combiner les 4 tableaux pour ressembler à un livre de comptes de crédits et débits avec le montant du solde à cette transaction dans l'ordre de date pour chaque utilisateur. Par exemple, je veux imprimer le livre de compte de l'uid 1, puis toutes ses transactions doivent être imprimées comme un livre de comptes dans l'ordre de la date à laquelle la transaction de crédit ou de débit a eu lieu avec le montant du solde à cette transaction (tout comme un livret bancaire ).

Toutes les tables sont vraiment lourdes avec un grand nombre d'enregistrements, donc je recherche une solution rapide (de bonnes performances).

La mise en œuvre finale doit être effectuée dans Codeigniter

Un utilisateur peut obtenir 10 (ou n'importe quel nombre) transactions dans le tableau 1, et ainsi de suite au total les tables. Le nombre d'enregistrements pour chaque utilisateur dans chaque table peut varier.

Le résultat ressemblerait à ceci:

uid | amnt | dat
1   | 50   |31-02-2016
2   | 40   |22-03-2017
...and so on

Veuillez suggérer un moyen d'y parvenir. Je ne peux pas décider si cela doit être fait en utilisant des jointures ou une autre stratégie.

sql mysql

11 commentaires

Pourquoi avez-vous autant de tables de crédit?

Il semble qu'il ne s'agisse que d'une UNION des 4 tables, sauf que vous multipliez le montant par -1 dans le débitable .

Quelle version de MySQL utilisez-vous? Et y a-t-il des clés uniques / primaires dans les tables?

@stickybit Bonjour, toutes les tables ont une colonne de clé primaire: sno

@Barmar J'ai lu que UNION est lent. Pour un traitement rapide, JOINS doit être utilisé. toutes les tables sont vraiment lourdes avec un grand nombre d'enregistrements.

@Neetu Vous ne pouvez utiliser JOIN qu'en cas de relation entre les lignes. Quelle est la relation ici?

@Barmar Toutes les tables ont des enregistrements pour le même utilisateur, ses seulement 3 sources de revenus et 1 source de dépenses

Vous devez clarifier la question et montrer à quoi vous voulez que la vue résultante ressemble. Comment des lignes spécifiques sont-elles liées les unes aux autres? Il ne suffit pas d'avoir le même ID utilisateur, car il y aura plusieurs lignes dans chaque table avec le même ID utilisateur.

@Barmar J'ai ajouté une liste de colonnes pour la sortie. Oui, un utilisateur peut obtenir 10 (ou n'importe quel nombre) transactions dans la table 1, et ainsi de suite dans toutes les tables. Le nombre d'enregistrements pour chaque utilisateur dans chaque table peut varier.

Si vous deviez rejoindre toutes les tables de crédit, vous obtiendrez des colonnes de toutes, pas une seule colonne de crédit . C'est pourquoi il faut que ce soit un syndicat.

pensez à modifier votre schéma

3 Réponses :

1
votes

Vous pouvez unir tous les quatre tables et annuler les montants dans débittable:

SELECT uid, amt,      dat FROM credittable1 UNION ALL
SELECT uid, amt,      dat FROM credittable2 UNION ALL
SELECT uid, amt,      dat FROM credittable3 UNION ALL
SELECT uid, -1 * amt, dat FROM debittable

3 commentaires

@Barmar J'ai lu que UNION est lent. Pour un traitement rapide, JOINS doit être utilisé. toutes les tables sont vraiment lourdes avec un grand nombre d'enregistrements.

Les @Neetu union sont lents car ils doivent éliminer les données en double - union all ne le fait pas. De plus, il est inutile d'utiliser une jointure si elle ne vous donne tout simplement pas les données que vous recherchez.

Cette solution présente 2 problèmes: d'abord, elle affiche des valeurs négatives pour les débits, alors qu'un livret bancaire ne le fait pas. Deuxièmement, il ne montre pas d'équilibre dans chaque transaction. J'ai ajouté la sortie souhaitée dans la description.

0
votes

Un UNION ALL sur toutes les tables vous donne la base (et vous devriez envisager de reconcevoir le schéma et de n'utiliser qu'une seule de ces tables dès le début).

Pour l'équilibre, vous pouvez utilisez une sous-requête, qui obtient la somme des montants «précédents». «Précédente» signifie ici les réservations dont la date est inférieure ou égale à la date de la réservation actuelle. Comme il peut y avoir plus de réservations en une journée, nous avons besoin d'un deuxième critère pour les différencier. C'est là que sno entre en jeu - il doit également être inférieur ou égal à l'actuel. Pour nous assurer qu'un sno est unique sur l'ensemble de l'ensemble, pas seulement sur la table dont il provient, nous ajoutons un identifiant de table unique.

Dans MySQL 8.0, une somme fenêtrée peut être utilisée pour cela, ce qui rend les choses beaucoup plus faciles. Mais je suppose que vous êtes sur une version inférieure.

SELECT x.amnt,
       x.dat,
       (SELECT coalesce(sum(amnt), 0)
               FROM (SELECT concat('c1-', sno) sno
                            amnt,
                            dat
                            FROM credittable1
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('c3-', sno) sno,
                            amnt,
                            dat
                            FROM credittable2
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('c3-', sno) sno,
                            amnt,
                            dat
                            FROM credittable3
                            WHERE uid = 1
                     UNION ALL
                     SELECT concat('d1-', sno) sno,
                            -1 * amnt,
                            dat
                            FROM debittable
                            WHERE uid = 1) y
               WHERE y.dat <= x.dat
                     AND y.sno <= x.sno) bal
       FROM (SELECT concat('c1-', sno) sno,
                    amnt,
                    dat
                    FROM credittable1
                    WHERE uid = 1
             UNION ALL
             SELECT concat('c2-', sno) sno,
                    amnt,
                    dat
                    FROM credittable2
                    WHERE uid = 1
             UNION ALL
             SELECT concat('c3-', sno) sno,
                    amnt,
                    dat
                    FROM credittable3
                    WHERE uid = 1
             UNION ALL
             SELECT concat('d1-', sno) sno,
                    -1 * amnt,
                    dat
                    FROM debittable
                    WHERE uid = 1) x
       ORDER BY x.dat;

0 commentaires

0
votes

Je pense que je ferais ceci pour obtenir les données de base:

sum(debit + credit) over (partition by uid order by date)

Ensuite, vous pouvez utiliser des variables pour obtenir le solde:

select uid, date,
       (@b := if(@u = uid, @u + credit + debit,
                 if(@u := uid, credit + debit, credit + debit)
                )
       ) as balance
from (select uid, date, sum(credit) as credit, sum(debit) as debit
      from ((select uid, dat amt as credit, 0 as debit
             from credittable1
            ) union all
            (select uid, dat amt as credit, 0 as debit
             from credittable2
            ) union all
            (select uid, dat amt as credit, 0 as debit
             from credittable3
            ) union all
            (select uid, dat, 0, - amt
             from debittable
            )
           ) ad
      group by uid, date
      order by uid, date
     ) ud cross join
     (select @u := -1, @b := 0) balance;

Ou dans MySQL 8+, vous pouvez utiliser une somme cumulée:

select uid, date, sum(credit) as credit, sum(debit) as debit
from ((select uid, dat amt as credit, 0 as debit
       from credittable1
      ) union all
      (select uid, dat amt as credit, 0 as debit
       from credittable2
      ) union all
      (select uid, dat amt as credit, 0 as debit
       from credittable3
      ) union all
      (select uid, dat, 0, - amt
       from debittable
      )
     ) ad
group by uid, dat
order by uid, date;

0 commentaires