797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 0 0 1 1 0
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0想要得到的结果797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0注意红字部分就行了。
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 0 0 1 1 0
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0想要得到的结果797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0注意红字部分就行了。
--> 测试数据:@T
DECLARE @T TABLE([C1] UNIQUEIDENTIFIER,[C2] VARCHAR(6),[C3] INT,[C4] INT,[C5] INT,[C6] INT,[C7] INT)
INSERT @T
SELECT '797EA420-E9E0-428A-A8B4-35B3B11BC838','上海组',1,1,1,1,1 UNION ALL
SELECT '98F591F8-7C58-4611-92ED-8663E61832F8','上海组',1,1,1,1,1 UNION ALL
SELECT 'B4745602-02AD-4CB5-8194-9A1DC1CAA4F4','上海组',1,0,1,0,1 UNION ALL
SELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',0,0,1,1,0 UNION ALL
SELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',1,1,1,1,0 UNION ALL
SELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,0 UNION ALL
SELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,1 UNION ALL
SELECT 'FEF53CF1-C353-4356-9D7A-9E8366A696E7','上海组',0,0,1,0,0 UNION ALL
SELECT '3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A','上海组',1,1,1,1,0 UNION ALL
SELECT 'F387E6A8-9DD7-4278-8AAE-EDBC87461FA6','上海组',1,1,1,1,0 UNION ALL
SELECT 'CA0338C9-F037-45BD-85A8-F6390A7BA6AF','上海组',1,1,0,0,0SELECT * FROM @T T
WHERE C3+C4+C5+C6+C7=(SELECT MAX(C3+C4+C5+C6+C7) FROM @T WHERE C1=T.C1)
/*
C1 C2 C3 C4 C5 C6 C7
------------------------------------ ------ ----------- ----------- ----------- ----------- -----------
797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0
*/
if object_id('[test]') is not null drop table [test]
go
create table [test](
[A] varchar(36),
[B] varchar(6),
[C] int,
[D] int,
[E] int,
[F] int,
[G] int
)
insert [test]
select '797EA420-E9E0-428A-A8B4-35B3B11BC838','上海组',1,1,1,1,1 union all
select '98F591F8-7C58-4611-92ED-8663E61832F8','上海组',1,1,1,1,1 union all
select 'B4745602-02AD-4CB5-8194-9A1DC1CAA4F4','上海组',1,0,1,0,1 union all
select '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',0,0,1,1,0 union all
select '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',1,1,1,1,0 union all
select '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,0 union all
select '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,1 union all
select 'FEF53CF1-C353-4356-9D7A-9E8366A696E7','上海组',0,0,1,0,0 union all
select '3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A','上海组',1,1,1,1,0 union all
select 'F387E6A8-9DD7-4278-8AAE-EDBC87461FA6','上海组',1,1,1,1,0 union all
select 'CA0338C9-F037-45BD-85A8-F6390A7BA6AF','上海组',1,1,0,0,0
gowith t
as(
select px=row_number()over(partition by A order by C DESC,D DESC,E DESC,F DESC,G DESC)
,* FROM test
)
select A,B,C,D,E,F,G from t where px=1/*
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
*/
CREATE FUNCTION fn_CalcBin(@cN1 VARCHAR(8000),@cN2 VARCHAR(8000),@Sign VARCHAR(5))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)
DECLARE @M INT
DECLARE @TMaxc VARCHAR(8000),@TMinc VARCHAR(8000)
DECLARE @i INT
IF ISNULL(@cN1,'')='' OR ISNULL(@cN2,'')=''
SET @Result=ISNULL(@cN1,'')+ISNULL(@cN2,'')
ELSE
BEGIN
SELECT @M=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN LEN(@cN1) ELSE LEN(@cN2) END,
@TMaxc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN @cN1 ELSE @cN2 END,
@TMinc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN REPLICATE('0',LEN(@cN1)-LEN(@cN2))+@cN2
ELSE REPLICATE('0',LEN(@cN2)-LEN(@cN1))+@cN1 END
SELECT @i=1,@Result=''
WHILE @i<=@M
BEGIN
SET @Result=@Result+
CASE WHEN @Sign='&' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) & CAST(SUBSTRING(@TMinc,@i,1) AS INT))
WHEN @Sign='|' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) | CAST(SUBSTRING(@TMinc,@i,1) AS INT))
WHEN @Sign='^' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) ^ CAST(SUBSTRING(@TMinc,@i,1) AS INT))
END
SET @i=@i+1
END
END
RETURN @Result
ENDGO
SELECT dbo.fn_CalcBin('11001100' ,'11110000','&') [与],
dbo.fn_CalcBin('11001100' ,'11110000','|') [或],
dbo.fn_CalcBin('11001100' ,'11110000','^') [异或]
先查找出相同记录的行,然后第三列进行或运算。最后再left join 查出 你想要的结果
797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
91BAD726-34B9-44B7-ABA6-81AED8D388B4 北京组 0 0 0 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 北京组 0 0 0 0 0
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 0 0 1 1 0
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
0C3FE9ED-F6AF-433B-B721-AEDA3D09EBF8 北京组 0 0 0 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0运行叶子的SQL得到的结果
6A2E1BA1-8271-4437-AF1D-2CDA40FE95FC 北京组 0 0 1 1 0
797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
91BAD726-34B9-44B7-ABA6-81AED8D388B4 北京组 0 0 0 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
0C3FE9ED-F6AF-433B-B721-AEDA3D09EBF8 北京组 0 0 0 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0结果北京组少了一条记录,其实合并的前提是前面的GUID要一样
叶子再麻烦你了。
select *,row_id=row_number()over(partition by col1,col2 order by col3 DESC,col4 DESC,col5 DESC,col6 DESC)
,* FROM test
)
select * from t where row_id=1
from @T
group by C1,C2
-->try
--> 测试数据:@T
DECLARE @T TABLE([C1] UNIQUEIDENTIFIER,[C2] NVARCHAR(6),[C3] INT,[C4] INT,[C5] INT,[C6] INT,[C7] INT)
INSERT @T
SELECT '797EA420-E9E0-428A-A8B4-35B3B11BC838',N'上海组',1,1,1,1,1 UNION ALL
SELECT '98F591F8-7C58-4611-92ED-8663E61832F8',N'上海组',1,1,1,1,1 UNION ALL
SELECT 'B4745602-02AD-4CB5-8194-9A1DC1CAA4F4',N'上海组',1,0,1,0,1 UNION ALL
SELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E',N'上海组',0,0,1,1,0 UNION ALL
SELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E',N'上海组',1,1,1,1,0 UNION ALL
SELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC',N'上海组',1,1,1,1,0 UNION ALL
SELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC',N'上海组',1,1,1,1,1 UNION ALL
SELECT 'FEF53CF1-C353-4356-9D7A-9E8366A696E7',N'上海组',0,0,1,0,0 UNION ALL
SELECT '3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A',N'上海组',1,1,1,1,0 UNION ALL
SELECT 'F387E6A8-9DD7-4278-8AAE-EDBC87461FA6',N'上海组',1,1,1,1,0 UNION ALL
SELECT 'CA0338C9-F037-45BD-85A8-F6390A7BA6AF',N'上海组',1,1,0,0,0
;with cte as
(
select row_number() over(partition by C1 order by C1) rn,* from @T a
where exists(select 1 from @T where a.C1=C1 group by C1 having count(1)>1)
)
select distinct a.C1,a.C2,a.C3|b.C3 C3,a.C4|b.C4 C4,a.C5|b.C5 C5,a.C6|b.C6 C6,a.C7|b.C7 C7 from cte a,cte b
where a.rn<>b.rn and a.C1=b.C1
union all
select * from @T t
where not exists(select 1 from cte where t.C1=C1)
/*
C1 C2 C3 C4 C5 C6 C7
------------------------------------ ------ ----------- ----------- ----------- ----------- -----------
97A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0
017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1
797EA420-E9E0-428A-A8B4-35B3B11BC838 上海组 1 1 1 1 1
98F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 1
FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 0
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0
CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 0 0
*/
if object_id('[test]') is not null drop table [test]
go
create table [test](
[A] varchar(36),
[B] varchar(6),
[C] int,
[D] int,
[E] int,
[F] int,
[G] int
)
insert [test]
select '797EA420-E9E0-428A-A8B4-35B3B11BC838','上海组',1,1,1,1,1 union all
select '98F591F8-7C58-4611-92ED-8663E61832F8','上海组',1,1,1,1,1 union all
select 'B4745602-02AD-4CB5-8194-9A1DC1CAA4F4','上海组',1,0,1,0,1 union all
select '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',0,0,1,1,0 union all
select '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',1,1,1,1,0 union all
select '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,0 union all
select '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,1 union all
select 'FEF53CF1-C353-4356-9D7A-9E8366A696E7','上海组',0,0,1,0,0 union all
select '3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A','上海组',1,1,1,1,0 union all
select 'F387E6A8-9DD7-4278-8AAE-EDBC87461FA6','上海组',1,1,1,1,0 union all
select 'CA0338C9-F037-45BD-85A8-F6390A7BA6AF','上海组',1,1,0,0,0
goSELECT t.A,t.B,case when SUM(t.C)>0 THEN 1 ELSE 0 END AS C,
case when SUM(t.D)>0 THEN 1 ELSE 0 END AS D,
case when SUM(t.E)>0 THEN 1 ELSE 0 END AS E,
case when SUM(t.F)>0 THEN 1 ELSE 0 END AS F,
case when SUM(t.G)>0 THEN 1 ELSE 0 END AS G
FROM test t
GROUP BY t.A,t.B
--SELECT * FROM test t
结贴结贴!!!!
WHERE C3+C4+C5+C6+C7=(SELECT MAX(C3+C4+C5+C6+C7) FROM @T WHERE C1=T.C1)