不用游标如何插入（insert）到表（2）

Question

--B
DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [CASH] DECIMAL(10,1), [CREDIT] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 'A', 1.1, 1.2 UNION ALL 
SELECT 2, 'B', 0.0, 5.0 UNION ALL 
SELECT 3, 'A', 5.0, 10.0 UNION ALL 
SELECT 4, 'A', 2.4, 5.5 UNION ALL 
SELECT 5, 'B', 3.0, 4.7;WITH CET AS
(
SELECT ID AS ID2,[COMPANY],[CASH],0 AS [CREDIT],PX=0 FROM @TB WHERE CASH<>0
UNION ALL
SELECT ID,[COMPANY],0,[CASH],1 FROM @TB WHERE CASH<>0
)SELECT 1000+SUBID-1 AS ID,50 AS BillID ,SUBID,COMPANY,CASH AS MONEYJ,CREDIT AS MONEYD
INTO TBALL
FROM (
SELECT TOP 100 PERCENT *,SUBID=ROW_NUMBER() OVER (ORDER BY ID2,PX)
FROM CET 
ORDER BY ID2,PX
) T
SELECT * FROM TBALL
DROP TABLE TBALL
/*
1000 50 1 A 1.1 0.0
1001 50 2 A 0.0 1.1
1002 50 3 A 5.0 0.0
1003 50 4 A 0.0 5.0
1004 50 5 A 2.4 0.0
1005 50 6 A 0.0 2.4
1006 50 7 B 3.0 0.0
1007 50 8 B 0.0 3.0
*/

Answer 1

--A方式：
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[COMPANY] varchar(1),[CASH] numeric(2,1),[CREDIT] numeric(3,1))
insert [tb]
select 1,'A',1.1,1.2 union all
select 2,'B',0.0,5.0 union all
select 3,'A',5.0,10.0 union all
select 4,'A',2.4,5.5 union all
select 5,'B',3.0,4.7
go
--select * from [tb]with t1 as
(
select ID=1000+row_number() over(order by COMPANY,id),BillID=50,COMPANY,CODE='现金',MONEYJ=CASH,MONEYD=0.0 
from tb where CASH>0
)
, t2 as
(
select ID=max(ID),BillID,COMPANY,CODE='应收',MONEYJ=0.0,MONEYD=sum(MONEYJ)
from t1 group by BillID,COMPANY
)
select id,BillID,SUBID=row_number() over(order by id,code),CODE,MONEYJ,MONEYD
from (select * from t1 union all select * from t2) t
/*
id                   BillID      SUBID                CODE MONEYJ                                  MONEYD
-------------------- ----------- -------------------- ---- --------------------------------------- ---------------------------------------
1001                 50          1                    现金   1.1                                     0.0
1002                 50          2                    现金   5.0                                     0.0
1003                 50          3                    现金   2.4                                     0.0
1003                 50          4                    应收   0.0                                     8.5
1004                 50          5                    现金   3.0                                     0.0
1004                 50          6                    应收   0.0                                     3.0(6 行受影响)
*/

Answer 2

我是2000的，没有ROW_NUMBER()，2000的怎么写呀？

Answer 3

