DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [MONEY] DECIMAL(10,1)) INSERT @TB SELECT 1, 'A', 1.1 UNION ALL SELECT 2, 'B', 2.0 UNION ALL SELECT 3, 'A', 5.0 UNION ALL SELECT 4, 'A', 2.4 UNION ALL SELECT 5, 'B', 3.0SELECT 1000+ID-1 AS 编号, 50 AS BillID, SUBID=ROW_NUMBER() OVER (ORDER BY [COMPANY],ID), COMPANY, CODE=N'应收', [MONEY] AS MONEYJ, 0 AS MONEYD INTO TBALL FROM @TB UNION ALL SELECT (SELECT 1000+MAX(ID) FROM @TB) AS 编号, 50 AS BillID, SUBID=(SELECT 1+MAX(ID) FROM @TB), '', CODE=N'收入', 0 AS MONEYJ, (SELECT SUM([MONEY]) FROM @TB) AS MONEYDSELECT * FROM TBALL DROP TABLE TBALL /* 1000 50 1 A 应收 1.1 0.0 1002 50 2 A 应收 5.0 0.0 1003 50 3 A 应收 2.4 0.0 1001 50 4 B 应收 2.0 0.0 1004 50 5 B 应收 3.0 0.0 1005 50 6 收入 0.0 13.5 */
declare @TB table(ID int,COMPANY varchar(10),[MONEY] money) insert into @TB select 1,'A',1.1 insert into @TB select 2,'B',2.0 insert into @TB select 3,'A',5.0 insert into @TB select 4,'A',2.4 insert into @TB select 5,'B',3.0 declare @TBALL table(id int identity(1000,1),BillID int,SUBID int,COMPANY varchar(10),CODE varchar(10),MONEYJ money,MONEYD money) --insert into @TBALL select 1000,50,1,'A','应收',1.1, 0 --insert into @TBALL select 1001,50,2,'A','应收',5.0, 0 --insert into @TBALL select 1002,50,3,'A','应收',2.4, 0 --insert into @TBALL select 1003,50,4,'B','应收',2.0, 0 --insert into @TBALL select 1004,50,5,'B','应收',3.0, 0 --insert into @TBALL select 1005,50,6,' ','收入', 0,13.5 insert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD) select 50 as BillID, t.ID as SUBID , t.COMPANY , '应收' as CODE , t.[MONEY] as MONEYJ, 0 as MONEYD from (select a.COMPANY, a.[MONEY], (select count(1) from @TB where COMPANY<a.COMPANY or (COMPANY=a.COMPANY and ID<=a.ID)) as ID from @TB a) t order by t.IDinsert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD) select 50,count(1)+1,'','收入',0,sum([MONEY]) from @TBselect * from @TBALL /* id BillID SUBID COMPANY CODE MONEYJ MONEYD ----------- ----------- ----------- ---------- ---------- --------------------- --------------------- 1000 50 1 A 应收 1.1000 .0000 1001 50 2 A 应收 5.0000 .0000 1002 50 3 A 应收 2.4000 .0000 1003 50 4 B 应收 2.0000 .0000 1004 50 5 B 应收 3.0000 .0000 1005 50 6 收入 .0000 13.5000 */
合并成一条insert语句: declare @TB table(ID int,COMPANY varchar(10),[MONEY] money) insert into @TB select 1,'A',1.1 insert into @TB select 2,'B',2.0 insert into @TB select 3,'A',5.0 insert into @TB select 4,'A',2.4 insert into @TB select 5,'B',3.0 declare @TBALL table(id int identity(1000,1),BillID int,SUBID int,COMPANY varchar(10),CODE varchar(10),MONEYJ money,MONEYD money) --insert into @TBALL select 1000,50,1,'A','应收',1.1, 0 --insert into @TBALL select 1001,50,2,'A','应收',5.0, 0 --insert into @TBALL select 1002,50,3,'A','应收',2.4, 0 --insert into @TBALL select 1003,50,4,'B','应收',2.0, 0 --insert into @TBALL select 1004,50,5,'B','应收',3.0, 0 --insert into @TBALL select 1005,50,6,' ','收入', 0,13.5 insert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD) select 50 as BillID, t.ID as SUBID , t.COMPANY , '应收' as CODE , t.[MONEY] as MONEYJ, 0 as MONEYD from (select a.COMPANY, a.[MONEY], (select count(1) from @TB where COMPANY<a.COMPANY or (COMPANY=a.COMPANY and ID<=a.ID)) as ID from @TB a) t union all select 50,count(1)+1,'','收入',0,sum([MONEY]) from @TB order by SUBID select * from @TBALL /* id BillID SUBID COMPANY CODE MONEYJ MONEYD ----------- ----------- ----------- ---------- ---------- --------------------- --------------------- 1000 50 1 A 应收 1.1000 .0000 1001 50 2 A 应收 5.0000 .0000 1002 50 3 A 应收 2.4000 .0000 1003 50 4 B 应收 2.0000 .0000 1004 50 5 B 应收 3.0000 .0000 1005 50 6 收入 .0000 13.5000 */
INSERT @TB
SELECT 1, 'A', 1.1 UNION ALL
SELECT 2, 'B', 2.0 UNION ALL
SELECT 3, 'A', 5.0 UNION ALL
SELECT 4, 'A', 2.4 UNION ALL
SELECT 5, 'B', 3.0SELECT 1000+ID-1 AS 编号,
50 AS BillID,
SUBID=ROW_NUMBER() OVER (ORDER BY [COMPANY],ID),
COMPANY,
CODE=N'应收',
[MONEY] AS MONEYJ,
0 AS MONEYD
INTO TBALL
FROM @TB
UNION ALL
SELECT (SELECT 1000+MAX(ID) FROM @TB) AS 编号,
50 AS BillID,
SUBID=(SELECT 1+MAX(ID) FROM @TB),
'',
CODE=N'收入',
0 AS MONEYJ,
(SELECT SUM([MONEY]) FROM @TB) AS MONEYDSELECT * FROM TBALL
DROP TABLE TBALL
/*
1000 50 1 A 应收 1.1 0.0
1002 50 2 A 应收 5.0 0.0
1003 50 3 A 应收 2.4 0.0
1001 50 4 B 应收 2.0 0.0
1004 50 5 B 应收 3.0 0.0
1005 50 6 收入 0.0 13.5
*/
insert into @TB select 1,'A',1.1
insert into @TB select 2,'B',2.0
insert into @TB select 3,'A',5.0
insert into @TB select 4,'A',2.4
insert into @TB select 5,'B',3.0
declare @TBALL table(id int identity(1000,1),BillID int,SUBID int,COMPANY varchar(10),CODE varchar(10),MONEYJ money,MONEYD money)
--insert into @TBALL select 1000,50,1,'A','应收',1.1, 0
--insert into @TBALL select 1001,50,2,'A','应收',5.0, 0
--insert into @TBALL select 1002,50,3,'A','应收',2.4, 0
--insert into @TBALL select 1003,50,4,'B','应收',2.0, 0
--insert into @TBALL select 1004,50,5,'B','应收',3.0, 0
--insert into @TBALL select 1005,50,6,' ','收入', 0,13.5 insert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD)
select
50 as BillID,
t.ID as SUBID ,
t.COMPANY ,
'应收' as CODE ,
t.[MONEY] as MONEYJ,
0 as MONEYD
from
(select
a.COMPANY,
a.[MONEY],
(select count(1) from @TB where COMPANY<a.COMPANY or (COMPANY=a.COMPANY and ID<=a.ID)) as ID
from
@TB a) t
order by
t.IDinsert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD)
select 50,count(1)+1,'','收入',0,sum([MONEY]) from @TBselect * from @TBALL
/*
id BillID SUBID COMPANY CODE MONEYJ MONEYD
----------- ----------- ----------- ---------- ---------- --------------------- ---------------------
1000 50 1 A 应收 1.1000 .0000
1001 50 2 A 应收 5.0000 .0000
1002 50 3 A 应收 2.4000 .0000
1003 50 4 B 应收 2.0000 .0000
1004 50 5 B 应收 3.0000 .0000
1005 50 6 收入 .0000 13.5000
*/
declare @TB table(ID int,COMPANY varchar(10),[MONEY] money)
insert into @TB select 1,'A',1.1
insert into @TB select 2,'B',2.0
insert into @TB select 3,'A',5.0
insert into @TB select 4,'A',2.4
insert into @TB select 5,'B',3.0
declare @TBALL table(id int identity(1000,1),BillID int,SUBID int,COMPANY varchar(10),CODE varchar(10),MONEYJ money,MONEYD money)
--insert into @TBALL select 1000,50,1,'A','应收',1.1, 0
--insert into @TBALL select 1001,50,2,'A','应收',5.0, 0
--insert into @TBALL select 1002,50,3,'A','应收',2.4, 0
--insert into @TBALL select 1003,50,4,'B','应收',2.0, 0
--insert into @TBALL select 1004,50,5,'B','应收',3.0, 0
--insert into @TBALL select 1005,50,6,' ','收入', 0,13.5 insert into @TBALL(BillID,SUBID,COMPANY,CODE,MONEYJ,MONEYD)
select
50 as BillID,
t.ID as SUBID ,
t.COMPANY ,
'应收' as CODE ,
t.[MONEY] as MONEYJ,
0 as MONEYD
from
(select
a.COMPANY,
a.[MONEY],
(select count(1) from @TB where COMPANY<a.COMPANY or (COMPANY=a.COMPANY and ID<=a.ID)) as ID
from
@TB a) t
union all
select 50,count(1)+1,'','收入',0,sum([MONEY]) from @TB
order by SUBID
select * from @TBALL
/*
id BillID SUBID COMPANY CODE MONEYJ MONEYD
----------- ----------- ----------- ---------- ---------- --------------------- ---------------------
1000 50 1 A 应收 1.1000 .0000
1001 50 2 A 应收 5.0000 .0000
1002 50 3 A 应收 2.4000 .0000
1003 50 4 B 应收 2.0000 .0000
1004 50 5 B 应收 3.0000 .0000
1005 50 6 收入 .0000 13.5000
*/