根据提供的一个数返回记录

[菜鸟之GrowUP]

DECLARE @tbl TABLE (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(20),
  score DECIMAL(18,2) 
)

INSERT INTO @tbl(name, score)
SELECT 'a', 80 UNION SELECT 'b', 90 UNION SELECT 'c', 70 UNION SELECT 'd', 60

DECLARE @Name VARCHAR(20),
        @Score DECIMAL(18,2),
        @TotScore DECIMAL(18,2),
        @TmpScore DECIMAL(18,2),
        @TmpName VARCHAR(2000)

SELECT * FROM @tbl  ORDER BY id DESC
/*
id          name                 score
----------- -------------------- ---------------------------------------
4           d                    60.00
3           c                    70.00
2           b                    90.00
1           a                    80.00
*/

--传入数字参数
SET @TotScore=100

SET @TmpScore=0
SET @TmpName=''
DECLARE tbl CURSOR FOR
  SELECT name, score FROM @tbl ORDER BY id DESC
OPEN tbl
FETCH NEXT FROM tbl INTO @Name, @Score
WHILE @@FETCH_STATUS=0
BEGIN 
  SET @TmpScore=@TmpScore+@Score
  SET @TmpName=@TmpName+','+@Name
  IF @TmpScore<@TotScore
  BEGIN 
    FETCH NEXT FROM tbl INTO @Name, @Score
  END
  ELSE
    BREAK;
END
CLOSE tbl
DEALLOCATE tbl

SELECT @TmpName, @TmpScore
/*
RetNames                         RetScore
-------------------------------- ---------------------------
,d,c                              130
*/

问题详细如下：
我需要根据一个传入的数字返回一个查询语句满足这个数字的记录数，比如上述的测试，具体示例如下：
1、传入100 返回D和C
2、传入60 返回D
3、传入70 返回D
4、传入130 返回D和C
5、传入150 返回B/C/D
总之传入的数字返回的结果是根据向上取值的规则进行，现在我通过游标实现了目的，因为我这个只是一个数字的实现效果，现在我有10000多条数据需要根据这个数字来到达该目的，在外边再加了一个游标，发现运行的效果太差了，不知道是否有方法把这一段sql转换成一个sql语句呢？

求助各个大神了！！

[Yole]

您可以先把组合做成一张表，然后在比较： 例如：a,b,c,d,a+b,a+c,a+d...a+b+c+d. 把这些数字添加到表中排序，然后再用来比较。 a 90 b 80 c 70 d 60 a+b 170 a+c 160 ... a+b+c+d 300

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 1 楼 u010192842 的回复:

您可以先把组合做成一张表，然后在比较： 例如：a,b,c,d,a+b,a+c,a+d...a+b+c+d. 把这些数字添加到表中排序，然后再用来比较。 a 90 b 80 c 70 d 60 a+b 170 a+c 160 ... a+b+c+d 300

这种方法显然是不可行的，因为我在实际的环境的是每次这样的查询都返回不一样的数据的，行数也可能是几行，也可能是上百行，要这样组合，那我还是都嵌套一次游标的。

• 打赏
• 举报

回复 赞

[中国风]

引用 楼主 u010975691 的回复:

我需要根据一个传入的数字返回一个查询语句满足这个数字的记录数，比如上述的测试，具体示例如下： 1、传入100 返回D和C 2、传入60 返回D 3、传入70 返回D 4、传入130 返回D和C 5、传入150 返回B/C/D

传入70 返回D-->D/C 能解释不？

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 3 楼 roy_88 的回复:

[quote=引用 楼主 u010975691 的回复:] 我需要根据一个传入的数字返回一个查询语句满足这个数字的记录数，比如上述的测试，具体示例如下： 1、传入100 返回D和C 2、传入60 返回D 3、传入70 返回D 4、传入130 返回D和C 5、传入150 返回B/C/D

传入70 返回D-->D/C 能解释不？ [/quote] 不好意思，大版，上面是写错了，应该返回的是D和C

• 打赏
• 举报

回复 赞

[Yole]

基础的数值不仅仅是a,b,c,d么？ 还会出现e,f,g,h...,z么？

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 5 楼 u010192842 的回复:

基础的数值不仅仅是a,b,c,d么？ 还会出现e,f,g,h...,z么？

是的，在时间的环境中，外面还有一套数据，需要根据这套数据的某个数字字段返回对应的明细数据列表，这个明细数据每个都不是固定的，说白了，我这个测试字段就相当于明细表的主键字段。

• 打赏
• 举报

回复 赞

[Yole]

主要是您要取最近的一个，而不是相同的；所以一定要把所有的情况都做出来，不然最近的一个您也得是一种一种组合去比较。

• 打赏
• 举报

回复 赞

[Tiger_Zhao]

单个参数查询

--传入数字参数
SET @TotScore=100

;WITH a AS ( -- 生成序号
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY id DESC) rn
      FROM @tbl
)
,b AS ( -- 递归
    SELECT rn,
           CONVERT(VARCHAR(2000),','+name) TmpName,
           CONVERT(DECIMAL(18,2),Score) TmpScore
      FROM a
     WHERE rn = 1
    UNION ALL
    SELECT a.rn,
           CONVERT(VARCHAR(2000),b.TmpName+','+a.name) TmpName,
           CONVERT(DECIMAL(18,2),b.TmpScore+a.Score) TmpScore
      FROM b
      JOIN a
        ON a.rn = b.rn + 1
     WHERE b.TmpScore < @TotScore -- 找到后直接终止递归
)
    SELECT TOP 1
           @TotScore TotScore,
           TmpName,
           TmpScore
      FROM b
  ORDER BY rn DESC

  TotScore TmpName      TmpScore
