34,591
社区成员
发帖
与我相关
我的任务
分享
要快,可以改一下:
SET NOCOUNT ON
DECLARE @n INT,@i INT
DECLARE @m INT
DECLARE @a TABLE(a VARCHAR(1000),b INT,c int)
DECLARE @b TABLE(a VARCHAR(1000),b INT,c int)
SET @n=100
INSERT @a SELECT *,null FROM yzs WHERE num<@n+1
INSERT @b SELECT * FROM @a
SELECT @m=@@ROWCOUNT
SET @i=1
WHILE @i<=@m
BEGIN
INSERT @a SELECT a.a+','+b.a,a.b+b.b,LEN(a.a+','+b.a)-LEN(REPLACE(a.a+','+b.a,',','')) FROM @a a ,@b b
WHERE CHARINDEX(','+b.a+',',','+a.a+',')=0 AND a.b+b.b<=@n
DELETE FROM @a WHERE c<(SELECT MAX(c) FROM @a)
SET @i=@i+1
END
SELECT distinct y.* FROM yzs y
INNER JOIN (SELECT TOP 1 a FROM @a ORDER BY b+ISNULL(c,0) DESC)aa
on CHARINDEX(','+LTRIM(ID)+',',','+a+',')>0
--512M内存n=100时7秒出结果
---不厚道了 学习梁哥的 对了的话分给梁哥
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-09 10:58:53
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (id int,num int)
INSERT INTO [tb]
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,5 UNION ALL
SELECT 6,7 UNION ALL
SELECT 7,12 UNION ALL
SELECT 8,23
--SQL查询如下:
DECLARE @i int;
SET @i = 15;
;WITH Liang AS
(
SELECT Plen=1 ,id,num,total=num,path=CAST(RTRIM(id) AS varchar(MAX))
FROM tb WHERE num <= @i
UNION ALL
SELECT Plen=Plen+1,A.id,A.num,A.num+B.total,B.path+','+RTRIM(A.id)
FROM tb AS A
JOIN Liang AS B
ON A.num+B.total <= @i AND A.id > B.id
AND CHARINDEX(','+RTRIM(A.id)+',',','+B.path+',')=0
)
, cte as
(
SELECT TOP 1 WITH TIES * FROM Liang WHERE total<=@i ORDER BY total DESC
)
select * from cte where Plen=(select MAX(Plen) from cte)
/*
Plen id num total path
----------- ----------- ----------- ----------- -----------
4 6 7 15 1,2,3,6
(1 行受影响)
*/
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-09 10:58:53
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (id int,num int)
INSERT INTO [tb]
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,5 UNION ALL
SELECT 6,7 UNION ALL
SELECT 7,12 UNION ALL
SELECT 8,23
--SQL查询如下:
DECLARE @i int;
SET @i = 16;
;WITH Liang AS
(
SELECT id,num,total=num,path=CAST(RTRIM(id) AS varchar(MAX))
FROM tb WHERE num <= @i
UNION ALL
SELECT A.id,A.num,A.num+B.total,B.path+','+RTRIM(A.id)
FROM tb AS A
JOIN Liang AS B
ON A.num+B.total <= @i AND A.id > B.id
AND CHARINDEX(','+RTRIM(A.id)+',',','+B.path+',')=0
)
SELECT TOP 1 WITH TIES * FROM Liang WHERE total<=@i ORDER BY total DESC
/*
id num total path
----------- ----------- ----------- -------------
7 12 15 3,7
6 7 15 3,4,6
7 12 15 2,7
6 7 15 2,4,6
6 7 15 1,2,3,6
(5 行受影响)
*/