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

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

这似乎令人困惑我有一个购买表和销售表，以及可以是借方和贷方的跨栏。看，我很困惑。为什么不只是有一张桌子？ – Strawberry

我需要两个表为我的项目，这是为例，所以我使它如此简单，：D –

为什么你需要两个表？您可以按类型推迟交易。 –

答

'没办法让它成为一张桌子' - 这种说法是坦率的荒谬。

反正...

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

和@strawberry再次击败我，仍然努力寻找更好的解决方案，也许我需要改变我的昵称为[tag：apple]？ – davejal

好，非常有帮助的答案。非常感谢你草莓。 –

@strawberry，在这种情况下只有很少的记录，但是如果记录很多，哪种解决方案工作得更快？ – davejal

答

已经解决你的问题，但也许这一个可以帮助其他人在另一种情况

select date, sum(sumbuy) as totalbuy, sum(sumsell) as totalsell from (SELECT DATE_FORMAT(buy.date, '%Y-%m-%d') date 
    , SUM(case when trans = 'credit' then total else 0 end) as sumbuy 
    , SUM(case when trans = 'debit' then total else 0 end) as sumsell 
    FROM buy 
    GROUP 
    BY DATE_FORMAT(buy.date, '%d') 
union 
SELECT DATE_FORMAT(sell.date, '%Y-%m-%d') date 
    , SUM(case when trans = 'credit' then total else 0 end) as sumbuy 
    , SUM(case when trans = 'debit' then total else 0 end) as sumsell 
    FROM sell 
    GROUP 
    BY DATE_FORMAT(sell.date, '%d')) a 
    group by date

我注意到你有2020000 sumsell的，不应该是还是你会嘿嘿，不想从购买表中借记交易？

如果你只是想从销售表从买表的借方和唯一的信用，你可以重写查询是这样的：

select date, sum(sumbuy) as totalbuy, sum(sumsell) as totalsell from (SELECT DATE_FORMAT(buy.date, '%Y-%m-%d') date 
, SUM(case when trans = 'credit' then total else 0 end) as sumbuy 
, SUM(case when trans = 'debit' then total else 0 end) as sumsell 
    FROM buy 
where trans = 'credit' 
GROUP 
BY DATE_FORMAT(buy.date, '%d') 
union 
SELECT DATE_FORMAT(sell.date, '%Y-%m-%d') date 
, SUM(case when trans = 'credit' then total else 0 end) as sumbuy 
, SUM(case when trans = 'debit' then total else 0 end) as sumsell 
FROM sell 
where trans = 'debit' 
GROUP 
BY DATE_FORMAT(sell.date, '%d')) a 
group by date

sqlfiddle

Php mysql根据日期加入群组

相关推荐