62,046
社区成员
发帖
与我相关
我的任务
分享求思路，或解决方法
有一个表的数据如下：
ID NO AMT STATUS
1 X1 18 1
2 X2 15 1
3 X3 2 1
4 X4 5 1
5 X5 20 1
----------------------
现在要求 AMT 的和等于 20 的记录，一旦满足，就把其他记录的STATUS 置为0
比如：可以把 ID=2,4,5的STATUS设为0，也可以 把 ：ID=1，2，3，4设为0，也可以 把 1，3，5设为0，
这个怎么弄比较好？？
ID NO AMT STATUS
1 X1 18 1
2 X2 15 1
3 X3 2 1
4 X4 5 1
5 X5 20 1
----------------------
现在要求 AMT 的和等于 20 的记录，一旦满足，就把其他记录的STATUS 置为0
比如：可以把 ID=2,4,5的STATUS设为0，也可以 把 ：ID=1，2，3，4设为0，也可以 把 1，3，5设为0，
这个怎么弄比较好？？
...全文
请发表友善的回复…
发表回复
r00_a2lBUR 2017-06-12
- 打赏
- 举报
建议这样的问题，还是在宿主语言解决。
qq_22884749 2016-10-13
- 打赏
- 举报
用存储过程计算
道素 2016-10-13
- 打赏
- 举报
组合合计部分是下面这部分
SELECT b.* FROM (
SELECT ROW_NUMBER()OVER(ORDER BY t1.ID) AS ModeID,COUNT(0)OVER(PARTITION BY 1) AS ModeCount
,t1.ID AS Line1,t2.id AS Line2,t3.id AS Line3,t4.id AS Line4,t5.id AS Line5
FROM #t AS t1
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id>t1.id AND tt.AMT+t1.AMT<=20) AS t2
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id>t2.id AND tt.AMT+t1.AMT+t2.AMT<=20) AS t3
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id >t3.id AND tt.AMT+t1.AMT+t2.AMT+t3.AMT<=20) AS t4
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id >t4.id AND tt.AMT+t1.AMT+t2.AMT+t3.AMT+t4.AMT<=20) AS t5
WHERE t1.AMT+ISNULL(t2.AMT,0) +ISNULL(t3.AMT,0) +ISNULL(t4.AMT,0) +ISNULL(t5.AMT,0) =20
) a
CROSS APPLY(VALUES(a.ModeID,Line1),(a.ModeID,Line2),(a.ModeID,Line3),(a.ModeID,Line4),(a.ModeID,Line5)) b(ModeID,LineID)
WHERE b.LineID IS NOT NULL
/*
1:选择1，3放弃2，4，5
2：选择2，4放弃1，3，5
3：选择5，放弃1，2，3，4
ModeID LineID
1 1
1 3
2 2
2 4
3 5
*/
道素 2016-10-13
- 打赏
- 举报
这里涉及到算法，如果数据量大，你最好像确定某种算法，如果待组合的行数固定且有限，可以采取类似下面全部组合的方式计算
你这里得到三种方案
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(ID INT,No VARCHAR(100),AMT INT,[STATUS] BIT)
GO
INSERT INTO #t(ID,NO,AMT,[STATUS])
select 1,'X1',18,1 UNION ALL
select 2,'X2',15,1 UNION ALL
select 3,'X3',2,1 UNION ALL
select 4,'X4',5,1 UNION ALL
select 5,'X5',20,1
SELECT * FROM (
SELECT sv.number,t.*,CASE WHEN c.LineID IS NULL THEN 0 ELSE 1 END AS Selected
,SUM(ISNULL(c.ModeID,0)) OVER(PARTITION BY sv.number) AS ModelLineCount
FROM #t AS t
INNER JOIN MASTER.dbo.spt_values AS sv ON sv.[type]='P' AND sv.number BETWEEN 1 AND 5
LEFT JOIN (
SELECT b.*,a.ModeCount FROM (
SELECT ROW_NUMBER()OVER(ORDER BY t1.ID) AS ModeID,COUNT(0)OVER(PARTITION BY 1) AS ModeCount
,t1.ID AS Line1,t2.id AS Line2,t3.id AS Line3,t4.id AS Line4,t5.id AS Line5
FROM #t AS t1
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id>t1.id AND tt.AMT+t1.AMT<=20) AS t2
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id>t2.id AND tt.AMT+t1.AMT+t2.AMT<=20) AS t3
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id >t3.id AND tt.AMT+t1.AMT+t2.AMT+t3.AMT<=20) AS t4
OUTER APPLY (SELECT * FROM #t AS tt WHERE tt.id >t4.id AND tt.AMT+t1.AMT+t2.AMT+t3.AMT+t4.AMT<=20) AS t5
WHERE t1.AMT+ISNULL(t2.AMT,0) +ISNULL(t3.AMT,0) +ISNULL(t4.AMT,0) +ISNULL(t5.AMT,0) =20
) a
CROSS APPLY(VALUES(a.ModeID,Line1),(a.ModeID,Line2),(a.ModeID,Line3),(a.ModeID,Line4),(a.ModeID,Line5)) b(ModeID,LineID)
WHERE b.LineID IS NOT NULL
) c ON c.LineID=t.ID AND c.ModeID=sv.number
) d WHERE d.ModelLineCount>0
ORDER BY d.number,d.ID
/*
number ID No AMT STATUS Selected ModelLineCount
1 1 X1 18 1 1 2
1 2 X2 15 1 0 2
1 3 X3 2 1 1 2
1 4 X4 5 1 0 2
1 5 X5 20 1 0 2
2 1 X1 18 1 0 4
2 2 X2 15 1 1 4
2 3 X3 2 1 0 4
2 4 X4 5 1 1 4
2 5 X5 20 1 0 4
3 1 X1 18 1 0 3
3 2 X2 15 1 0 3
3 3 X3 2 1 0 3
3 4 X4 5 1 0 3
3 5 X5 20 1 1 3
*/
㤁孞 2016-10-13
- 打赏
- 举报
这种算法建议在程序代码里面做操作.
myhid 2016-10-13
- 打赏
- 举报
你这个好像没有效果。
myhid 2016-10-13
- 打赏
- 举报
这个不确定到底是几条记录加起来等于20哦。
myhid 2016-10-13
- 打赏
- 举报
能写个存储过程吗？
卖水果的net 2016-10-13
- 打赏
- 举报
用单一语句比较困难，更难以理解！
建议在程序中处理！
或者放到一个存储过程中。
myhid 2016-10-13
- 打赏
- 举报
谢谢各们的回答~我仔细研究下各位的代码。
闭包客 2016-10-13
- 打赏
- 举报
--创建测试表
CREATE TABLE #
(
ID INT,
NO CHAR(2),
AMT INT,
STATUS BIT
);
--插入测试数据
INSERT INTO # VALUES
(1,'X1',18,1),
(2,'X2',15,1),
(3,'X3',2,1),
(4,'X4',5,1),
(5,'X5',20,1);
--1行的情况
update # set #.[STATUS] = 0 where not exists
(
select * from
(
select top 1 * from # where exists
(
select SUM(a.AMT) from
(
select top 1 * from # order by AMT desc
) a
having SUM(a.AMT) >= 20
) order by AMT desc
) b
where #.ID = b.ID
)
--2行的情况
update # set #.[STATUS] = 0 where not exists
(
select * from
(
select top 2 * from # where exists
(
select SUM(a.AMT) from
(
select top 2 * from # order by AMT desc
) a
having SUM(a.AMT) >= 20
) order by AMT desc
) b
where #.ID = b.ID
)
--3行的情况
update # set #.[STATUS] = 0 where not exists
(
select * from
(
select top 3 * from # where exists
(
select SUM(a.AMT) from
(
select top 3 * from # order by AMT desc
) a
having SUM(a.AMT) >= 20
) order by AMT desc
) b
where #.ID = b.ID
)
--4行的情况
update # set #.[STATUS] = 0 where not exists
(
select * from
(
select top 4 * from # where exists
(
select SUM(a.AMT) from
(
select top 4 * from # order by AMT desc
) a
having SUM(a.AMT) >= 20
) order by AMT desc
) b
where #.ID = b.ID
)
RINK_1 2016-10-12
- 打赏
- 举报
;WITH CTE
AS
(
SELECT * FROM TABLE A WHERE NOT EXISTS (SELECT 1 FROM TABLE WHERE ID<A.ID)
UNION ALL
SELECT B.* FROM CTE A
JOIN TABLE B ON A.AMT+B.AMT=20 AND A.ID<B.ID
)
UPDATE TABLE
SET SUATUS=0
WHERE ID NOT IN (SELECT ID FROM CTE)
mingqing6364 2016-10-12
- 打赏
- 举报
从ID升序判读做不到，或者说如果不使用游标，我做不到
只能按类别来，比如一个数=20，两个数相加=20，三个数相加=20……
--创建测试表
CREATE TABLE #
(
ID INT,
NO CHAR(2),
AMT INT,
STATUS BIT
);
--插入测试数据
INSERT INTO # VALUES
(1,'X1',18,1),
(2,'X2',15,1),
(3,'X3',2,1),
(4,'X4',5,1),
(5,'X5',20,1);
--一个数等于20
UPDATE # SET STATUS = 0 WHERE NOT EXISTS (SELECT A.ID FROM # A WHERE A.AMT = 20 AND #.ID = A.ID)
SELECT * FROM #
UPDATE # SET STATUS = 1;
--两个数等于20
UPDATE # SET STATUS = 0 WHERE NOT EXISTS (SELECT A.ID FROM # A INNER JOIN # B ON A.AMT + B.AMT = 20 WHERE #.ID = A.ID)
SELECT * FROM #
UPDATE # SET STATUS = 1;
--删除测试表
DROP TABLE #;myhid 2016-10-12
- 打赏
- 举报
优先从ID升序判断是否等于20，也就是把 ID=2,4,5的STATUS设为0
LongRui888 2016-10-12
- 打赏
- 举报
如果这样的话，到底是要优先把 那几个的status为0呢？
