mysql分组问题

Question

我有下表:

date1              date2       sc   cash    date
"2010-09-20"    "2010-09-21"    202 300 "2010-03-01"
"2010-09-20"    "2010-09-21"    202 600 "2010-08-01"
"2010-09-20"    "2010-09-21"    202 670 "2010-08-20"
"2010-09-20"    "2010-09-21"    202 710 "2010-09-01"
"2010-09-20"    "2010-09-21"    202 870 "2010-09-21"
"2010-09-21"    "2010-09-22"    199 300 "2010-03-01"
"2010-09-21"    "2010-09-22"    199 600 "2010-08-01"
"2010-09-21"    "2010-09-22"    199 670 "2010-08-20"
"2010-09-21"    "2010-09-22"    199 710 "2010-09-01"
"2010-09-21"    "2010-09-22"    199 870 "2010-09-21"

什么是分组依据(date1,date2)和(cash,date)这样date = max(date <= date1)

date1              date2       sc   cash    date
"2010-09-20"    "2010-09-21"    202 870 "2010-09-21"
"2010-09-21"    "2010-09-22"    199 870 "2010-09-21"

Answer 1

你需要的是使用HAVING子句...我假设你在每一行上都有一个id..(如果没有，你将不得不修改我的查询，但会更难 =D )

SELECT date1,date2,sc,cash,date
FROM mytable 
WHERE id IN (SELECT id
             FROM mytable GROUP BY date1,date2
             HAVING max(date)>=date1)

我已经在 Postgresql 中测试了它并且工作正常。抱歉，我无法在 MySql 中测试它

祝你好运