php - 通过 join 和/或 union 将两个 MySQL 查询简化为一个

Question

我有两个疑问:

(SELECT b904_Vasarlas.PrintedFigure as Image, 
    b904_Vasarlas.ProductColor as Color, 
    SUM(b904_Vasarlas.QTY) as QTY 
FROM `b904_Vasarlas` 
GROUP BY 1, 2) 
ORDER by 1, 2

具有良好的输出:

Image;Color;QTY
"villamos","blue","5"
"villamos","pink","5"
"virag","blue","5"
"virag","pink","5"
"virag2","blue","5"
"virag2","pink","5"
"vitorlas","blue","5"
"vitorlas","pink","5"
"vonat","blue","5"
"vonat","pink","5"
"zaszlo","blue","5"
"zaszlo","pink","5"
"zsiraf","blue","15"
"zsiraf","pink","15"

和

(SELECT b904_Eladas.PrintedFigure as Image, 
    b904_Eladas.ProductColor as Color, 
    SUM(b904_Eladas.QTY) as QTY 
FROM `b904_Eladas` 
GROUP BY 1, 2) 
ORDER by 1, 2

具有良好的输出:

Image;Color;QTY
"golya","blue","20"
"golya","pink","10"
"hajokormany","blue","20"
"hajokormany","pink","10"
"macska","blue","10"
"macska","pink","10"
"malac","blue","10"
"malac","pink","10"
"villamos","blue","3"
"villamos","pink","3"
"virag","blue","3"
"virag","pink","3"
"virag2","blue","4"
"vitorlas","blue","5"
"vitorlas","pink","5"
"vonat","blue","5"
"vonat","pink","5"
"zaszlo","blue","15"
"zaszlo","pink","5"
"zsiraf","blue","16"
"zsiraf","pink","11"

如何通过一个按图像和颜色列分组的 mysql 查询来获取两个表的 QTY 列的差异？

<小时/>

最后我使用了这段较长的代码，但我认为我可以通过更简单的一个查询字符串来解决问题。

$Eladasok = array();
$ElLoop = 0;
$GyartasQuery = '(SELECT ' . $DBConnection[1] . '_Vasarlas.PrintedFigure as RaktarKep, ' . $DBConnection[1] . '_Vasarlas.ProductColor as RaktarSzin, SUM(' . $DBConnection[1] . '_Vasarlas.QTY) as RaktarQTY  FROM `' . $DBConnection[1] . '_Vasarlas` GROUP BY 1, 2) ORDER by 1, 2';
$EladasQuery = '(SELECT ' . $DBConnection[1] . '_Eladas.PrintedFigure as EladasKep, ' . $DBConnection[1] . '_Eladas.ProductColor as EladasSzin, SUM(' . $DBConnection[1] . '_Eladas.QTY) as EladasQTY  FROM `' . $DBConnection[1] . '_Eladas` GROUP BY 1, 2) ORDER by 1, 2';
$GyartasResult = mysql_query($GyartasQuery);
$EladasResult = mysql_query($EladasQuery);

while(false !== $EladasContent = mysql_fetch_assoc($EladasResult)) {                        
    $new_array[] = $EladasContent;
    $Eladasok[$new_array[$ElLoop][EladasKep] . '-' . $new_array[$ElLoop][EladasSzin]] = $EladasContent[EladasQTY];
    $ElLoop++;
}

