select place,time,min(money) money from table1 where table1.Time>='20050101' and table1.Time < '20050201' group by place,timeselect place,time,max(money) money from table1 where table1.Time>='20050101' and table1.Time < '20050201' group by place,time
min(money)不对 ,因为今天的3条数据 的金额 可能是 100,50,200
不一定是越来越大的。
而且时间 我要取当前日期的 昨天 不是'20050201'。
(select place,min(time) as time from table1 where (time >= CONVERT(dateTime, CONVERT(varchar(10), GETDATE() - 1, 120)))
AND (time < CONVERT(dateTime, CONVERT(varchar(10), GETDATE())))unionselect place,max(time) as timefrom table1 where (time >= CONVERT(dateTime, CONVERT(varchar(10), GETDATE() - 1, 120)))
AND (time < CONVERT(dateTime, CONVERT(varchar(10), GETDATE()))))bwhere a.place=b.place and a.time=b.time
问题2也由【jinjazz(近身剪(N-P攻略)) 】解决了 谢谢获得该地点 当天的 第一条金额 作为 今天的期初 【答案】
select a.place,a.money,CONVERT(varchar(10), GETDATE() - 1, 112) AS Date
from table1 a,
(
select place,min(time) as Time from table1
where (time>= CONVERT(dateTime, CONVERT(varchar(10), GETDATE() - 1, 120)))
AND (time< CONVERT(dateTime, CONVERT(varchar(10), GETDATE())))
GROUP BY place
) b
where a.place=b.placeand a.time= b.time
谢谢 所有热心参与的朋友 谢谢 揭帖
select a.place,a.money_2,CONVERT(varchar(10), GETDATE() - 1, 112) AS Date
from table1 a,
(
select place,max(time) as Time from table1
where (time>= CONVERT(dateTime, CONVERT(varchar(10), GETDATE() - 1, 120)))
AND (time< CONVERT(dateTime, CONVERT(varchar(10), GETDATE())))
GROUP BY place
) b
where a.place=b.placeand a.time= b.time