php - 对另一个表中日期之间的表中的数据求和

Question

我有这两个表，它们通过 itemid 列相互关联。正如我在 data_table 的第一个示例中所示，我设法对不同的 itemid 值求和。

现在我想将这个总和与另一个表中的特定日期相关联，但我做不到。请问有人能帮帮我吗？

项目表

+--------+-----------+------------+| itemid | published | publish_up |+--------+-----------+------------+|      1 |         1 | 02-01-2014 |+--------+-----------+------------+

data_table

+--------+---------+------+| itemid | fieldid | data |+--------+---------+------+|      1 |      27 |    5 ||      1 |      28 |   10 ||      1 |      29 |    5 |+--------+---------+------+

This query is ok:

SELECT (SELECT SUM(data) FROM data_table WHERE fieldid='27')
     + (SELECT SUM(data) FROM data_table WHERE fieldid='28')
     + (SELECT SUM(data) FROM data_table WHERE fieldid='29')

但不在日期之间:

SELECT (SELECT SUM(data) FROM data_table WHERE fieldid='27')
     + (SELECT SUM(data) FROM data_table WHERE fieldid='28')
     + (SELECT SUM(data) FROM data_table WHERE fieldid='29')    
WHERE  (publish_up BETWEEN '2014-01-01 00:00:00' AND '2014-01-31 00:00:00') 
FROM   items_table

Answer 1

SELECT   itemid, SUM(data)
FROM     data_table JOIN items_table USING (itemid)
WHERE    publish_up >= '2014-01-01'
     AND publish_up <  '2014-02-01'
GROUP BY itemid