有一表结构如下
cate values dates
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
A3 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-03-01
A3 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
数据的录入都是在每个季度下一月,但dates 显示的是每个季度最后一个月的的第一天.比如:
================================================================
A1 12.32,15.6,14.5,45.00,78.12,23.45 2006-03-01
================================================================
这条条记录是在四月分录入的而dates显示的是第一季度最后一个月的的第一天.该表的数据在导入时就生成好了,现在我想建立一视图:现在是8月也就是第三季度 ,我要列出所有第二季度(当前季度的上一个季度)的数据如下:
cate values dates
A1 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A2 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
A3 12.31,15.6,14.5,44.00,78.12,23.45 2006-06-01
A4 14.56,67.23,45.12,23.1,34.67,56.98 2006-06-01
但这些数据的values要做一些变动(values是varchar类型的,字符之间用,隔开)拿A1来说,也就是第3个逗号和第4个逗号之间的数据(44.00)加上所有小于这个季度(第二季度)并且cate相同记录的同样是第3个逗号和第4个逗号之间的数据(45.00)的和除以2,得到z最终的试图格式如下: