搜索到“最大日期” sql中的字段 [英] Search to have "max date" field in sql

查看:80
本文介绍了搜索到“最大日期” sql中的字段的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一张桌子如:



TYPE_________DATE________________MONEY

=============== ======================

F01 __________ 2013/11/01 __________ 2

F01 __________ 2013/11/01 __________ 4

F01 __________ 2013/11/03 __________ 3

F02 __________ 2013/11/01 __________ 2

F02 __________ 2013/11/02 __________ 4

F02 __________ 2013/11/06 __________ 4



如果我搜索以下条件2013/11/01< = DATE< = 2013/11/05,我有以下结果:



TYPE_________MONEY______NEWDATE

============================ ===

F01 __________ 9 __________ 2013/11/03

F02 __________ 6 __________ 2013/11/06



请帮我一个sql命令来获得这个结果

谢谢

解决方案

你可以使用一个子查询,比如:

 选择 typ,mon,dat  as  newdate 来自 mytable  as  a 其中 a.dat =(选择 max(b.dat)来自 mytable  as  b 其中 ' < span class =code-string> 2013/11/01'< = dat< = '  2013/11/05'); 


 选择 a。类型,Sum(a。 Money  as   Money ,b。日期  as  newdate 
来自 temp a
left 淳佳n 选择 类型,max( date as 日期 来自 temp group by type )b a。类型 = b。类型
其中 a。日期> = ' 2013-11-1' AND
a。日期< = ' 2013-11-5'
group by a。 type ,b。 日期





[= EDIT =]由于OP的评论

  SELECT  t。类型,Sum(t。 Money  as   Money ,Max(t。日期 as  NewDate 
FROM t
WHERE 日期> = ' 2013-11-1' AND
日期< = ' 2013-11-5'
t。类型





[= EDIT =]





祝你好运,

OI


创建表AAA



LOAIPHIEU VARCHAR(3)NOT NULL,

THOIGIAN DATETIME NOT NULL,

TONGTIEN INT NOT NOT





SELECT *来自AAA

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F01','2013/11/01',2)

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F01','2013/11/01',4)

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F01','2013/11/03',3)

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F02','2013/11/01',2)

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F02','2013/11/02',4)

INSERT AAA(LOAIPHIEU,THOIGIAN,TONGTIEN)价值观('F02','2013/11/06',4)



- 解决方案1:错误



- 解决方案2:不适用于案例F02 IS 2013/11/02 - 我的结果是2013/11/06

SELECT LOAIPHIEU as vType,Sum(TONGTIEN)as iMoney,Max(THOIGIAN)as NEWDATE

来自AAA

THERE THOIGIAN> ='2013-11-1'和

THOIGIAN< ='2013-11-5'

Group by LOAIPHIEU





=>请帮助我另一个解决方案(2013/11/01< = DATE< = 2013/11/05),结果为:

TYPE_________MONEY______NEWDATE

==== ===========================

F01 __________ 9 __________ 2013/11/03

F02 __________ 6 __________ 2013 / 11 / 06

I have a table such as:

TYPE_________DATE________________MONEY
=====================================
F01__________2013/11/01__________2
F01__________2013/11/01__________4
F01__________2013/11/03__________3
F02__________2013/11/01__________2
F02__________2013/11/02__________4
F02__________2013/11/06__________4

If I search follow condition "2013/11/01 <= DATE <= 2013/11/05", I have result such as:

TYPE_________MONEY______NEWDATE
===============================
F01__________9__________2013/11/03
F02__________6__________2013/11/06

please help me a sql command to have this result
Thanks

解决方案

You might use a subquery, something like:

select typ, mon, dat as newdate from mytable as a where a.dat = (select max(b.dat) from mytable as b where '2013/11/01' <= dat <= '2013/11/05');


select a.Type, Sum(a.Money) as Money, b.Date as newdate
from temp a
left join (select type, max(date) as Date from temp group by type) b on a.Type = b.Type
where a.Date >= '2013-11-1' AND
      a.Date <= '2013-11-5'
group by a.type, b.Date



[=EDIT=] due to the OP's comment

SELECT t.Type, Sum(t.Money) as Money, Max(t.Date) as NewDate
FROM Table t
WHERE Date >= '2013-11-1' AND
      Date <= '2013-11-5'
Group By t.Type



[=EDIT=]


Good luck,
OI


create table AAA
(
LOAIPHIEU VARCHAR(3) NOT NULL,
THOIGIAN DATETIME NOT NULL,
TONGTIEN INT NOT NULL
)

SELECT * FROM AAA
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/01',2)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/01',4)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/03',3)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/01',2)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/02',4)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/06',4)

--SOLUTION 1: ERROR

--SOLUTION 2: NOT RIGHT FOR CASE "F02 IS 2013/11/02" - MY RESULT IS 2013/11/06
SELECT LOAIPHIEU as vType, Sum(TONGTIEN) as iMoney, Max(THOIGIAN) as NEWDATE
FROM AAA
WHERE THOIGIAN >= '2013-11-1' AND
THOIGIAN <= '2013-11-5'
Group By LOAIPHIEU


=> please help me another solution (2013/11/01 <= DATE <= 2013/11/05) to have result as:
TYPE_________MONEY______NEWDATE
===============================
F01__________9__________2013/11/03
F02__________6__________2013/11/06


这篇关于搜索到“最大日期” sql中的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