虽然要拼凑也能拼出来,但是实在想不通楼主为什么要这样转换特别是那个id和subid,搞两个唯一不重复的编号,不知道是何用意?
解决方案 »
- mysql转换成sqlserver2005数据库
- SqlConnection会自动断开
- 输入一个数n,然后输出数n!+(n-1)!+(n-2)!...1!代码出错,感谢指点。
- 一条SQL语句出错:服务器: 消息 8114,级别 16,状态 5,过程 JIEGUO33,行 14,将数据类型 varchar 转换为 float 时出错。
- 关于SQL中作业的问题?
- 提个很简单的语句上的问题
- 如何模仿日历表
- 各位大师,请教一个安全问题
- 关于数据库大本营3.0—数据库的问题!
- SQL2000 & SQL2012共存无法访问?
- 关于TXT导入sql server的问题 急急急
- 不用游标如何插入(insert)到表(2)
into #
from(
select top 100 percent * from(
Select 50 as BillID, company ,CASH,0.0 as MONEYD ,CODE = '现金' from ta where CASH > 0
union all
select 50 ,company,0 ,sum(cash) ,'应收' from ta group by company) a
order by 2 ,code)c
select right(1000+ SUBID -1 ,5),* from #
drop table #
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
*/
里面没有ROW_NUMBER(),2000的怎么写?
--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 *,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
*/
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
*/
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
INTO TBALL
FROM #SELECT * FROM TBALL
DROP TABLE #,TBALL
/*
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
*/
--方法二
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
*/
如果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