取每月第一天F1的值，最大的F2，最小的，F3，最后一天F4的值，F5总和
code, date, F1, F2, F3, F4 , F5
1 19910102 10 12 8 9 500
1 19910103 9 12 8 8 2500
1 19910104 7 12 2 4 1500
1 19910112 13 15 8 14 500
1 19910205 11 13 8 9 500
1 19910213 10 12 6 7 1500
结果
code left(date,6) F1, F2, F3, F4 , F5
1 199101 10 15 2 15 5000
1 199102 11 13 6 7 2000
这是个金融类的，每月第一天的开盘价F1 ，每月最后一天的收盘价F4 ，其他几个字段都已经搞定，关键是F1 ,F4
------解决方案--------------------
try
- SQL code
select left(date,6) as 月份,F1=(select top 1 value from tb where left(date,6)=left(t.date,6) order by date), F2=max(value), F3=min(value), F4=(select top 1 value from tb where left(date,6)=left(t.date,6) order by date desc), F5=sum(value) from tb tgroup by left(date,6)
------解决方案--------------------
- SQL code
create table jc(code int, [date] date, F1 int, F2 int, F3 int, F4 int, F5 int) insert into jc select 1, '19910102', 10, 12, 8, 9, 500 union allselect 1, '19910103', 9, 12, 8, 8, 2500 union allselect 1, '19910104', 7, 12, 2, 4, 1500 union allselect 1, '19910112', 13, 15, 8, 14, 500 union allselect 1, '19910205', 11, 13, 8, 9, 500 union allselect 1, '19910213', 10, 12, 6, 7, 1500;with t as(select code,[date],F1,F2,F3,F4,F5, row_number() over(partition by month([date]) order by [date]) rn, replace(left(date,7),'-','') mon from jc)select a.code,cast(a.mon as varchar(6)) mon,(select top 1 F1 from t b where b.mon=a.mon and b.rn=1) F1,(select max(F2) from t b where b.mon=a.mon) F2,(select min(F3) from t b where b.mon=a.mon) F3,(select top 1 F4 from t b where b.mon=a.mon order by b.rn desc) F4,sum(F5) F5from t agroup by a.code,a.mon/*code mon F1 F2 F3 F4 F5----------- ------ ----------- ----------- ----------- ----------- -----------1 199101 10 15 2 14 50001 199102 11 13 6 7 2000(2 row(s) affected)*/