22,207
社区成员
发帖
与我相关
我的任务
分享根据提供的一个数返回记录
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语句呢？
求助各个大神了！！
...全文
请发表友善的回复…
发表回复
菜鸟之GrowUP 2015-11-30
- 打赏
- 举报
谢谢大版，顺序不影响，主要是结果，我看看
中国风 2015-11-30
- 打赏
- 举报
用以上方法，如果要去掉第1个逗号，用STUFF
中国风 2015-11-30
- 打赏
- 举报
生成的格式是: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
*/菜鸟之GrowUP 2015-11-30
- 打赏
- 举报
我这个要的效果其实就是根据一个数字，算出最近的临近数据行刚好数字合计能满足提供的这个数字就行。
中国风 2015-11-30
- 打赏
- 举报
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 2015-11-30
- 打赏
- 举报
谢谢大神，我看看效果！
Tiger_Zhao 2015-11-30
- 打赏
- 举报
单个参数查询
多个参数同时查询
--传入数字参数
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
Yole 2015-11-30
- 打赏
- 举报
主要是您要取最近的一个，而不是相同的；所以一定要把所有的情况都做出来，不然最近的一个您也得是一种一种组合去比较。
菜鸟之GrowUP 2015-11-30
- 打赏
- 举报
是的，在时间的环境中，外面还有一套数据，需要根据这套数据的某个数字字段返回对应的明细数据列表，这个明细数据每个都不是固定的，说白了，我这个测试字段就相当于明细表的主键字段。
Yole 2015-11-30
- 打赏
- 举报
基础的数值不仅仅是a,b,c,d么？
还会出现e,f,g,h...,z么？
菜鸟之GrowUP 2015-11-30
- 打赏
- 举报
传入70 返回D-->D/C
能解释不？
[/quote]
不好意思，大版，上面是写错了，应该返回的是D和C
中国风 2015-11-30
- 打赏
- 举报
传入70 返回D-->D/C
能解释不？
菜鸟之GrowUP 2015-11-30
- 打赏
- 举报
这种方法显然是不可行的，因为我在实际的环境的是每次这样的查询都返回不一样的数据的，行数也可能是几行，也可能是上百行，要这样组合，那我还是都嵌套一次游标的。
Yole 2015-11-30
- 打赏
- 举报
您可以先把组合做成一张表，然后在比较：
例如：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
