SQL delete最小值加上组中的下一个最大值
SQL delete最小值加上组中的下一个最大值
提问于 2015-07-22 00:28:45
What I get What I need
id hash assigned id hash assigned
-- ---- -------- -- ---- --------
1 1j 1 1 1j 1
2 1j-1 1 3 1j-2 1
3 1j-2 1 4 3m 1
4 3m 1 6 3m-8 1
5 3m-3 1 7 f7 1
6 3m-8 1 8 6q 0
7 f7 1 9 3y 0
8 6q 0 10 3y-3 0
9 3y 0
10 3y-3 0 我的数据集看起来像这样。散列始终是一个特定的数字长度，在本例中为2个字符。它在与顺序的但不是相同数量的单元的扩展同步后被修改。我需要删除那些分配了1的第一个修改。
这是我的非工作最佳猜测：
DELETE
FROM `table`
WHERE (`assigned`=1)
IN (SELECT `hash`, min(`hash`)+1 FROM `table` GROUP BY SUBSTRING(`hash`,1 , 2) ORDER BY hash ASC;回答 2
Stack Overflow用户
发布于 2015-07-22 00:52:16
一旦你的计数器达到两位数和三位数，你在字符串比较方面就会遇到问题。这是一个直截了当的方法，可能会让你更接近。在实际删除所有内容之前，请记住尝试使用select。
delete from T
where
assigned = 1
-- Business requirement not explained.
-- I'm assuming the entire group has the same assigned value.
and hash = (
select min(hash) from T as t2
where
left(t2.hash, 2) = left(T.hash, 2)
and t2.hash > (
select min(hash) from T as t3
where left(t3.hash, 2) = left(t2.hash, 2)
)
)Stack Overflow用户
发布于 2015-07-22 00:58:15
SQL Server
delete from T where id in (
select
t1.id
from T t1
where t1.assigned = 1 and t1.hash like '%-%' and not exists (
select * from T t2 where left(t1.hash, 2) = left(t2.hash, 2) and t2.hash like '%-%' and
cast(substring(t2.hash, 4, len(t2.hash) - 3) as int) <
cast(substring(t1.hash, 4, len(t1.hash) - 3) as int)
)
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31544654
复制相关文章
相似问题
腾讯云开发者
