好久没上来了~1、先查询出数据来 select 会员卡号 as kh,isnull(sum(消费金额),0) as je,isnull(sum(积分),0) as jf,convert(char(4),消费日期,120) as rq from 消费明细表 group by 会员卡号,convert(char(4),消费日期,120) 在交叉动态创建列 利用游标创建列并回填数据参考：--考勤统计newcreate procedure kqtj @date1 datetime,@date2 datetimeas /*创建数据表*/ IF Exists(Select * From SysObjects Where Type='U' And Name='kqtjt') Drop Table kqtjt Create Table kqtjt ([站点] char(1), [票房] varchar(50), [姓名] varchar(20), [实勤天数] int) insert kqtjt(站点,票房,姓名) select distinct left(lo_maid,1),machine_address,lo_operid from login,machinelist where login.lo_maid=machinelist.machine_id and lo_nr='XX' and lo_operid<>'管理员' and lo_operid<>'主任' and lo_datetime>=@date1 and lo_datetime<=@date2 order by left(lo_maid,1) declare @i int,@s varchar(20),@d char(10) set @i=0 while @i<=datediff(dd,@date1,@date2) begin set @s=ltrim(str(month(dateadd(dd,@i,@date1))))+'月'+ltrim(str(day(dateadd(dd,@i,@date1))))+'日' set @d=convert(char(10),dateadd(dd,@i,@date1),120) --添加列 exec('ALTER TABLE kqtjt ADD ['+@s+'] varchar(50) NULL') exec('update kqtjt set ['+@s+']=a.bs from (select left(lo_maid,1) as zd,lo_operid,case when count(*)>0 then ''*'' else '''' end as bs from login where convert(char(10),lo_datetime,120)='''+@d+''' and lo_nr=''xx'' group by left(lo_maid,1),lo_operid) a where kqtjt.[站点]=a.zd and kqtjt.[姓名]=a.lo_operid') set @i=@i+1 end update kqtjt set [实勤天数]=sl from (select lo_operid,zd,count(*) as sl from (select distinct lo_operid,left(lo_maid,1) as zd,convert(char(10),lo_datetime,120) as rq from login where lo_datetime>=@date1 and lo_datetime<=@date2 and lo_nr='xx') a group by lo_operid,zd) b where kqtjt.[站点]=b.zd and [姓名]=b.lo_operid select * from kqtjt2、这个就简单点了，select 会员卡号,isnull(sum(消费金额),0) as 消费金额总计,isnull(sum(积分),0) as 积分总计,isnull((select sum(消费金额) from 消费明细表 where 会员卡号=a.会员卡号 and convert(char(4),消费日期,120)=convert(char(4),getdate(),120)),0) as 当年消费金额,isnull((select sum(积分) from 消费明细表 where 会员卡号=a.会员卡号 and convert(char(4),消费日期,120)=convert(char(4),getdate(),120)),0) as 当年积分, from 消费明细表 a
