搜索到“最大日期” sql中的字段 [英] Search to have "max date" field in 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屋!