mysql - sql查询交叉表

我有一个数据库，有以下表格：
具有以下列的customer_info表：

custID | storeName     | accName
  1    |  bayport      | name one
  2    |  plainfield   | name two
  3    |  bayport      | name three

处理具有以下列的表：

dealID | dealDate     | custID
  1    | 2012-10-15   |   1
  2    | 2012-11-25   |   2
  3    | 2012-12-17   |   3
  4    | 2012-12-17   |   1

包含以下列的phone_details表

phone_ID  | instore  | online  | received | dealID
    1     |    1     |    0    |    0     |   1
    2     |    1     |    0    |    0     |   1
    3     |    0     |    1    |    1     |   1
    4     |    0     |    1    |    0     |   2
    5     |    0     |    1    |    0     |   3
    6     |    0     |    1    |    1     |   3
    7     |    1     |    0    |    1     |   4
    8     |    1     |    0    |    1     |   4

发票详细信息

payment_ID   | due  | paid | tender | dealID
      1      | 1000 | 500  | cash   |    1
      2      | 500  | 100  | credit |    2
      3      | 200  | 200  | cash   |    3
      4      | 350  | 350  | debit  |    4

我如何查询这个表，以便在某个日期的结果是这样的？
例如，2012-10-15-2012-11-25

               | bayport |   plainfield
# of instore   |    2    |       0
# of online    |    1    |       1
total          |    3    |       1
# of received  |    1    |       0
total amount   |  1000   |      500

最佳答案

与@MichaelBerkowski's excellent answer相比，如果您能够容忍以bayport/plainfield为行、instore/online为列的数据（根据上面的my comment，您只需执行以下操作：

SELECT    c.storeName,
          SUM(p.instore) AS `# of instore`,
          SUM(p.online ) AS `# of online`
FROM      customer_info c
LEFT JOIN deals         d
       ON d.custID    = c.custID
      AND d.dealDate  = '2012-10-15'
LEFT JOIN phone_details p USING (dealID)
GROUP BY  storeName

在sqlfiddle上查看。