请教大家一个问题:MS SQL中的Group By,有没有“与或”的功能?比如以下的例子,FID和Code是字段名,其中Code是字符串类型,长度为4。其值由“1”和“0”两个字符组成。我希望能将FID值相同的记录的Code的值进行“与或”处理,即同位置上有任意一个“1”的保留“1”,全部为“0”的则取“0”,如下所示:
FID Code
A 1010
A 1100
B 0011
B 1001结果:
FID Code
A 1110
B 1011不知道有没办法处理呢?
FID Code
A 1010
A 1100
B 0011
B 1001结果:
FID Code
A 1110
B 1011不知道有没办法处理呢?
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 行受影响)*/
也许行
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 行受影响)
*/
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不定长度的一种处理方式
我刚才采用了另外一种方法,按位拆分后用max来处理。我试试各位的代码,比较下那种方式效率更高。再次感谢各位的帮助。