--少了CODE
DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [CASH] DECIMAL(10,1), [CREDIT] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 'A', 1.1, 1.2 UNION ALL 
SELECT 2, 'B', 0.0, 5.0 UNION ALL 
SELECT 3, 'A', 5.0, 10.0 UNION ALL 
SELECT 4, 'A', 2.4, 5.5 UNION ALL 
SELECT 5, 'B', 3.0, 4.7;WITH CET AS
(
SELECT ID AS ID2,[COMPANY],N'现金' AS CODE,[CASH],0 AS [CREDIT],PX=0 FROM @TB WHERE CASH<>0
UNION ALL
SELECT ID,[COMPANY],N'应收',0,[CASH],1 FROM @TB WHERE CASH<>0
)SELECT 1000+SUBID-1 AS ID,50 AS BillID ,SUBID,COMPANY,CODE,CASH AS MONEYJ,CREDIT AS MONEYD
INTO TBALL
FROM (
SELECT TOP 100 PERCENT *,SUBID=ROW_NUMBER() OVER (ORDER BY ID2,PX)
FROM CET 
ORDER BY ID2,PX
) T
SELECT * FROM TBALL
DROP TABLE TBALL
/*
1000 50 1 A 现金 1.1 0.0
1001 50 2 A 应收 0.0 1.1
1002 50 3 A 现金 5.0 0.0
1003 50 4 A 应收 0.0 5.0
1004 50 5 A 现金 2.4 0.0
1005 50 6 A 应收 0.0 2.4
1006 50 7 B 现金 3.0 0.0
1007 50 8 B 应收 0.0 3.0
*/

Answer 4

我这里是2000，无法执行SELECT TOP 100 PERCENT *,SUBID=ROW_NUMBER() OVER (ORDER BY ID2,PX)

Answer 5

--2000
DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [CASH] DECIMAL(10,1), [CREDIT] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 'A', 1.1, 1.2 UNION ALL 
SELECT 2, 'B', 0.0, 5.0 UNION ALL 
SELECT 3, 'A', 5.0, 10.0 UNION ALL 
SELECT 4, 'A', 2.4, 5.5 UNION ALL 
SELECT 5, 'B', 3.0, 4.7SELECT *,ID2=IDENTITY(int,1,1) INTO #
FROM (
SELECT ID,[COMPANY],N'现金' AS CODE,[CASH],0 AS [CREDIT],PX=0 FROM @TB WHERE CASH<>0
UNION ALL
SELECT ID,[COMPANY],N'应收',0,[CASH],1 FROM @TB WHERE CASH<>0
) T
ORDER BY ID,PX
SELECT 1000+ID2-1 AS ID,50 AS BillID ,ID2 AS SUBID,COMPANY,CODE,CASH AS MONEYJ,CREDIT AS MONEYD
INTO TBALL
FROM #SELECT * FROM TBALL
DROP TABLE TBALL,#
/*
1000 50 1 A 现金 1.1 0.0
1001 50 2 A 应收 0.0 1.1
1002 50 3 A 现金 5.0 0.0
1003 50 4 A 应收 0.0 5.0
1004 50 5 A 现金 2.4 0.0
1005 50 6 A 应收 0.0 2.4
1006 50 7 B 现金 3.0 0.0
1007 50 8 B 应收 0.0 3.0
*/

Answer 6

--A
DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [CASH] DECIMAL(10,1), [CREDIT] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 'A', 1.1, 1.2 UNION ALL 
SELECT 2, 'B', 0.0, 5.0 UNION ALL 
SELECT 3, 'A', 5.0, 10.0 UNION ALL 
SELECT 4, 'A', 2.4, 5.5 UNION ALL 
SELECT 5, 'B', 3.0, 4.7SELECT COMPANY,ISNULL(CASH,0) AS CASH,CASE WHEN CASH IS NULL THEN DEBIT ELSE 0 END AS DEBIT,ID=IDENTITY(int,1,1)
INTO #
FROM (
SELECT COMPANY,CASH,SUM(CASH) AS DEBIT
FROM (
SELECT *
FROM @TB 
WHERE CASH<>0
) T
GROUP BY COMPANY,CASH WITH ROLLUP
HAVING COMPANY IS NOT NULL OR CASH IS NOT NULL
) TSELECT 1000+ID-1 AS ID,50 AS BillID ,ID AS SUBID,COMPANY,CASE WHEN CASH<>0 THEN N'现金' ELSE N'应收' END AS CODE,CASH AS MONEYJ,DEBIT AS MONEYD
FROM #
DROP TABLE #
/*
1000 50 1 A 现金 1.1 0.0
1001 50 2 A 现金 2.4 0.0
1002 50 3 A 现金 5.0 0.0
1003 50 4 A 应收 0.0 8.5
1004 50 5 B 现金 3.0 0.0
1005 50 6 B 应收 0.0 3.0
*/

