急求sql 非常感谢
797EA420-E9E0-428A-A8B4-35B3B11BC838上海组11111
98F591F8-7C58-4611-92ED-8663E61832F8上海组11111
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4上海组10101
97A59D8D-1C5D-4303-8192-9B76CB442F8E上海组00110
97A59D8D-1C5D-4303-8192-9B76CB442F8E上海组11110
017F233A-74D2-4039-90E5-9D944D2B8EEC上海组11110
017F233A-74D2-4039-90E5-9D944D2B8EEC上海组11111
FEF53CF1-C353-4356-9D7A-9E8366A696E7上海组00100
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A上海组11110
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6上海组11110
CA0338C9-F037-45BD-85A8-F6390A7BA6AF上海组11000
想要得到的结果
797EA420-E9E0-428A-A8B4-35B3B11BC838上海组11111
98F591F8-7C58-4611-92ED-8663E61832F8上海组11111
B4745602-02AD-4CB5-8194-9A1DC1CAA4F4上海组10101
97A59D8D-1C5D-4303-8192-9B76CB442F8E上海组11110
017F233A-74D2-4039-90E5-9D944D2B8EEC上海组11111
FEF53CF1-C353-4356-9D7A-9E8366A696E7上海组00100
3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A上海组11110
F387E6A8-9DD7-4278-8AAE-EDBC87461FA6上海组11110
CA0338C9-F037-45BD-85A8-F6390A7BA6AF上海组11000
注意红字部分就行了。
[解决办法]
select distinct * from tb，这个效率不知道怎么样
[解决办法]
--> 测试数据：@TDECLARE @T TABLE([C1] UNIQUEIDENTIFIER,[C2] VARCHAR(6),[C3] INT,[C4] INT,[C5] INT,[C6] INT,[C7] INT)INSERT @TSELECT '797EA420-E9E0-428A-A8B4-35B3B11BC838','上海组',1,1,1,1,1 UNION ALLSELECT '98F591F8-7C58-4611-92ED-8663E61832F8','上海组',1,1,1,1,1 UNION ALLSELECT 'B4745602-02AD-4CB5-8194-9A1DC1CAA4F4','上海组',1,0,1,0,1 UNION ALLSELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',0,0,1,1,0 UNION ALLSELECT '97A59D8D-1C5D-4303-8192-9B76CB442F8E','上海组',1,1,1,1,0 UNION ALLSELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,0 UNION ALLSELECT '017F233A-74D2-4039-90E5-9D944D2B8EEC','上海组',1,1,1,1,1 UNION ALLSELECT 'FEF53CF1-C353-4356-9D7A-9E8366A696E7','上海组',0,0,1,0,0 UNION ALLSELECT '3955D68C-7D7B-4AC7-B6D1-CD4F31983F8A','上海组',1,1,1,1,0 UNION ALLSELECT 'F387E6A8-9DD7-4278-8AAE-EDBC87461FA6','上海组',1,1,1,1,0 UNION ALLSELECT 'CA0338C9-F037-45BD-85A8-F6390A7BA6AF','上海组',1,1,0,0,0SELECT * FROM @T TWHERE 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 198F591F8-7C58-4611-92ED-8663E61832F8 上海组 1 1 1 1 1B4745602-02AD-4CB5-8194-9A1DC1CAA4F4 上海组 1 0 1 0 197A59D8D-1C5D-4303-8192-9B76CB442F8E 上海组 1 1 1 1 0017F233A-74D2-4039-90E5-9D944D2B8EEC 上海组 1 1 1 1 1FEF53CF1-C353-4356-9D7A-9E8366A696E7 上海组 0 0 1 0 03955D68C-7D7B-4AC7-B6D1-CD4F31983F8A 上海组 1 1 1 1 0F387E6A8-9DD7-4278-8AAE-EDBC87461FA6 上海组 1 1 1 1 0CA0338C9-F037-45BD-85A8-F6390A7BA6AF 上海组 1 1 0 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 @ResultENDGO SELECT dbo.fn_CalcBin('11001100' ,'11110000','&') [与], dbo.fn_CalcBin('11001100' ,'11110000','|') [或], dbo.fn_CalcBin('11001100' ,'11110000','^') [异或]
[解决办法]
;with tas(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
[解决办法]
如果你是想用GUID分组，后面的值按“或"求值的话：
select c1,c2,MAX(c3) as c3,MAX(c4) as c4,MAX(c5) as c5,MAX(c6) as c6,MAX(c7) as c7from @T group by C1,C2