22,206
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([FID] [nvarchar](10),[Code] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A','1010' UNION ALL
SELECT 'A','1100' UNION ALL
SELECT 'B','0011' UNION ALL
SELECT 'B','1001' UNION ALL
SELECT 'C','101101' UNION ALL
SELECT 'C','101001' UNION ALL
SELECT 'C','100001'
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY FID, Code ORDER BY GETDATE()),FID,Code,
[Code1]=SUBSTRING(a.Code,b.number+1,1)
FROM tb a,master..spt_values b
WHERE b.type='p' AND LEN(a.Code)>b.number
),
t1 AS
(
SELECT DISTINCT rn,FID,SIGN(SUM(CAST([Code1] AS INT))OVER(PARTITION BY rn,FID)) Code1
FROM t
)
SELECT DISTINCT FID,Code=(SELECT ''+LTRIM(Code1) FROM t1 WHERE FID=a.FID ORDER BY rn FOR XML PATH(''))
FROM t1 a
/*
FID Code
---------- -----------------
A 1110
B 1011
C 101101
(3 行受影响)
*/
2005不定长度的一种处理方式--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([FID] [nvarchar](10),[Code] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A','1010' UNION ALL
SELECT 'A','1100' UNION ALL
SELECT 'B','0011' UNION ALL
SELECT 'B','1001'
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT fid, [Code]=
LTRIM(SIGN(SUM(CAST(SUBSTRING(Code, 1, 1) AS INT)))) +
LTRIM(SIGN(SUM(CAST(SUBSTRING(Code, 2, 1) AS INT)))) +
LTRIM(SIGN(SUM(CAST(SUBSTRING(Code, 3, 1) AS INT)))) +
LTRIM(SIGN(SUM(CAST(SUBSTRING(Code, 4, 1) AS INT))))
FROM tb
GROUP BY fid
/*
fid Code
---------- ------------------------------------------------
A 1110
B 1011
(2 行受影响)
*/
--建立测试环境
declare @tb TABLE
(
FID varchar(1),
Code varchar(4)
)
INSERT @tb
select 'A','1010' union all
select 'A','1100' union all
select 'B','0011' union all
select 'B', '1001'
--查询
select fid,
ltrim(case when sum(cast(substring(Code,1,1) as int))>0 then 1 else 0 end) +
ltrim(case when sum(cast(substring(Code,2,1) as int))>0 then 1 else 0 end) +
ltrim(case when sum(cast(substring(Code,3,1) as int))>0 then 1 else 0 end) +
ltrim(case when sum(cast(substring(Code,4,1) as int))>0 then 1 else 0 end) as code
from @tb
group by fid
--结果
/*
(4 行受影响)
fid code
---- ------------------------------------------------
A 1110
B 1011
(2 行受影响)
*/