J'ai une table mysql "bf_monthly_bill" dans laquelle les données sur chaque utilisateur d'une autre table "bf_users" sont stockées chaque mois.
La structure de ma table bf_users est
SELECT bf_users.display_name,first_month.package_price firstt_bill,second_month.package_price second_bill,third_month.package_price third_bill from bf_monthly_bill AS first_month JOIN bf_monthly_bill AS second_month ON first_month.company_id=second_month.company_id JOIN bf_monthly_bill AS third_month ON first_month.company_id=third_month.company_id JOIN bf_users ON bf_users.id=first_month.company_id Where first_month.bill_month='Jan2k19' AND second_month.bill_month='Feb2k19' AND third_month.bill_month='Mar2k19'
La structure de bf_monthly_bill est
company_name | first_month_bill | second_month_bill |third_month_bill ABC | 500 | 200 | 500 DEF | 300 | 0 or Null | 300 GHI | 900 | 542 | 900 JKL | 0 or NUll | 0 or Null | 200
Je veux chaque nom d'entreprise avec son package_price de chaque mois de facturation, par exemple
company_id | package_price | bill_month 1 | 500 | Jan2k19 2 | 300 | Jan2k19 3 | 900 | Jan2k19 1 | 200 | Feb2k19 3 | 542 | Feb2k19 1 | 500 | Mar2k19 2 | 300 | Mar2k19 3 | 900 | Mar2k19 4 | 200 | Mar2k19
Quoi J'ai essayé est
id | display_name 1 | ABC 2 | DEF 3 | GHI 4 | JKL
Le problème avec cette requête est qu'elle ne me donne que les entreprises et leurs factures respectives qui sont présentes chaque mois
3 Réponses :
Vous pouvez essayer d'utiliser l'agrégation conditionnelle
select display_name,
sum(case when bill_month='Jan2k19' then package_price end) as first_bill,
sum(case when bill_month='Feb2k19' then package_price end) as second_bill,
sum(case when bill_month='Mar2k19' then package_price end) as third_bill
from
(
SELECT u.display_name,b.package_price,b.bill_month
from bf_users u inner join bf_monthly_bill b on u.id=b.company_id
)A group by display_name
@BensonOO si vous rencontrez des problèmes de performances avec cette requête lorsque les données augmentent dans le futur ... Petit indice de prise en compte de la sous-requête car MySQL sous 5.7 a tendance à matérialiser les requêtes utilisées dans la clause from ... MySQL: 5.7+ a tendance à exclure le sous-requête sur elle-même dans l'optimiseur.
@BensonOO a oublié de mentionner l'exécution de EXPLAIN sur certaines versions de MySQL peut afficher les réécritures SQL internes de l'optimiseur lorsque les réécritures se produisent hors de la source Ajouté: mais ne soyez pas surpris lorsque les JOINs sont convergés dans les anciennes jointures par virgule en interne
LEFT JOIN pour vous assurer que vous obtenez la liste complète des entreprises même s'il n'y a pas de facture pour aucun mois. GROUP BY pour générer un seul enregistrement par entreprise. CASE WHEN ou IF ELSE pour créer n colonnes et sélectionner les données de la ligne appropriée uniquement. Il peut s'agir de la première ou de n'importe quelle ligne, utilisez donc la fonction d'agrégation pour sélectionner des données même si elles se trouvent sur n'importe quelle ligne d'un groupe en définissant les autres valeurs de ligne comme NULL car la fonction d'agrégation exclut les valeurs nulles. SELECT
u.display_name,
SUM(IF( bill_month='Jan2k19', package_price, NULL)) as first_bill,
SUM(IF( bill_month='Feb2k19', package_price, NULL)) as second_bill,
SUM(IF( bill_month='Mar2k19', package_price, NULL)) as third_bill
FROM bf_users u
LEFT JOIN bf_monthly_bill b on u.id=b.company_id
GROUP BY u.display_name;
Essayez ceci ::
name first_month_bill second_month_bill third_month_bill ABC 500 200 500 DEF 300 (null) 300 GHI 900 542 900 JKL (null) (null) 200
Sortie ::
SELECT bu.name,
max(case when (bb.bill_month='Jan2k19') then bb.package_price else NULL end) as 'first_month_bill',
max(case when (bb.bill_month='Feb2k19') then bb.package_price else NULL end) as 'second_month_bill',
max(case when (bb.bill_month='Mar2k19') then bb.package_price else NULL end) as 'third_month_bill'
FROM bf_users bu
INNER JOIN bf_monthly_bill bb
ON bu.id = bb.bf_users_id GROUP BY bu.name;
Voici SQLFiddle démo