27,580
社区成员
发帖
与我相关
我的任务
分享一个如何更优化的算法的题
值1 “ 1111111111”
值2 “ 1100000000”
同样的位置同样的字符相同。共有2个。
这两个字符串相同的数量是2
表 T
ID KEY
1 1011110111
2 0010110111
3 0000110100
4 0010110110
...............................
共60万条记录
传入有一个字符串值 '1001001111'
求表T里，所有相同数量大于5的记录。
因为表T有60万条记录，所以要找出 传入的字符串值 '1001001111'，相同数量大于5的记录，是一个需要优化算法的问题。
求解决，谢谢各位！
值2 “ 1100000000”
同样的位置同样的字符相同。共有2个。
这两个字符串相同的数量是2
表 T
ID KEY
1 1011110111
2 0010110111
3 0000110100
4 0010110110
...............................
共60万条记录
传入有一个字符串值 '1001001111'
求表T里，所有相同数量大于5的记录。
因为表T有60万条记录，所以要找出 传入的字符串值 '1001001111'，相同数量大于5的记录，是一个需要优化算法的问题。
求解决，谢谢各位！
...全文
请发表友善的回复…
发表回复
phprun 2017-07-05
- 打赏
- 举报
学习了，大神威武！
吉普赛的歌 2017-07-05
- 打赏
- 举报
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(ID INT IDENTITY(1,1) PRIMARY KEY, [KEY] VARCHAR(50))
--加测试数据
INSERT INTO t([KEY])
SELECT '1011110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0000110100' UNION ALL
SELECT '0010110110'
--加10万条测试数据
DECLARE @i INT,@iMax INT
SET @iMax=100000
WHILE 1=1
BEGIN
SELECT @i=COUNT(1) FROM t
PRINT @i
IF @i>=@imax
BEGIN
BREAK;
END
IF @i=0
BEGIN
INSERT INTO t ([KEY]) VALUES(N'1011110111')
END
IF @i<=@imax/2
BEGIN
INSERT INTO t ([KEY])
SELECT [KEY] FROM t
END
ELSE
BEGIN
INSERT INTO t ([KEY])
SELECT TOP( @iMax-@i ) [KEY] FROM t
END
END
GO
--我的函数
IF OBJECT_ID('dbo.Fun_GetEqualsChars') IS NOT NULL
DROP FUNCTION dbo.Fun_GetEqualsChars
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-07-04
-- Description: 比较两个字符串的相同字符的个数（从右至左）
-- =============================================
CREATE FUNCTION dbo.Fun_GetEqualsChars
(
@c1 VARCHAR(50),
@c2 VARCHAR(50)
)
RETURNS INT
AS
BEGIN
DECLARE @r INT
SET @r =
CASE WHEN LEFT(RIGHT(@c1,1),1) = LEFT(RIGHT(@c2,1),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,2),1) = LEFT(RIGHT(@c2,2),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,3),1) = LEFT(RIGHT(@c2,3),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,4),1) = LEFT(RIGHT(@c2,4),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,5),1) = LEFT(RIGHT(@c2,5),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,6),1) = LEFT(RIGHT(@c2,6),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,7),1) = LEFT(RIGHT(@c2,7),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,8),1) = LEFT(RIGHT(@c2,8),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,9),1) = LEFT(RIGHT(@c2,9),1) THEN 1 ELSE 0 END +
CASE WHEN LEFT(RIGHT(@c1,10),1) = LEFT(RIGHT(@c2,10),1) THEN 1 ELSE 0 END
RETURN @r
END
GO
--按他的办法写的函数
IF OBJECT_ID('dbo.Fun_GetEqualsChars_fish1') IS NOT NULL
DROP FUNCTION dbo.Fun_GetEqualsChars_fish1
GO
-- =============================================
-- Author: fish1
-- Create date: 2017-07-04
-- Description: 比较两个字符串的相同字符的个数（从右至左）
-- =============================================
CREATE FUNCTION dbo.Fun_GetEqualsChars_fish1
(
@c1 VARCHAR(50),
@c2 VARCHAR(50)
)
RETURNS INT
AS
BEGIN
DECLARE @r INT,@CombineKey VARCHAR(50)
SET @CombineKey= cast((cast(@c1 as int)+cast(@c2 as int)) as varchar)
SET @r = len(@CombineKey)-len(Replace(REPLACE(@CombineKey,'2',''),0,''))
RETURN @r
END
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
[ID]
,[KEY]
,dbo.Fun_GetEqualsChars([key],'1001001111') AS eualsChars
FROM t
WHERE dbo.Fun_GetEqualsChars([key],'1001001111')>5
SELECT
[ID]
,[KEY]
,dbo.Fun_GetEqualsChars_fish1([key],'1001001111') AS eualsChars
FROM t
WHERE dbo.Fun_GetEqualsChars_fish1([key],'1001001111')>5
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
[ID]
,[KEY]
,dbo.Fun_GetEqualsChars_fish1([key],'1001001111') AS eualsChars
FROM t
WHERE dbo.Fun_GetEqualsChars_fish1([key],'1001001111')>5
SELECT
[ID]
,[KEY]
,dbo.Fun_GetEqualsChars([key],'1001001111') AS eualsChars
FROM t
WHERE dbo.Fun_GetEqualsChars([key],'1001001111')>5
/*
(20000 行受影响)
表 't'。扫描计数 1，逻辑读取 510 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 1250 毫秒，占用时间 = 1309 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
(20000 行受影响)
表 't'。扫描计数 1，逻辑读取 510 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 1125 毫秒，占用时间 = 1253 毫秒。
*/吉普赛的歌 2017-07-05
- 打赏
- 举报
不好意思， 现在才看到他有写代码。
你可以按我上面说的实际比较一下两种方式哪个的消耗更大。
吉普赛的歌 2017-07-05
- 打赏
- 举报
高手
[/quote]你先告诉我：
现在查一次需要多少秒？
你理想中的应该是多少秒？[/quote]
这个一定是比较耗费CPU的吧？一个语句里这么多的 LEN LEFT RIGHT
难道这是很好的方法吗？
不过还要谢谢你！[/quote]
你觉得字符串相加就是高手， 其实你得先问问人家， 要怎么相加？有没有代码提供？
恕我愚昧，只知道字符串相加只可能连成一个长串， 按他的说法， 只可能是将每一位单独分离出来， 再转换类型为 int ，才能相加。 这个操作的消耗很小？
SQL Server （其它数据库也如此） 在应用中本身就是一门实验科学， 很多东西是实验出来的， 不是想象出来的。
当然， 要说没有技巧和原理也不可能， 但实验是第一步。
下面的代码可以衡量 cpu, io 的消耗， 你自己可以放在两种不同的查询语句之前用于比较：
SET STATISTICS IO ON
SET STATISTICS TIME ON
--查询方法1:
-- select .....
--查询方法2:
-- select .....
骑着蜗牛去爬山 2017-07-04
- 打赏
- 举报
create function [dbo].[getequalscharscount](@s1 varchar(50),@s2 varchar(50)) returns int
as
begin
declare @len int;
declare @ct int;
set @len= case when len(@s1)<len(@s2) then len(@s1) else len(@s2) end;
select @s1=reverse(@s1),@s2=reverse(@s2);
select @ct=sum(case when substring(@s1,number,1)=substring(@s2,number,1) then 1 else 0 end) from master..spt_values where type='p' and number between 1 and @len
return @ct;
end
吉普赛的歌 2017-07-04
- 打赏
- 举报
你先告诉我：
现在查一次需要多少秒？
你理想中的应该是多少秒？
andy0618 2017-07-04
- 打赏
- 举报
你确定这是最佳算法吗？
吉普赛的歌 2017-07-04
- 打赏
- 举报
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(ID INT IDENTITY(1,1) PRIMARY KEY, [KEY] VARCHAR(50))
INSERT INTO t([KEY])
SELECT '1011110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0000110100' UNION ALL
SELECT '0010110110'
/*
SELECT * FROM t
ID KEY
1 1011110111
2 0010110111
3 0010110111
4 0000110100
5 0010110110
*/
GO
IF OBJECT_ID('dbo.Fun_GetEqualsChars') IS NOT NULL
DROP FUNCTION dbo.Fun_GetEqualsChars
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-07-04
-- Description: 比较两个字符串的相同字符的个数（从右至左）
-- =============================================
CREATE FUNCTION dbo.Fun_GetEqualsChars
(
@c1 VARCHAR(50),
@c2 VARCHAR(50)
)
RETURNS INT
AS
BEGIN
DECLARE @r INT
SET @r =
CASE WHEN LEN(@c1)>=1 AND LEN(@c2)>=1 AND LEFT(RIGHT(@c1,1),1) = LEFT(RIGHT(@c2,1),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=2 AND LEN(@c2)>=2 AND LEFT(RIGHT(@c1,2),1) = LEFT(RIGHT(@c2,2),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=3 AND LEN(@c2)>=3 AND LEFT(RIGHT(@c1,3),1) = LEFT(RIGHT(@c2,3),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=4 AND LEN(@c2)>=4 AND LEFT(RIGHT(@c1,4),1) = LEFT(RIGHT(@c2,4),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=5 AND LEN(@c2)>=5 AND LEFT(RIGHT(@c1,5),1) = LEFT(RIGHT(@c2,5),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=6 AND LEN(@c2)>=6 AND LEFT(RIGHT(@c1,6),1) = LEFT(RIGHT(@c2,6),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=7 AND LEN(@c2)>=7 AND LEFT(RIGHT(@c1,7),1) = LEFT(RIGHT(@c2,7),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=8 AND LEN(@c2)>=8 AND LEFT(RIGHT(@c1,8),1) = LEFT(RIGHT(@c2,8),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=9 AND LEN(@c2)>=9 AND LEFT(RIGHT(@c1,9),1) = LEFT(RIGHT(@c2,9),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=10 AND LEN(@c2)>=10 AND LEFT(RIGHT(@c1,10),1) = LEFT(RIGHT(@c2,10),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=11 AND LEN(@c2)>=11 AND LEFT(RIGHT(@c1,11),1) = LEFT(RIGHT(@c2,11),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=12 AND LEN(@c2)>=12 AND LEFT(RIGHT(@c1,12),1) = LEFT(RIGHT(@c2,12),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=13 AND LEN(@c2)>=13 AND LEFT(RIGHT(@c1,13),1) = LEFT(RIGHT(@c2,13),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=14 AND LEN(@c2)>=14 AND LEFT(RIGHT(@c1,14),1) = LEFT(RIGHT(@c2,14),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=15 AND LEN(@c2)>=15 AND LEFT(RIGHT(@c1,15),1) = LEFT(RIGHT(@c2,15),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=16 AND LEN(@c2)>=16 AND LEFT(RIGHT(@c1,16),1) = LEFT(RIGHT(@c2,16),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=17 AND LEN(@c2)>=17 AND LEFT(RIGHT(@c1,17),1) = LEFT(RIGHT(@c2,17),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=18 AND LEN(@c2)>=18 AND LEFT(RIGHT(@c1,18),1) = LEFT(RIGHT(@c2,18),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=19 AND LEN(@c2)>=19 AND LEFT(RIGHT(@c1,19),1) = LEFT(RIGHT(@c2,19),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=20 AND LEN(@c2)>=20 AND LEFT(RIGHT(@c1,20),1) = LEFT(RIGHT(@c2,20),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=21 AND LEN(@c2)>=21 AND LEFT(RIGHT(@c1,21),1) = LEFT(RIGHT(@c2,21),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=22 AND LEN(@c2)>=22 AND LEFT(RIGHT(@c1,22),1) = LEFT(RIGHT(@c2,22),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=23 AND LEN(@c2)>=23 AND LEFT(RIGHT(@c1,23),1) = LEFT(RIGHT(@c2,23),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=24 AND LEN(@c2)>=24 AND LEFT(RIGHT(@c1,24),1) = LEFT(RIGHT(@c2,24),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=25 AND LEN(@c2)>=25 AND LEFT(RIGHT(@c1,25),1) = LEFT(RIGHT(@c2,25),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=26 AND LEN(@c2)>=26 AND LEFT(RIGHT(@c1,26),1) = LEFT(RIGHT(@c2,26),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=27 AND LEN(@c2)>=27 AND LEFT(RIGHT(@c1,27),1) = LEFT(RIGHT(@c2,27),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=28 AND LEN(@c2)>=28 AND LEFT(RIGHT(@c1,28),1) = LEFT(RIGHT(@c2,28),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=29 AND LEN(@c2)>=29 AND LEFT(RIGHT(@c1,29),1) = LEFT(RIGHT(@c2,29),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=30 AND LEN(@c2)>=30 AND LEFT(RIGHT(@c1,30),1) = LEFT(RIGHT(@c2,30),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=31 AND LEN(@c2)>=31 AND LEFT(RIGHT(@c1,31),1) = LEFT(RIGHT(@c2,31),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=32 AND LEN(@c2)>=32 AND LEFT(RIGHT(@c1,32),1) = LEFT(RIGHT(@c2,32),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=33 AND LEN(@c2)>=33 AND LEFT(RIGHT(@c1,33),1) = LEFT(RIGHT(@c2,33),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=34 AND LEN(@c2)>=34 AND LEFT(RIGHT(@c1,34),1) = LEFT(RIGHT(@c2,34),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=35 AND LEN(@c2)>=35 AND LEFT(RIGHT(@c1,35),1) = LEFT(RIGHT(@c2,35),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=36 AND LEN(@c2)>=36 AND LEFT(RIGHT(@c1,36),1) = LEFT(RIGHT(@c2,36),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=37 AND LEN(@c2)>=37 AND LEFT(RIGHT(@c1,37),1) = LEFT(RIGHT(@c2,37),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=38 AND LEN(@c2)>=38 AND LEFT(RIGHT(@c1,38),1) = LEFT(RIGHT(@c2,38),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=39 AND LEN(@c2)>=39 AND LEFT(RIGHT(@c1,39),1) = LEFT(RIGHT(@c2,39),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=40 AND LEN(@c2)>=40 AND LEFT(RIGHT(@c1,40),1) = LEFT(RIGHT(@c2,40),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=41 AND LEN(@c2)>=41 AND LEFT(RIGHT(@c1,41),1) = LEFT(RIGHT(@c2,41),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=42 AND LEN(@c2)>=42 AND LEFT(RIGHT(@c1,42),1) = LEFT(RIGHT(@c2,42),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=43 AND LEN(@c2)>=43 AND LEFT(RIGHT(@c1,43),1) = LEFT(RIGHT(@c2,43),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=44 AND LEN(@c2)>=44 AND LEFT(RIGHT(@c1,44),1) = LEFT(RIGHT(@c2,44),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=45 AND LEN(@c2)>=45 AND LEFT(RIGHT(@c1,45),1) = LEFT(RIGHT(@c2,45),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=46 AND LEN(@c2)>=46 AND LEFT(RIGHT(@c1,46),1) = LEFT(RIGHT(@c2,46),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=47 AND LEN(@c2)>=47 AND LEFT(RIGHT(@c1,47),1) = LEFT(RIGHT(@c2,47),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=48 AND LEN(@c2)>=48 AND LEFT(RIGHT(@c1,48),1) = LEFT(RIGHT(@c2,48),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=49 AND LEN(@c2)>=49 AND LEFT(RIGHT(@c1,49),1) = LEFT(RIGHT(@c2,49),1) THEN 1 ELSE 0 END +
CASE WHEN LEN(@c1)>=50 AND LEN(@c2)>=50 AND LEFT(RIGHT(@c1,50),1) = LEFT(RIGHT(@c2,50),1) THEN 1 ELSE 0 END
RETURN @r
END
GO
SELECT
[ID]
,[KEY]
,dbo.Fun_GetEqualsChars([key],'1001001111') AS eualsChars
FROM t
WHERE dbo.Fun_GetEqualsChars([key],'1001001111')>5
/*
ID KEY eualsChars
1 1011110111 6
*/andy0618 2017-07-04
- 打赏
- 举报
你先告诉我：
现在查一次需要多少秒？
你理想中的应该是多少秒？[/quote]
这个一定是比较耗费CPU的吧？一个语句里这么多的 LEN LEFT RIGHT
难道这是很好的方法吗？
不过还要谢谢你！
andy0618 2017-07-04
- 打赏
- 举报
高手
顺势而为1 2017-07-04
- 打赏
- 举报
[code=sql][/
if not object_id(N'Tempdb..#Tmp_Tbl') is null
drop table #Tmp_Tbl
Go
CREATE TABLE #Tmp_Tbl(List_ID INT IDENTITY(1,1) PRIMARY KEY, [SourceKEY] VARCHAR(50))
INSERT INTO #Tmp_Tbl([SourceKEY])
SELECT '1011110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0000110100' UNION ALL
SELECT '0010110110'
GO
if not object_id(N'Tempdb..#Tmp_Result') is null
drop table #Tmp_Result
Go
Select SourceKEY,'1001001111' as ParaKey,
cast((cast(SourceKey as int)+cast('1001001111' as int)) as varchar) as CombineKey
Into #Tmp_Result
From #Tmp_Tbl
Select SourceKey,ParaKey,(len(CombineKey)-len(Replace(REPLACE(CombineKey,'2',''),0,''))) as SameQty
From #Tmp_Result
code][/quote]
顺势而为1 2017-07-04
- 打赏
- 举报
[code=sql][/
if not object_id(N'Tempdb..#Tmp_Tbl') is null
drop table #Tmp_Tbl
Go
CREATE TABLE #Tmp_Tbl(List_ID INT IDENTITY(1,1) PRIMARY KEY, [SourceKEY] VARCHAR(50))
INSERT INTO #Tmp_Tbl([SourceKEY])
SELECT '1011110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0010110111' UNION ALL
SELECT '0000110100' UNION ALL
SELECT '0010110110'
GO
if not object_id(N'Tempdb..#Tmp_Result') is null
drop table #Tmp_Result
Go
Select SourceKEY,'1001001111' as ParaKey,
cast((cast(SourceKey as int)+cast('1001001111' as int)) as varchar) as CombineKey
Into #Tmp_Result
From #Tmp_Tbl
Select SourceKey,ParaKey,(len(CombineKey)-len(Replace(REPLACE(CombineKey,'2',''),0,''))) as SameQty
From #Tmp_Result
code]
顺势而为1 2017-07-04
- 打赏
- 举报
楼主，如果你的字符串全是0,1， 那你可以两个字串相加, 0和2的就是相同的数.