while(false !== $GyartasContent = mysql_fetch_assoc($GyartasResult)) {
    if (($GyartasContent[RaktarQTY] - $Eladasok[$GyartasContent[RaktarKep] . '-' . $GyartasContent[RaktarSzin]]) <= 0) {
        $Cellcolour = ' bgcolor="#DDDDDD"';
        $CellHighlight = ' bgcolor="#FFBBBB"';
    } else {
        $Cellcolour = '';
        $CellHighlight = '';
    }

    echo'
    <tr><td align=center' . $CellHighlight . '>'.++$RoNo.'</td>
    <td' . $Cellcolour . '>' . $GyartasContent[RaktarKep] . ' - ' . $GyartasContent[RaktarSzin] .  '</td>
    <td' . $Cellcolour . '>' . intval($GyartasContent[RaktarQTY]) . '</td>
    <td' . $Cellcolour . '>' . intval($Eladasok[$GyartasContent[RaktarKep] . '-' . $GyartasContent[RaktarSzin]]) . '</td>
    <td' . $Cellcolour . '>' .  intval($GyartasContent[RaktarQTY] - $Eladasok[$GyartasContent[RaktarKep] . '-' . $GyartasContent[RaktarSzin]]). 
    '</td></tr>';
}
echo '</tr></table></div>';

Answer 1

数据库1的内容:

从b904_vasarlas中选择PrintedFigure、ProductColor、QTY

(Name, color, QTY)"villamos","blue","5""villamos","pink","5""virág","blue","5""virág","pink","5""virág2","blue","5""virág2","pink","5""vitorlás","blue","5""vitorlás","pink","5""vonat","blue","5""vonat","pink","5""zsiráf","blue","5""zsiráf","pink","5""zászló","blue","5""zászló","pink","5""zsiráf","blue","10""zsiráf","pink","10"

The content of database2:

SELECT PrintedFigure, ProductColor, QTY FROM b904_eladas

(Name, color, QTY)"vitorlás","blue","5""vitorlás","pink","5""vonat","blue","5""vonat","pink","5""zsiráf","blue","5""zsiráf","pink","5""zászló","blue","5""zászló","pink","5""zsiráf","blue","5""zászló","blue","10""zsiráf","blue","6""zsiráf","pink","6""gólya","blue","10""gólya","pink","10""hajókormány","blue","10""hajókormány","pink","10""macska","blue","10""macska","pink","10""malac","blue","10""malac","pink","10""villamos","blue","3""villamos","pink","3""virág","blue","3""virág","pink","3""virág2","blue","4""gólya","blue","10""hajókormány","blue","10"

I need the difference of column called QTY when PrintedFigure AND the product color is same in booth databases.

Your result is wrong:

Image,Color,QtyV,QtyE,Qty,QtyOtherWay "gólya","blue","0","0","-20","-20""gólya","pink","0","0","-10","-10""hajókormány","blue","0","0","-20","-20""hajókormány","pink","0","0","-10","-10""macska","blue","0","0","-10","-10""macska","pink","0","0","-10","-10""malac","blue","0","0","-10","-10""malac","pink","0","0","-10","-10""villamos","blue","5","5","2","2""villamos","pink","5","5","2","2""virág","blue","5","5","2","2""virág","pink","5","5","2","2""virág2","blue","5","5","1","1""virág2","pink","5","5","5","5""vitorlás","blue","5","5","0","0""vitorlás","pink","5","5","0","0""vonat","blue","5","5","0","0""vonat","pink","5","5","0","0""zászló","blue","10","10","-5","-5""zászló","pink","5","5","0","0""zsiráf","blue","45","45","13","13""zsiráf","pink","30","30","8","8"

I have exactly same problem before when I tried using join and union.

The result is good with code using two queries and while loop for array creation:

#   Name and color      Difference1   villamos - blue     22   villamos - pink     23   virág - blue        24   virág - pink        25   virág2 - blue       16   virág2 - pink       57   vitorlás - blue     08   vitorlás - pink     09   vonat - blue        010  vonat - pink        011  zászló - blue       -1012  zászló - pink       013  zsiráf - blue       -114  zsiráf - pink       4

I am asking the solution of one query because I want to cancel using this loop:

            while(false !== $EladasContent = mysql_fetch_assoc($EladasResult)) {                        
                $new_array[] = $EladasContent;
                $Eladasok[$new_array[$ElLoop][EladasKep] . '-' . $new_array[$ElLoop][EladasSzin]] = $EladasContent[EladasQTY];
                $ElLoop++;
            }