php - PHP MySQL和与加入按日期分组

你好，我需要帮助来解决我的代码。
我试过下面的问题；

SELECT DATE_FORMAT(buy.date, '%Y-%m-%d') date
     , SUM(buy.total) sumbuy
     , SUM(sell.total) sumsell
  FROM buy
  JOIN sell
    ON DATE_FORMAT(buy.date, '%Y-%m-%d') = DATE_FORMAT(sell.date,'%Y-%m-%d')
 WHERE buy.trans = 'credit'
   AND sell.trans= 'debit'
 GROUP
    BY DATE_FORMAT(buy.date, '%d')

结果是：

   date        sumbuy       sumsell
 ------------------------------------
 2017-02-01    1560000      8080000

我期望的是：

        date        sumbuy       sumsell
     ------------------------------------
     2017-02-01     390000       2020000

这里是完整的桌子
购买

total           trans            date
-----------------------------------------------
140000         credit       2017-02-01 04:31:00
50000          credit       2017-02-01 04:32:00
190000         debit        2017-02-01 04:33:00
50000          credit       2017-02-01 04:34:00
150000         credit       2017-02-01 04:35:00

卖

    total         trans         date
   ------------------------------------------
   120000         debit    2017-02-01 04:31:00
   300000         debit    2017-02-01 04:32:00
   800000         debit    2017-02-01 04:33:00
   800000         debit    2017-02-01 04:35:00

请任何人帮我解决这个问题。

最佳答案

“也没办法让它变成一张桌子”——这句话实在太荒谬了。
不管怎样。。。

DROP TABLE IF EXISTS buy;

CREATE TABLE buy
(total INT NOT NULL
,trans enum('credit','debit')
,date DATETIME NOT NULL);

INSERT INTO buy VALUES
(140000,'credit','2017-02-01 04:31:00'),
( 50000,'credit','2017-02-01 04:32:00'),
(190000,'debit' ,'2017-02-01 04:33:00'),
( 50000,'credit','2017-02-01 04:34:00'),
(150000,'credit','2017-02-01 04:35:00');

DROP TABLE IF EXISTS sell;

CREATE TABLE sell
(total INT NOT NULL
,trans enum('credit','debit')
,date DATETIME NOT NULL);

INSERT INTO sell VALUES
(120000,'debit','2017-02-01 04:31:00'),
(300000,'debit','2017-02-01 04:32:00'),
(800000,'debit','2017-02-01 04:33:00'),
(800000,'debit','2017-02-01 04:35:00');

SELECT DATE(date) date
     , SUM(CASE WHEN type = 'buy' AND trans = 'credit' THEN total END) sumbuy
     , SUM(CASE WHEN type = 'sell' AND trans = 'debit' THEN total END) sumsell
  FROM
     ( SELECT *
            ,'buy' type
         FROM buy
        UNION
          ALL
       SELECT *
            ,'sell'
         FROM sell
     ) x
 GROUP
    BY DATE(date);
+------------+--------+---------+
| date       | sumbuy | sumsell |
+------------+--------+---------+
| 2017-02-01 | 390000 | 2020000 |
+------------+--------+---------+
1 row in set (0.02 sec)

SQLfiddle

关于php - PHP MySQL和与加入按日期分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42436513/