Je travaille sur un projet pour afficher des informations sur le montant dans un site Web. Et je veux demander comment combiner deux tables en SQL et ses valeurs. Je donne un exemple de tableau Tableau 1 id date montant1 ---...

0
Mehedi Hasan Siam 15 mars 2021 à 08:24

1 réponse

Meilleure réponse
SELECT dates.`date`, 
       COALESCE(data21.amount1, 0) amount1, 
       COALESCE(data22.amount2, 0) amount2
FROM ( SELECT `date`, row_number11 row_number
       FROM ( SELECT table1.*, 
                     @row_number11 := CASE WHEN @date11 = table1.`date`
                                           THEN @row_number11 + 1
                                           ELSE 1 END row_number11,
                     @date11 := table1.`date` date1
              FROM table1
              CROSS JOIN ( SELECT @date11:=NULL, @row_number11:= 0 ) variables
              ORDER BY table1.`date`, table1.id
            ) data11
       UNION 
       SELECT `date`, row_number12 row_number
       FROM ( SELECT table2.*, 
                     @row_number12 := CASE WHEN @date12 = table2.`date`
                                           THEN @row_number12 + 1
                                           ELSE 1 END row_number12,
                     @date12 := table2.`date` date2
              FROM table2
              CROSS JOIN ( SELECT @date12:=NULL, @row_number12:= 0 ) variables
              ORDER BY table2.`date`, table2.id
            ) data12
       ) dates
LEFT JOIN ( SELECT table1.*, 
                   @row_number21 := CASE WHEN @date21 = table1.`date`
                                    THEN @row_number21 + 1
                                    ELSE 1 END row_number21,
                   @date21 := table1.`date` date1
            FROM table1
            CROSS JOIN ( SELECT @date21:=NULL, @row_number21:= 0 ) variables
            ORDER BY table1.`date`, table1.id
          ) data21 ON dates.`date` = data21.`date` 
                  AND dates.row_number = data21.row_number21
LEFT JOIN ( SELECT table2.*, 
                   @row_number22 := CASE WHEN @date22 = table2.`date`
                                    THEN @row_number22 + 1
                                    ELSE 1 END row_number22,
                   @date22 := table2.`date` date2
            FROM table2
            CROSS JOIN ( SELECT @date22:=NULL, @row_number22:= 0 ) variables
            ORDER BY table2.`date`, table2.id
          ) data22 ON dates.`date` = data22.`date` 
                  AND dates.row_number = data22.row_number22
ORDER BY `date`

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=d416718749843f0f8b8973855cbb93ed

PS. Je ne vois pas comment faire cela plus efficace / moins complexe sur votre ancienne version de MySQL. Je vous recommande fortement de mettre à niveau MySQL vers la version actuelle.

1
Akina 15 mars 2021 à 06:54