Answer 7

--B
--方法二
DECLARE @TB TABLE([ID] INT, [COMPANY] VARCHAR(1), [CASH] DECIMAL(10,1), [CREDIT] DECIMAL(10,1))
INSERT @TB 
SELECT 1, 'A', 1.1, 1.2 UNION ALL 
SELECT 2, 'B', 0.0, 5.0 UNION ALL 
SELECT 3, 'A', 5.0, 10.0 UNION ALL 
SELECT 4, 'A', 2.4, 5.5 UNION ALL 
SELECT 5, 'B', 3.0, 4.7SELECT TOP 100 PERCENT COMPANY,ISNULL(CASH,0) AS CASH,CASE WHEN CASH IS NULL THEN DEBIT ELSE 0 END AS DEBIT,ID2=IDENTITY(int,1,1)
INTO #
FROM (
SELECT ID,COMPANY,CASH,SUM(CASH) AS DEBIT
FROM (
SELECT *
FROM @TB 
WHERE CASH<>0
) T
GROUP BY ID,COMPANY,CASH WITH ROLLUP
HAVING COMPANY IS NOT NULL OR CASH IS NOT NULL
) T
ORDER BY IDSELECT 1000+ID2-1 AS ID,50 AS BillID ,ID2 AS SUBID,COMPANY,CASE WHEN CASH<>0 THEN N'现金' ELSE N'应收' END AS CODE,CASH AS MONEYJ,DEBIT AS MONEYD
FROM #DROP TABLE #
/*
1000 50 1 A 现金 1.1 0.0
1001 50 2 A 应收 0.0 1.1
1002 50 3 A 现金 5.0 0.0
1003 50 4 A 应收 0.0 5.0
1004 50 5 A 现金 2.4 0.0
1005 50 6 A 应收 0.0 2.4
1006 50 7 B 现金 3.0 0.0
1007 50 8 B 应收 0.0 3.0
*/

Answer 8

昨天有些忙忘记来看回复了，但是还是有些问题，
如果TB单据表多一列 PRODUCTNAME，发现楼上的方式就不行了。TB是单据表: 
ID  COMPANY    CASH    CREDIT    PRODUCTNAME
    1    A      1.1      1.2      P1
    2    B      0.0      5.0      P2
    3    A      5.0      10.0     P3
    4    A      2.4      5.5      P4
    5    B      3.0      4.7      P5
要求插入insert到凭证表TBALL, 两种方式 
TBALL.id  identity 
TBALL.SUBID 行号（记录行号） 
TBALL.BILLID 单据编号 A方式： 
TBALL是凭证表: 
编号  单据号  子编号    公司      科目      借      贷      产品名
id  BillID SUBID  COMPANY  CODE    MONEYJ MONEYD    PRODUCTNAME
1000  50    1        A      现金    1.1    0.0        P1
1001  50    2        A      现金    5.0    0.0        P3
1002  50    3        A      现金    2.4    0.0        P4
1003  50    4        A      应收    0.0    8.5        
1003  50    5        B      现金    3.0    0.0        P5
1004  50    6        B      应收    0.0    3.0        B方式 
TBALL是凭证表: 
编号  单据号  子编号    公司      科目      借      贷     产品名
id  BillID SUBID  COMPANY  CODE    MONEYJ MONEYD  PRODUCTNAME
1000  50    1        A      现金    1.1    0.0          P1
1001  50    2        A      应收    0.0    1.1          P1
1002  50    3        A      现金    5.0    0.0          P3
1003  50    4        A      应收    0.0    5.0          P3
1003  50    5        A      现金    2.4    0.0          P4
1004  50    6        A      应收    0.0    2.4          P4
1005  50    7        B      现金    3.0    0.0          P5
1006  50    8        B      应收    0.0    3.0          P5