INSERT INTO #FWCZS_FP_KP SELECT 1,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL SELECT 2,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL SELECT 3,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL SELECT 4,'999999','','3333','AAA',30,'2013/11/29',1,'BB' UNION ALL SELECT 5,'999999','','3333','AAA',30,'2013/11/29',1,'BB' UNION ALL SELECT 6,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL SELECT 7,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL SELECT 8,'999999','','3333','AAA',40,'2013/11/29',1,'BB' UNION ALL SELECT 9,'999999','','3333','AAA',40,'2013/11/29',1,'BB' UNION ALL SELECT 10,'999999','','3333','AAA',40,'2013/11/29',1,'BB'
select MIN(KP_ID ) 'min',MAX(KP_ID ) 'max', COUNT(KP_ID ) 'count',AVG (KP_JE )'money',MAX(ZF_BZ ) 'flag' from ( select * ,rn=ROW_NUMBER ()over( PARTITION by zf_bz order by KP_ID ) from #FWCZS_FP_KP) agroup by (a.KP_ID -a.rn) DROP TABLE #FWCZS_FP_KP
[KP_ID] [numeric](20, 0) ,--发票id
[FP_DM] [varchar](20) NULL,--发票代码
[FP_HM] [varchar](20) NULL,--发票号码
[SKF_SBH] [varchar](20) NULL,--收款方编号
[SKF_MC] [varchar](50) NULL,--收款方名称
[KP_JE] [decimal](16, 2) NULL,--开票金额
[KP_RQ] [datetime] NULL,--开票日期
[ZF_BZ] [char](1) NULL,--作废标志
[HZ_BH] [varchar](400) NULL--汇总编号
)
INSERT INTO #FWCZS_FP_KP
SELECT 1,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL
SELECT 2,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL
SELECT 3,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL
SELECT 4,'999999','','3333','AAA',30,'2013/11/29',1,'BB' UNION ALL
SELECT 5,'999999','','3333','AAA',30,'2013/11/29',1,'BB' UNION ALL
SELECT 6,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL
SELECT 7,'999999','','3333','AAA',10,'2013/11/29',0,'BB' UNION ALL
SELECT 8,'999999','','3333','AAA',40,'2013/11/29',1,'BB' UNION ALL
SELECT 9,'999999','','3333','AAA',40,'2013/11/29',1,'BB' UNION ALL
SELECT 10,'999999','','3333','AAA',40,'2013/11/29',1,'BB'
select MIN(KP_ID ) 'min',MAX(KP_ID ) 'max', COUNT(KP_ID ) 'count',AVG (KP_JE )'money',MAX(ZF_BZ ) 'flag' from (
select * ,rn=ROW_NUMBER ()over( PARTITION by zf_bz order by KP_ID ) from #FWCZS_FP_KP) agroup by (a.KP_ID -a.rn) DROP TABLE #FWCZS_FP_KP