如何统计B列里的数据 谢谢
表1 a b 01 1221222212122111121211122211222
02 22222111121211111111111122222211111
03 212121212111111112222212121221111222222
.
.
.
.
.
................. 问题11个1个的为单数 2个以上双数
如01里1221 为2个单数 1个双数 122122221 为3个单数2个双数 03里212121 为6个单数 0个双数
如何统计01里出现单数的次数和双数的次数上面为
01 单数次数:8次 双数8次
02 单数次数:3次 双数5次
03 单数次数:14次 双数5次问题2
B列里的数据如统计特定数出现的次数如122吧01 里出现了4次
02 里出现1次
03 里出现了3次要是1221
01 里出现了1次
02 03没有出现
表1 a b 01 1221222212122111121211122211222
02 22222111121211111111111122222211111
03 212121212111111112222212121221111222222
.
.
.
.
.
................. 问题11个1个的为单数 2个以上双数
如01里1221 为2个单数 1个双数 122122221 为3个单数2个双数 03里212121 为6个单数 0个双数
如何统计01里出现单数的次数和双数的次数上面为
01 单数次数:8次 双数8次
02 单数次数:3次 双数5次
03 单数次数:14次 双数5次问题2
B列里的数据如统计特定数出现的次数如122吧01 里出现了4次
02 里出现1次
03 里出现了3次要是1221
01 里出现了1次
02 03没有出现
RETURNS INT
AS
BEGINDECLARE @SQL VARCHAR(100)
DECLARE @CNT INTSET @CNT=0
SET @SQL=@STRWHILE CHARINDEX('122', @SQL)>0
BEGIN
SET @CNT=@CNT+1
SET @SQL=STUFF(@SQL,CHARINDEX('122', @SQL),LEN('122')-1,'X')
END
RETURN @CNT
END
GO
DECLARE @TB TABLE(a VARCHAR(2), b VARCHAR(100))
INSERT @TB
SELECT '01', '1221222212122111121211122211222' UNION ALL
SELECT '02', '22222111121211111111111122222211111' UNION ALL
SELECT '03', '212121212111111112222212121221111222222'SELECT *,DBO.F_COUNT(B) AS NUMBER FROM @TBDROP FUNCTION F_COUNT
/*
a b NUMBER
---- ---------------------------------------------------------------------------------------------------- -----------
01 1221222212122111121211122211222 5
02 22222111121211111111111122222211111 1
03 212121212111111112222212121221111222222 3
*/
RETURNS INT
AS
BEGINDECLARE @SQL VARCHAR(100)
DECLARE @CNT INTSET @CNT=0
SET @SQL=@STRWHILE CHARINDEX(@STR2, @SQL)>0
BEGIN
SET @CNT=@CNT+1
SET @SQL=STUFF(@SQL,CHARINDEX(@STR2, @SQL),LEN(@STR2)-1,'X')
END
RETURN @CNT
END
GO
DECLARE @TB TABLE(a VARCHAR(2), b VARCHAR(100))
INSERT @TB
SELECT '01', '1221222212122111121211122211222' UNION ALL
SELECT '02', '22222111121211111111111122222211111' UNION ALL
SELECT '03', '212121212111111112222212121221111222222'SELECT *,DBO.F_COUNT(B,'122') AS NUMBER FROM @TBDROP FUNCTION F_COUNT
/*
a b NUMBER
---- ---------------------------------------------------------------------------------------------------- -----------
01 1221222212122111121211122211222 5
02 22222111121211111111111122222211111 1
03 212121212111111112222212121221111222222 3
*/
go
create table [表1]([a] varchar(2),[b] varchar(39))
insert [表1]
select '01','1221222212122111121211122211222' union all
select '02','22222111121211111111111122222211111' union all
select '03','212121212111111112222212121221111222222'select * from [表1]--Create the function to get the count.
create function fn_cal(@s nvarchar(1000),@f tinyint)
returns int
begin
if isnull(@s,'')='' return 0
--declaration
declare @s1 nvarchar(1000),@s2 nvarchar(1000),@s3 nvarchar(1000),@cnt1 int,@cnt2 int,@cnt int
--initialization,assuming leng(@s)>=3
select @cnt1=0,@cnt2=0,@s1=left(@s,1),@s2=substring(@s,2,1),@s3=substring(@s,3,1),@s=right(@s,len(@s)-3)
if @s1=@s2 or @s2=@s3 set @cnt2=1
if @s1<>@s2 set @cnt1=1
if @s2<>@s3 set @cnt1=@cnt1+1
--pattern matching
while len(@s)>0
begin
select @s1=@s2,@s2=@s3,@s3=left(@s,1),@s=right(@s,len(@s)-1)
if len(@s)>0
begin
if @s1<>@s2 and @s2<>@s3 set @cnt1=@cnt1+1
if @s1<>@s2 and @s2=@s3 set @cnt2=@cnt2+1
end
else
begin
if @s1<>@s2 and @s2<>@s3 set @cnt1=@cnt1+2
if @s1=@s2 and @s2<>@s3 set @cnt1=@cnt1+1
if @s1<>@s2 and @s2=@s3 set @cnt2=@cnt2+1
break
end
end
--return count
set @cnt=case @f when 1 then @cnt1 else @cnt2 end
return @cnt
end
go--test:
select a,[单数次数]=dbo.fn_cal(b,1),[双数次数]=dbo.fn_cal(b,2) from [表1]
/*
a 单数次数 双数次数
---- ----------- -----------
01 8 8
02 3 5
03 14 5(3 row(s) affected)
*/
if object_id(N'fn_cal',N'FN') is not null
drop function fn_cal
go
create function fn_cal(@s nvarchar(4000),@f tinyint)
returns int
begin
if isnull(@s,'')='' return 0
--declaration
declare @s1 nvarchar(1),@s2 nvarchar(1),@s3 nvarchar(1),@cnt1 int,@cnt2 int
--initialization,assuming leng(@s)>=3
select @cnt1=0,@cnt2=0,@s1=left(@s,1),@s2=substring(@s,2,1),@s3=substring(@s,3,1),@s=right(@s,len(@s)-3)
if @s1=@s2 or @s2=@s3 set @cnt2=1
if @s1<>@s2 set @cnt1=1
if @s2<>@s3 set @cnt1=@cnt1+1
--pattern matching
while len(@s)>0
begin
select @s1=@s2,@s2=@s3,@s3=left(@s,1),@s=right(@s,len(@s)-1)
if @s1<>@s2 and @s2<>@s3 set @cnt1=@cnt1+1
if @s1<>@s2 and @s2=@s3 set @cnt2=@cnt2+1
if len(@s)=0 and @s2<>@s3 set @cnt1=@cnt1+1
end
--return count
return case @f when 1 then @cnt1 else @cnt2 end
end
go
--test:
select a,[单数次数]=dbo.fn_cal(b,1),[双数次数]=dbo.fn_cal(b,2) from [表1]
/*
a 单数次数 双数次数
---- ----------- -----------
01 8 8
02 3 5
03 14 5(3 row(s) affected)
*/
INSERT @t SELECT '01','1221222212122111121211122211222'
UNION ALL SELECT '02','22222111121211111111111122222211111'
UNION ALL SELECT '03','212121212111111112222212121221111222222'
--SELECT * FROM @t--为了方便转换为2000所以像以下这么写,如果只在2005下适用更简单
DECLARE @n INT
SELECT @n=MAX(LEN(b)) FROM @t --加这个为了效率考率,其实可以不要
SELECT TOP(@n) IDENTITY(INT) id INTO # FROM sys.objects,sys.columns --sql2000用sysobjects,syscolumns,并去掉top(@n) 改用set rowcount @nSELECT *,SUBSTRING(b,id,1) v INTO #1 FROM @t a
INNER JOIN # b
ON LEN(b)>=idSELECT a.a,'双数个数' + RTRIM(SUM(CASE WHEN b.v%2=0 THEN 1 ELSE 0 END)) +','+'单数个数:' + RTRIM(SUM(CASE WHEN b.v%2=1 THEN 1 ELSE 0 END)) FROM
(SELECT DISTINCT a,gid=(SELECT MIN(id) FROM
(SELECT b.id FROM #1 b WHERE b.a=a.a AND b.id<=a.id AND b.v%2=a.v%2
AND NOT EXISTS(
SELECT 1 FROM #1 c WHERE c.a=a.a AND c.id<a.id AND c.id>b.id AND c.v%2!=b.v%2
)
) x
)
FROM #1 a
) a
INNER JOIN
#1 b
ON a.a=b.a AND a.gid=b.id
GROUP BY a.a/*
01 双数个数8,单数个数:8
02 双数个数4,单数个数:4
03 双数个数10,单数个数:9
纯sql2005的写法将更简单
*/
DROP TABLE #,#1
我本想用identity+replace的方法做一个表级的匹配,
无奈函数里不允许使用临时表,只好作罢。
INSERT @t SELECT '01','1221222212122111121211122211222'
UNION ALL SELECT '02','22222111121211111111111122222211111'
UNION ALL SELECT '03','212121212111111112222212121221111222222'
--SELECT * FROM @t--为了方便转换为2000所以像以下这么写,如果只在2005下适用更简单
DECLARE @n INT
SELECT @n=MAX(LEN(b)) FROM @t --加这个为了效率考率,其实可以不要
SELECT TOP(@n) IDENTITY(INT) id INTO # FROM sys.objects,sys.columns --sql2000用sysobjects,syscolumns,并去掉top(@n) 改用set rowcount @nSELECT *,SUBSTRING(b,id,1) v INTO #1 FROM @t a
INNER JOIN # b
ON LEN(b)>=id
SELECT a,'双次' + RTRIM(SUM(CASE WHEN cnt>1 THEN 1 ELSE 0 END)), '单次:' +RTRIM(SUM(CASE WHEN cnt=1 THEN 1 ELSE 0 END))
FROM
(
SELECT a,gid,COUNT(*) CNT FROM
(
SELECT *,gid=(SELECT MIN(id) FROM
(SELECT id FROM #1 b WHERE v=a.v AND a=a.a AND id<=a.id
AND NOT EXISTS
(
SELECT 1 FROM #1 WHERE id<a.id AND id>b.id AND v!=a.v AND a=a.a
)
) x
)
FROM #1 a
) a
GROUP BY a,gid
) a
GROUP BY a/*
01 双次8 单次:8
02 双次5 单次:3
03 双次5 单次:14
*/
DROP TABLE #,#1
谢谢您的回复
sql2000用sysobjects,syscolumns,并去掉top(@n) 改用set rowcount @n为什么提示set错误?
DECLARE @t TABLE(a VARCHAR(10),b VARCHAR(200))
INSERT @t SELECT '01','1221222212122111121211122211222'
UNION ALL SELECT '02','22222111121211111111111122222211111'
UNION ALL SELECT '03','212121212111111112222212121221111222222'
--SELECT * FROM @t--为了方便转换为2000所以像以下这么写,如果只在2005下适用更简单
DECLARE @n INT
SELECT @n=MAX(LEN(b)) FROM @t --加这个为了效率考率,其实可以不要
SET ROWCOUNT @n
SELECT IDENTITY(INT) id INTO # FROM sysobjects,syscolumns
SET ROWCOUNT 0SELECT *,SUBSTRING(b,id,1) v INTO #1 FROM @t a
INNER JOIN # b
ON LEN(b)>=id
SELECT a,'双次' + RTRIM(SUM(CASE WHEN cnt>1 THEN 1 ELSE 0 END)), '单次:' +RTRIM(SUM(CASE WHEN cnt=1 THEN 1 ELSE 0 END))
FROM
(
SELECT a,gid,COUNT(*) CNT FROM
(
SELECT *,gid=(SELECT MIN(id) FROM
(SELECT id FROM #1 b WHERE v=a.v AND a=a.a AND id<=a.id
AND NOT EXISTS
(
SELECT 1 FROM #1 WHERE id<a.id AND id>b.id AND v!=a.v AND a=a.a
)
) x
)
FROM #1 a
) a
GROUP BY a,gid
) a
GROUP BY a/*
01 双次8 单次:8
02 双次5 单次:3
03 双次5 单次:14*/
DROP TABLE #,#1
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^1]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^1]%',@s),1,'')
END
RETURN @S
ENDgocreate FUNCTION [dbo].[up_getstr2](@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^2]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^2]%',@s),1,'')
END
RETURN @S
ENDgodeclare @tb table (a char(2),b varchar(200))
insert into @tb (a,b)
(select '01','1221222212122111121211122211222' union all
select '02','22222111121211111111111122222211111' union all
select '03','212121212111111112222212121221111222222')select a,'单数次数:'+convert(varchar(2),len(dbo.up_getstr1(b)))+'次 双数次数:'+convert(varchar(2),len(dbo.up_getstr2(b)))
from @tbdrop function [dbo].[up_getstr1]
drop function [dbo].[up_getstr2]
非常感谢您的回复,'双次' + RTRIM(SUM(CASE WHEN cnt>1 THEN 1 ELSE 0 END)), '单次:' +RTRIM(SUM(CASE WHEN cnt=1 THEN 1 ELSE 0 END))
有个问题就是我把代码放进程序里,查询出来是2个单数次数,要是双数放后面,那查询出来是2个双数次,查询出来是后面的,1次查询不能同时出单数次数和双数次数
要分别查询才能查出来,如何处理?谢谢您
DECLARE @t TABLE(a VARCHAR(10),b VARCHAR(200))
INSERT @t SELECT '01','1221222212122111121211122211222'
UNION ALL SELECT '02','22222111121211111111111122222211111'
UNION ALL SELECT '03','212121212111111112222212121221111222222'--为了方便转换为2000所以像以下这么写,如果只在2005下适用更简单
DECLARE @n INT
SELECT @n=MAX(LEN(b)) FROM @t --加这个为了效率考率,其实可以不要
SET ROWCOUNT @n
SELECT IDENTITY(INT, 1, 1) id INTO # FROM sysobjects,syscolumns
SET ROWCOUNT 0-- 查询
SELECT
A.a,
单次数 = SUM(
CASE
WHEN SUBSTRING(a.b, B.id - 1, 1) = SUBSTRING(a.b, B.id, 1)
THEN 0
ELSE 1
END),
双次数 = SUM(
CASE
WHEN SUBSTRING(a.b, B.id - 1, 1) = SUBSTRING(a.b, B.id, 1)
THEN 1
ELSE 0
END)
FROM @t A, # B
WHERE SUBSTRING(a.b, B.id, 1) <> SUBSTRING(a.b, B.id + 1, 1)
GROUP BY A.a;-- 删除临时表
DROP TABLE #;
在13楼时理解楼主的需求产生了问题,导至了22楼写语句时的惯性思维。 没有跳出以前(13楼)的影子, 因为以连续性为依具,那么确实没必要那样去exists判断。老大的思路很清晰, 跳出三界外,不在五行中,见识了
where a between 300 and 600 加入那里?
A.a,
单次数 = SUM(
CASE
WHEN SUBSTRING(a.b, B.id - 1, 1) = SUBSTRING(a.b, B.id, 1)
THEN 0
ELSE 1
END),
双次数 = SUM(
CASE
WHEN SUBSTRING(a.b, B.id - 1, 1) = SUBSTRING(a.b, B.id, 1)
THEN 1
ELSE 0
END)
FROM @t A, # B
WHERE SUBSTRING(a.b, B.id, 1) <> SUBSTRING(a.b, B.id + 1, 1)
AND A.a BETWEEN 300 and 600 -- 对 a 过滤
GROUP BY A.a;