MySQL Union 结果是一列而不是行

Question

我需要选择两个求和字段，其中一个 sum() 的结果等于 male，而另一个 sum() 的结果等于 female

我目前的查询是这样的。

(SELECT 'Male' AS Gender, FLOOR(SUM( f.factTotalCost )) AS Spent, t.timeYear AS Year
FROM fact_usage f 
LEFT JOIN dim_time t ON t.timeID = f.timeID 
LEFT JOIN dim_customer c ON c.customerID = f.customerID WHERE t.timeYear IS NOT NULL AND c.customerGender = 'Male' GROUP BY t.timeYear 
ORDER BY t.timeYear ASC
)
UNION
(
SELECT 'Female' AS Gender, FLOOR(SUM( f.factTotalCost )) AS Spent, t.timeYear AS Year
FROM fact_usage f 
LEFT JOIN dim_time t ON t.timeID = f.timeID 
LEFT JOIN dim_customer c ON c.customerID = f.customerID WHERE t.timeYear IS NOT NULL AND c.customerGender = 'Female' GROUP BY t.timeYear
ORDER BY t.timeYear ASC

它返回这个

 +--------+----------+------+
    | Gender | Spent    | Year |
    +--------+----------+------+
    | Male   | 21180082 | 1990 |
    | Male   | 21415296 | 1991 |
    | Male   | 21401956 | 1992 |
    | Male   | 21104179 | 1993 |
    | Male   | 21209182 | 1994 |
    | Male   | 21232913 | 1995 |
    | Male   | 21289882 | 1996 |
    | Male   | 21049297 | 1997 |
    | Male   | 21116410 | 1998 |
    | Male   | 21318029 | 1999 |
    | Male   | 21346531 | 2000 |
    | Male   | 21422877 | 2001 |
    | Male   | 21496714 | 2002 |
    | Male   | 21299382 | 2003 |
    | Male   | 21277839 | 2004 |
    | Male   | 21436301 | 2005 |
    | Male   | 21415851 | 2006 |
    | Male   | 21335662 | 2007 |
    | Male   | 21097699 | 2008 |
    | Male   | 21459088 | 2009 |
    | Male   | 21266998 | 2010 |
    | Male   | 21256936 | 2011 |
    | Male   | 21233116 | 2012 |
    | Male   | 21329422 | 2013 |
    | Male   | 21176809 | 2014 |
    | Male   | 21197822 | 2015 |
    | Female | 21119160 | 1990 |
    | Female | 21184234 | 1991 |
    | Female | 20984759 | 1992 |
    | Female | 21030674 | 1993 |
    | Female | 20810592 | 1994 |
    | Female | 20846809 | 1995 |
    | Female | 20847376 | 1996 |
    | Female | 20823927 | 1997 |
    | Female | 20757323 | 1998 |
    | Female | 20933808 | 1999 |
    | Female | 21021068 | 2000 |
    | Female | 21007603 | 2001 |
    | Female | 21155283 | 2002 |
    | Female | 21302091 | 2003 |
    | Female | 21269045 | 2004 |
    | Female | 21008866 | 2005 |
    | Female | 21060483 | 2006 |
    | Female | 21056181 | 2007 |
    | Female | 21039697 | 2008 |
    | Female | 21180452 | 2009 |
    | Female | 21035352 | 2010 |
    | Female | 20918044 | 2011 |
    | Female | 21113909 | 2012 |
    | Female | 21056023 | 2013 |
    | Female | 20976295 | 2014 |
    | Female | 21017583 | 2015 |
    +--------+----------+------+

但是我需要它来返回

+--------+----------+------+
|Female  | Male     | Year |
+--------+----------+------+
|4848448 | 21180082 | 1990 |
|3888283 | 21415296 | 1991 |
|3323222 | 21401956 | 1992 |
|3232322 | 21104179 | 1993 |
|4444544 | 21209182 | 1994 |
|3434343 | 21232913 | 1995 |
|3434345 | 21289882 | 1996 |
|2325545 | 21049297 | 1997 |
|6556565 | 21116410 | 1998 |
|3434343 | 21318029 | 1999 |
|3434343 | 21346531 | 2000 |
|3434343 | 21422877 | 2001 |
|5454546 | 21496714 | 2002 |
|6767545 | 21299382 | 2003 |
|4557556 | 21277839 | 2004 |
|4547774 | 21436301 | 2005 |
|4545433 | 21415851 | 2006 |
|3435535 | 21335662 | 2007 |
|3443424 | 21097699 | 2008 |
|2323224 | 21459088 | 2009 |
|2325353 | 21266998 | 2010 |
|3342434 | 21256936 | 2011 |
|3444333 | 21233116 | 2012 |
|2324444 | 21329422 | 2013 |
|3434334 | 21176809 | 2014 |
|6454564 | 21197822 | 2015 |
+--------+----------+------+

我真的很坚持，并且已经花了几个小时试图让它工作:(

Answer 1

我认为您只需要条件聚合:

SELECT t.timeYear AS Year,
       SUM(CASE WHEN c.customerGender = 'Male' THEN f.factTotalCost ELSE 0 END) as male,
       SUM(CASE WHEN c.customerGender = 'Female' THEN f.factTotalCost ELSE 0 END) as female
FROM fact_usage f JOIN
     dim_time t
     ON t.timeID = f.timeID JOIN
     dim_customer c
     ON c.customerID = f.customerID
WHERE t.timeYear IS NOT NULL
GROUP BY t.timeYear 
ORDER BY t.timeYear ASC;

外连接是不必要的，因为 WHERE 子句无论如何都会将它们变成内连接。