---------- ---------- ----------
    100.00 ,d,c           130.00

多个参数同时查询

;WITH a AS ( -- 生成序号
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY id DESC) rn
      FROM @tbl
)
,b AS ( -- 递归
    SELECT rn,
           CONVERT(VARCHAR(2000),','+name) TmpName,
           CONVERT(DECIMAL(18,2),Score) TmpScore
      FROM a
     WHERE rn = 1
    UNION ALL
    SELECT a.rn,
           CONVERT(VARCHAR(2000),b.TmpName+','+a.name) TmpName,
           CONVERT(DECIMAL(18,2),b.TmpScore+a.Score) TmpScore
      FROM b
      JOIN a
        ON a.rn = b.rn + 1
   /* 或者可以先取得最大的参数用来过滤，进行优化
     WHERE b.TmpScore < @MaxTotScore
   */
)
,c(TotScore) AS ( -- 模拟多个要查的参数
    SELECT CONVERT(DECIMAL(18,2),60) UNION ALL
    SELECT 70 UNION ALL
    SELECT 100 UNION ALL
    SELECT 130 UNION ALL
    SELECT 150 UNION ALL
    SELECT 290 UNION ALL
    SELECT 310
)
        SELECT c.TotScore,
               CONVERT(varchar(10),t.TmpName) TmpName,
               t.TmpScore
          FROM c
   OUTER APPLY (
                    SELECT TOP 1 *
                      FROM b
                     WHERE b.TmpScore >= c.TotScore
                  ORDER BY b.rn
               ) t
OPTION (MAXRECURSION 0) -- 10000多条数据全部递归

  TotScore TmpName      TmpScore
---------- ---------- ----------
     60.00 ,d              60.00
     70.00 ,d,c           130.00
    100.00 ,d,c           130.00
    130.00 ,d,c           130.00
    150.00 ,d,c,b         220.00
    290.00 ,d,c,b,a       300.00
    310.00 NULL             NULL

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 8 楼 Tiger_Zhao 的回复:

单个参数查询

谢谢大神，我看看效果！

• 打赏
• 举报

回复 赞

[中国风]

DECLARE @tbl TABLE (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(20),
  score DECIMAL(18,2) 
)
 
INSERT INTO @tbl(name, score)
SELECT 'a', 80 UNION SELECT 'b', 90 UNION SELECT 'c', 70 UNION SELECT 'd', 60


DECLARE @TotScore INT= 150

SELECT 
(SELECT  ','+Name	
FROM    @tbl AS a
WHERE NOT EXISTS( SELECT    1
                      FROM      @tbl
                      WHERE     id > a.id
                      HAVING    SUM(score) >= @TotScore
                    )
FOR XML PATH('')) AS RetNames
,@TotScore AS RetScore


/*

RetNames	RetScore
,b,c,d	150
*/

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 7 楼 u010192842 的回复:

主要是您要取最近的一个，而不是相同的；所以一定要把所有的情况都做出来，不然最近的一个您也得是一种一种组合去比较。

我这个要的效果其实就是根据一个数字，算出最近的临近数据行刚好数字合计能满足提供的这个数字就行。

• 打赏
• 举报

回复 赞

[中国风]

生成的格式是:D/C 还是C/D ？用Order BY 控制显示格式

DECLARE @tbl TABLE (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(20),
  score DECIMAL(18,2) 
)
 
INSERT INTO @tbl(name, score)
SELECT 'a', 80 UNION SELECT 'b', 90 UNION SELECT 'c', 70 UNION SELECT 'd', 60


DECLARE @TotScore INT= 100

SELECT 
(SELECT  ','+Name	
FROM    @tbl AS a
WHERE NOT EXISTS( SELECT    1
                      FROM      @tbl
                      WHERE     id > a.id
                      HAVING    SUM(score) >= @TotScore
                    )
ORDER BY ID DESC
FOR XML PATH('')) AS RetNames
,@TotScore AS RetScore


/*

RetNames	RetScore
,d,c	100
*/

• 打赏
• 举报

回复 赞

[中国风]

用以上方法，如果要去掉第1个逗号，用STUFF

• 打赏
• 举报

回复 赞

[菜鸟之GrowUP]

引用 13 楼 roy_88 的回复:

生成的格式是:D/C 还是C/D ？用Order BY 控制显示格式

DECLARE @tbl TABLE (
  id INT IDENTITY(1,1) NOT NULL,
  name VARCHAR(20),
  score DECIMAL(18,2) 
)
 
INSERT INTO @tbl(name, score)
SELECT 'a', 80 UNION SELECT 'b', 90 UNION SELECT 'c', 70 UNION SELECT 'd', 60


DECLARE @TotScore INT= 100

SELECT 
(SELECT  ','+Name	
FROM    @tbl AS a
WHERE NOT EXISTS( SELECT    1
                      FROM      @tbl
                      WHERE     id > a.id
                      HAVING    SUM(score) >= @TotScore
                    )
ORDER BY ID DESC
FOR XML PATH('')) AS RetNames
,@TotScore AS RetScore


/*

RetNames	RetScore
,d,c	100
*/

谢谢大版，顺序不影响，主要是结果，我看看

• 打赏
• 举报

回复 赞