MYSQL 查询以获取日期差异 [英] MYSQL Query to get date difference
本文介绍了MYSQL 查询以获取日期差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含以下列和数据的表事务
i have a table Transaction with following column and data
id transaction_date trans_type account_id agents_id transaction_date price miles
1 2012-02-08 Buy 1 1 2010-02-08 0.016 12000
2 2012-03-01 Sell 2 2 2012-03-10 0.256 -2000
3 2012-03-27 Buy 3 3 2012-03-27 0.256 10000
4 2012-03-28 Sell 4 4 2012-03-28 0.589 -11000
5 2012-03-29 Buy 5 5 2012-03-29 0.87 25000
6 2012-03-29 Sell 6 6 2012-02-29 0.879 -12000
7 2012-04-01 Sell 7 7 2012-04-01 0.058 -15000
Account Table
id Program_id
1 1
2 1
3 2
Program table
id Abbreviation
1 AA
2 AC
Agents table
id Name
1 Bob
2 Ben
我想获得首次销售日期和首次购买日期以获取交易销售前的平均天数,以获取交易在库存中的天数,所以应该是
I want to get first sell date and first buy date to get average days before a transaction is sold, to get days transaction is in inventory, so it should be
(Sell date)2012-03-01 - (Buy date)2012-02-08
我正在尝试这个
SELECT
case when t.trans_type ='Sell' then transaction_date end as SellDate
,case when t.trans_type ='Buy' then transaction_date end as BuyDate
,DATEDIFF(case when t.trans_type ='Sell' then transaction_date end
,case when t.trans_type ='Buy' then transaction_date end) as Date
,transaction_date
FROM transactions t
order by transaction_date
但总是在 Date 中获取 NULL
But always getting NULL in Date
这是完整的查询
SELECT p.abbreviation,ag.name
,sum(-1.00 * t.miles * t.price - coalesce(t.fees,0) - coalesce(c.cost,0)) as profit
,sum(t.miles) 'Totakl Miles'
,avg(price / miles) 'Average'
,transaction_date
FROM transactions t
inner join accounts a on t.account_id = a.id
inner join programs p on a.program_id = p.id
inner join agents ag on t.agent_id = ag.id
LEFT JOIN (
SELECT rp.sell_id, sum(rp.miles * t.price) as cost
from report_profit rp
inner join transactions t on rp.buy_id = t.id
where t.miles > 50000
group by rp.sell_id
order by rp.sell_id
) c on t.id = c.sell_id
where t.transaction_date BETWEEN '2012-03-14' AND '2012-04-14'
Group by p.id , ag.id
编辑
我尝试了littlevicar的回答,但由于我添加了Group,它给出了错误子查询返回多条记录"
I tried liquorvicar answer, but it is giving error "Sub-query return more than one record" because of the Group by i added
有人可以指导我吗?
提前致谢...
推荐答案
首先感谢大家的帮助
这是返回确切结果的查询
Here is the query which return exact result
select p_id,ag_id,
p_abb,ag_name
,sum(-1.00 * miles * price - coalesce(fees,0) - coalesce(cost,0)) as profit
,sum(miles) 'Total Miles',avg(price / miles) 'Average'
,DATEDIFF(min(buy_dt),min(sell_dt)) as 'Days'
From
(
SELECT p.id 'p_id',ag.id 'ag_id',p.abbreviation 'p_abb',ag.name 'ag_name'
,miles
,price
,fees
,c.cost
,case when t.trans_type ='Sell' then transaction_date end 'sell_dt'
,case when t.trans_type ='Buy' then transaction_date end 'buy_dt'
,transaction_date
FROM transactions t
inner join accounts a on t.account_id = a.id
inner join programs p on a.program_id = p.id
inner join agents ag on t.agent_id = ag.id
LEFT JOIN (
SELECT rp.sell_id, sum(rp.miles * t.price) as cost
from report_profit rp
inner join transactions t on rp.buy_id = t.id
where t.miles > 50000
group by rp.sell_id
order by rp.sell_id
) c on t.id = c.sell_id
) t1
group by p_id, ag_id
再次感谢大家..
这篇关于MYSQL 查询以获取日期差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文