22,210
社区成员
发帖
与我相关
我的任务
分享
--建表
CREATE TABLE #T
(
C NUMERIC(18,2)
)
--测试数据
INSERT INTO #T VALUES(1),(1.3),(9.1),(3),(2),(39),(8)
--开始计算
DECLARE @X NUMERIC(18,2)=17
;
WITH ct
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS id FROM #T
)
,ct2 AS
(
SELECT c,id, CONVERT(VARCHAR(max),c) AS cpath FROM ct WHERE c<=@X
UNION ALL
SELECT CONVERT(NUMERIC(18,2),ct2.C+ct.c),ct.id,cpath+'+'+CONVERT(VARCHAR(10),ct.C) FROM ct2 INNER JOIN ct ON ct.id > ct2.id
AND ct2.C+ct.c<@X
)
SELECT TOP 1 c,ct2.cpath FROM ct2 ORDER BY ABS(@X-c)
OPTION(MAXRECURSION 32767)
with b as (
select c as max_val,c as sum_val
,convert(varchar(max),c) as used_val
from a
where c<=17
union all
select (case when max_val>a.c then max_val else a.c end),sum_val+a.c
,convert(varchar(max),used_val+'+'+convert(varchar,a.c))
from a,b
where charindex('+'+convert(varchar,a.c)+'+','+'+b.used_val+'+')<1
and sum_val+a.c<=17
)
select top 1 sum_val,max(used_val)
from b
group by sum_val
order by sum_val desc
;with b as (
select c as max_val,c as sum_val
,convert(varchar(max),c) as used_val
from a
union all
select (case when max_val>a.c then max_val else a.c end)
,sum_val+a.c,convert(varchar(max),used_val+'+'+convert(varchar,a.c))
from a,b
where charindex('+'+convert(varchar,a.c)+'+','+'+b.used_val+'+')<1
)
select top 1 sum_val,max(used_val)
from b
where sum_val<17
group by sum_val
order by sum_val desc
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(VALUE DECIMAL(12,2))
INSERT INTO #T
SELECT 1 UNION ALL
SELECT 1.3 UNION ALL
SELECT 9.1 UNION ALL
SELECT 3 UNION ALL
SELECT 2 UNION ALL
SELECT 39 UNION ALL
SELECT 8
GO
DECLARE @A DECIMAL(12,2)
SET @A=17
;WITH CTE
AS
(SELECT *,CAST(VALUE AS DECIMAL(12,2)) AS NUM,CAST(VALUE AS VARCHAR(20)) AS EXPRESSION
FROM #T WHERE VALUE<=@A
UNION ALL
SELECT A.*,CAST(A.VALUE+B.NUM AS DECIMAL(12,2)),CAST(CAST(A.VALUE AS VARCHAR(20))+'+'+B.EXPRESSION AS VARCHAR(20))
FROM #T A
JOIN CTE B ON A.VALUE<B.VALUE
WHERE A.VALUE+B.NUM<=@A)
SELECT A.NUM,
CASE WHEN RIGHT(EXPRESSION,1)='+' THEN SUBSTRING(EXPRESSION,1,LEN(EXPRESSION)-1)
ELSE EXPRESSION END AS EXPRESSION
FROM CTE A
WHERE NOT EXISTS (SELECT 1 FROM CTE WHERE NUM>A.NUM)