mysql - 获取两个日期之间的数据

Question

我有这两个表

第一个是费用表，第二个是费用名称

Exp_Type(第一个表)是费用名称(第二个表)，因为 2 是食物

我正在尝试根据费用类型对费用进行分组并获取特定日期之间的数据。

这是我尝试过的方法，但行不通。

select
(select 
(select name from EXPENSENAME where id=EXP_TYPE)as ExpenseType,
sum(PRICE) as cost
from EXPENSES WHERE USERID=1  GROUP BY EXPENSES.EXP_TYPE),
[date]
from EXPENSES where [date] BETWEEN '10-09-2015' and '10-18-2015 23:59:59'

And

select 
(select name from EXPENSENAME where id=EXP_TYPE)as ExpenseType,
sum(PRICE) as cost,
date
from EXPENSES WHERE USERID=1 and DATE BETWEEN '01/10/2015' and '29/10/2015' GROUP BY EXPENSES.EXP_TYPE

没有日期，我通过此查询获得结果，但我需要某些日期之间的相同数据，请帮助

select 
(select name from EXPENSENAME where id=EXP_TYPE)as ExpenseType,
sum(PRICE) as cost
from EXPENSES WHERE USERID=1  GROUP BY EXPENSES.EXP_TYPE

Answer 1

您想要将表格连接在一起

SELECT en.name as ExpenseType, SUM(e.price) as cost
FROM expenses e
JOIN expensename en ON en.id = e.exp_type
WHERE e.date BETWEEN '10-09-2015' and '10-18-2015'
GROUP BY en.name

这应该给你每个名字的费用

您当前的查询很糟糕......这就是原因

SELECT (SELECT ... FROM ... WHERE ... ) as ...

这是创建一个相关子查询，该子查询对父选择的每一行执行一次。意思是如果您有一个包含 4 行的表 (SELECT ... FROM ... WHERE ... ) 将执行 4 次扫描 16 行(假设来自同一个表)是一种非常非常糟糕的获取数据的方式...如果你有一百万行...好吧，算一下，这是一个坏主意