我以前写了个存储过程可以解决这个问题CREATE PROC [dbo].[PROC_SUM] @CONDITION FLOAT, @TABLENAME VARCHAR(100) AS BEGIN TRANSACTION /* 功能说明:用来将原表生成新一列,其值为小于原始列与给定数差 */ DECLARE @ROWNAME VARCHAR(100) --存储需要新建的列名 DECLARE @SQL VARCHAR(MAX) --查询出加上新列的的信息 DECLARE @SQL1 VARCHAR(MAX) DECLARE @SQL4 VARCHAR(MAX) DECLARE @SQL2 VARCHAR(MAX) --将查询结果插入新表A1 DECLARE @SQL3 VARCHAR(MAX) --删除原来的表A DECLARE @SQL5 VARCHAR(MAX) --存储新列值和-------------------------------------------------------------- --查询新列名 SELECT @ROWNAME=STUFF ('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100)),LEN('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100))),1,CAST(CAST ( RIGHT ('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100)),1)+1 AS INT) AS VARCHAR(100))) FROM SYSCOLUMNS WHERE ID=OBJECT_ID('A')AND name<>'NUM'SELECT @SQL='' SELECT @SQL1='' SELECT @SQL2='' SELECT @SQL3='' SELECT @SQL4='' ---------------------查询加入新列后的结果------------------------ SELECT @SQL=@SQL+'+'+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME)
SELECT @SQL4=@SQL4+' AND T1.WAGES1<>TT.'+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) SELECT @SQL=' ,ISNULL(T1.WAGES1,0) AS '+@ROWNAME +' FROM ( SELECT *,'+SUBSTRING (@SQL,6, LEN(@SQL)-5)+' AS WAGES FROM A) TT LEFT JOIN ( SELECT DISTINCT WAGES1 FROM A ) T1 ON T1.WAGES1<=CAST('+CAST(@CONDITION AS VARCHAR(100))+' AS FLOAT)-TT.WAGES '+SUBSTRING (@SQL4 , CHARINDEX('AND',@SQL4,5),LEN(@SQL4)-CHARINDEX('AND',@SQL4,5)+1)SELECT @SQL1=@SQL1+',TT.'+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) SELECT @SQL1='SELECT '+SUBSTRING (@SQL1,2,LEN(@SQL1)-1)SELECT @SQL='SELECT * FROM ('+@SQL1+@SQL+')TT 'EXEC( @SQL) -------------------------------------创建新表A1并将查询结果插入新表A1---------------------------------- SELECT @SQL2=@SQL2+','+NAME+' FLOAT' FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) SELECT @SQL2=' IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(''A1'') ) BEGIN DROP TABLE A1 ENDCREATE TABLE A1(NUM INT '+SUBSTRING (@SQL2,11,LEN(@SQL2)-10)+','+@ROWNAME +' FLOAT) INSERT INTO A1 SELECT * FROM ('+@SQL+')TT 'EXEC(@SQL2) ----------------------------------删除原表,并将新表A1改名为原表名------------------------------------------ SELECT @SQL3='DROP TABLE '+ @TABLENAME EXEC(@SQL3) EXEC sp_rename 'A1' , @TABLENAME ----------------------------------------获取新列值和----------------------------------------- SELECT @SQL5=' UPDATE A2 SET A=(SELECT SUM('+@ROWNAME +') FROM A )' EXEC(@SQL5) COMMIT TRANSACTION GO 然后列名wages1代表取一次的取法 再执行一次取两次 wages2代表取两次的取法
这里传进来的参数应该是15也就是全部取完的球的数字和 表的格式跟列名是这个 CREATE TABLE A (NUM VARCHAR(10),WAGES1 FLOAT) INSERT INTO A VALUES('1',1) INSERT INTO A VALUES('2',2) INSERT INTO A VALUES('3',3) INSERT INTO A VALUES('4',4) INSERT INTO A VALUES('5',5)
create table tb1(no char(1)) insert into tb1 select 1 union select 2 union select 3 union select 4 union select 5select * from tb1 union select a.no+b.no from tb1 a,tb1 b where a.no>b.no union select a.no+b.no+c.no from tb1 a,tb1 b,tb1 c where a.no>b.no and b.no>c.no union select a.no+b.no+c.no+d.no from tb1 a,tb1 b,tb1 c,tb1 d where a.no>b.no and b.no>c.no and c.no>d.no union select '12345'/* no ----- 1 12345 2 21 3 31 32 321 4 41 42 421 43 431 432 4321 5 51 52 521 53 531 532 5321 54 541 542 5421 543 5431 5432
if OBJECT_ID('tb') is not null drop table tb go create table tb(col int) insert into tb select 1 union all select 2 union all select 3 union all select 4 union all select 5select *,'','','','' from tb union all select a.col,b.col,'','','' from tb a,tb b where a.col<b.col union all select a.col,b.col,c.col,'','' from tb a,tb b,tb c where a.col<b.col and b.col<c.col and a.col<c.col union all select a.col,b.col,c.col,d.col,'' from tb a,tb b,tb c,tb d where a.col<b.col and a.col<c.col and a.col<d.col and b.col<c.col and b.col<d.col and c.col <d.col union all select a.col,b.col,c.col,d.col,e.col from tb a,tb b,tb c,tb d,tb e where a.col<b.col and a.col<c.col and a.col<d.col and a.col<e.col and b.col<c.col and b.col<d.col and b.col<e.col and c.col <d.col and c.col<e.col and d.col<e.col/* (5 行受影响) col ----------- ----------- ----------- ----------- ----------- 1 0 0 0 0 2 0 0 0 0 3 0 0 0 0 4 0 0 0 0 5 0 0 0 0 1 2 0 0 0 1 3 0 0 0 1 4 0 0 0 1 5 0 0 0 2 3 0 0 0 2 4 0 0 0 2 5 0 0 0 3 4 0 0 0 3 5 0 0 0 4 5 0 0 0 1 2 3 0 0 1 2 4 0 0 1 3 4 0 0 1 2 5 0 0 1 3 5 0 0 1 4 5 0 0 2 3 4 0 0 2 3 5 0 0 2 4 5 0 0 3 4 5 0 0 1 2 3 4 0 1 2 3 5 0 1 2 4 5 0 1 3 4 5 0 2 3 4 5 0 1 2 3 4 5(31 行受影响)*/
CREATE FUNCTION FN_C( @N INT ,@R INT ) RETURNS INT AS BEGIN IF @N<= 0 RETURN 1 DECLARE @RET INT DECLARE @I INT SET @I = 1 SET @RET = 1 WHILE @I <= @R BEGIN SET @RET = @RET * (@N - @I + 1) / @I SET @I = @I + 1 END RETURN @RET END GO SELECT DBO.FN_C(5,1)+DBO.FN_C(5,2)+DBO.FN_C(5,3)+DBO.FN_C(5,4)+DBO.FN_C(5,5)-- 结果 31
我以前写了个存储过程可以解决这个问题CREATE PROC [dbo].[PROC_SUM]
@CONDITION FLOAT,
@TABLENAME VARCHAR(100)
AS
BEGIN TRANSACTION
/*
功能说明:用来将原表生成新一列,其值为小于原始列与给定数差
*/
DECLARE @ROWNAME VARCHAR(100) --存储需要新建的列名
DECLARE @SQL VARCHAR(MAX) --查询出加上新列的的信息
DECLARE @SQL1 VARCHAR(MAX)
DECLARE @SQL4 VARCHAR(MAX)
DECLARE @SQL2 VARCHAR(MAX) --将查询结果插入新表A1
DECLARE @SQL3 VARCHAR(MAX) --删除原来的表A
DECLARE @SQL5 VARCHAR(MAX) --存储新列值和--------------------------------------------------------------
--查询新列名
SELECT @ROWNAME=STUFF ('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100)),LEN('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100))),1,CAST(CAST ( RIGHT ('WAGES'+CAST(MAX(CAST(SUBSTRING(NAME,6,LEN(NAME)-5) AS INT)) AS VARCHAR(100)),1)+1 AS INT) AS VARCHAR(100))) FROM SYSCOLUMNS WHERE ID=OBJECT_ID('A')AND name<>'NUM'SELECT @SQL=''
SELECT @SQL1=''
SELECT @SQL2=''
SELECT @SQL3=''
SELECT @SQL4=''
---------------------查询加入新列后的结果------------------------
SELECT @SQL=@SQL+'+'+NAME
FROM
SYSCOLUMNS
WHERE
ID=OBJECT_ID(@TABLENAME)
SELECT @SQL4=@SQL4+' AND T1.WAGES1<>TT.'+NAME
FROM
SYSCOLUMNS
WHERE
ID=OBJECT_ID(@TABLENAME)
SELECT @SQL=' ,ISNULL(T1.WAGES1,0) AS '+@ROWNAME +'
FROM
(
SELECT *,'+SUBSTRING (@SQL,6, LEN(@SQL)-5)+' AS WAGES FROM A) TT
LEFT JOIN
(
SELECT DISTINCT WAGES1 FROM A
) T1 ON T1.WAGES1<=CAST('+CAST(@CONDITION AS VARCHAR(100))+' AS FLOAT)-TT.WAGES '+SUBSTRING (@SQL4 , CHARINDEX('AND',@SQL4,5),LEN(@SQL4)-CHARINDEX('AND',@SQL4,5)+1)SELECT @SQL1=@SQL1+',TT.'+NAME
FROM
SYSCOLUMNS
WHERE
ID=OBJECT_ID(@TABLENAME)
SELECT @SQL1='SELECT '+SUBSTRING (@SQL1,2,LEN(@SQL1)-1)SELECT @SQL='SELECT * FROM ('+@SQL1+@SQL+')TT 'EXEC( @SQL)
-------------------------------------创建新表A1并将查询结果插入新表A1----------------------------------
SELECT @SQL2=@SQL2+','+NAME+' FLOAT'
FROM
SYSCOLUMNS
WHERE
ID=OBJECT_ID(@TABLENAME)
SELECT @SQL2='
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(''A1'') )
BEGIN
DROP TABLE A1
ENDCREATE TABLE A1(NUM INT '+SUBSTRING (@SQL2,11,LEN(@SQL2)-10)+','+@ROWNAME +' FLOAT)
INSERT INTO A1
SELECT * FROM ('+@SQL+')TT
'EXEC(@SQL2) ----------------------------------删除原表,并将新表A1改名为原表名------------------------------------------
SELECT @SQL3='DROP TABLE '+ @TABLENAME
EXEC(@SQL3)
EXEC sp_rename 'A1' , @TABLENAME
----------------------------------------获取新列值和-----------------------------------------
SELECT @SQL5='
UPDATE A2
SET A=(SELECT SUM('+@ROWNAME +') FROM A )'
EXEC(@SQL5)
COMMIT TRANSACTION GO
然后列名wages1代表取一次的取法 再执行一次取两次 wages2代表取两次的取法
表的格式跟列名是这个
CREATE TABLE A (NUM VARCHAR(10),WAGES1 FLOAT)
INSERT INTO A VALUES('1',1)
INSERT INTO A VALUES('2',2)
INSERT INTO A VALUES('3',3)
INSERT INTO A VALUES('4',4)
INSERT INTO A VALUES('5',5)
insert into tb1
select 1 union select 2 union
select 3 union select 4 union
select 5select * from tb1
union
select a.no+b.no from tb1 a,tb1 b where a.no>b.no
union
select a.no+b.no+c.no from tb1 a,tb1 b,tb1 c where a.no>b.no and b.no>c.no
union
select a.no+b.no+c.no+d.no from tb1 a,tb1 b,tb1 c,tb1 d where a.no>b.no and b.no>c.no and c.no>d.no
union
select '12345'/*
no
-----
1
12345
2
21
3
31
32
321
4
41
42
421
43
431
432
4321
5
51
52
521
53
531
532
5321
54
541
542
5421
543
5431
5432
drop table tb
go
create table tb(col int)
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5select *,'','','','' from tb
union all
select a.col,b.col,'','','' from tb a,tb b where a.col<b.col
union all
select a.col,b.col,c.col,'','' from tb a,tb b,tb c where a.col<b.col and b.col<c.col and a.col<c.col
union all
select a.col,b.col,c.col,d.col,'' from tb a,tb b,tb c,tb d where a.col<b.col and a.col<c.col and a.col<d.col and b.col<c.col and b.col<d.col and c.col <d.col
union all
select a.col,b.col,c.col,d.col,e.col from tb a,tb b,tb c,tb d,tb e where a.col<b.col and a.col<c.col and a.col<d.col and a.col<e.col and b.col<c.col and b.col<d.col and b.col<e.col and c.col <d.col and c.col<e.col and d.col<e.col/*
(5 行受影响)
col
----------- ----------- ----------- ----------- -----------
1 0 0 0 0
2 0 0 0 0
3 0 0 0 0
4 0 0 0 0
5 0 0 0 0
1 2 0 0 0
1 3 0 0 0
1 4 0 0 0
1 5 0 0 0
2 3 0 0 0
2 4 0 0 0
2 5 0 0 0
3 4 0 0 0
3 5 0 0 0
4 5 0 0 0
1 2 3 0 0
1 2 4 0 0
1 3 4 0 0
1 2 5 0 0
1 3 5 0 0
1 4 5 0 0
2 3 4 0 0
2 3 5 0 0
2 4 5 0 0
3 4 5 0 0
1 2 3 4 0
1 2 3 5 0
1 2 4 5 0
1 3 4 5 0
2 3 4 5 0
1 2 3 4 5(31 行受影响)*/
@N INT
,@R INT
)
RETURNS INT
AS
BEGIN
IF @N<= 0
RETURN 1
DECLARE @RET INT
DECLARE @I INT
SET @I = 1
SET @RET = 1
WHILE @I <= @R
BEGIN
SET @RET = @RET * (@N - @I + 1) / @I
SET @I = @I + 1
END
RETURN @RET
END
GO
SELECT DBO.FN_C(5,1)+DBO.FN_C(5,2)+DBO.FN_C(5,3)+DBO.FN_C(5,4)+DBO.FN_C(5,5)-- 结果
31