现表中有如下几条记录
A B C D E F
00COMMH3 6222610008571002 淑山 885062 LPS 600
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 884868 LERB 300
00CBP3 6029071032483 德好 888687 LPP3 100能够用SQL语句查询得到如下的结果
A B C D E F
00COMMH3 6222610008571002 淑山 885062 LPS 600
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 888888 SSSS 400需求就是如果表中有两条或两条以上记录的字段A,B,C同时相同,
就把相同记录的字段F的值相加,D取常量888888,F取常量SSSS,
其他的无字段A,B,C同时重复的记录保存不变
A B C D E F
00COMMH3 6222610008571002 淑山 885062 LPS 600
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 884868 LERB 300
00CBP3 6029071032483 德好 888687 LPP3 100能够用SQL语句查询得到如下的结果
A B C D E F
00COMMH3 6222610008571002 淑山 885062 LPS 600
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 888888 SSSS 400需求就是如果表中有两条或两条以上记录的字段A,B,C同时相同,
就把相同记录的字段F的值相加,D取常量888888,F取常量SSSS,
其他的无字段A,B,C同时重复的记录保存不变
FROM TB WHERE A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL
GROUP BY A,B,C
UNION ALL
SELECT A,B,C,D,E,F FROM TB WHERE A IS NULL OR B IS NULL OR C IS NULL
00COMMH3 6222610008571002 淑山 885062 LPS 600
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 888888 SSSS 400
ssss 是怎么来的
SELECT
T1.*
FROM
TB T1
JOIN
(
SELECT
A,B,C,
CASE WHEN EXISTS(SELECT 1 FROM TB WHERE A=T.A AND B=T.B AND C=T.C AND D<>T.D)
THEN 88888 ELSE D END AS D ,SUM(F)F
FROM TB T GROUP BY A,B,C ) AS T2ON T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
from tb
where a in(
SELECT a
FROM tb
GROUP BY a,B,c
having COUNT(1)>1
)
group by A,B,c
union all
select *
from tb
group by a,B,c
having COUNT(1)=1
select *
from tb
group by a,B,c
having COUNT(1)=1
這個可以執行嗎?
/***********************************************--> 测试数据:[TB]
--> 测试时间:2009-08-10 16:38:17
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[TB]') is not null drop table [TB]
create table [TB]([A] varchar(8),[B] varchar(30),[C] varchar(4),[D] int,[E] varchar(4),[F] int)
insert [TB]
select '00COMMH3',6222610008571002,'淑山',885062,'LPS',600 union all
select '00CB23',955880159168380,'康明',882869,'LPP2',200 union all
select '00CBP3',6029071032483,'德好',884868,'LERB',300 union all
select '00CBP3',6029071032483,'德好',888687,'LPP3',100select A=max(A),
B=min(B),
C=max(C),
D=case when sum(F)>max(F) then 8888 else max(D) end,
e=case when sum(F)>max(F) then 'ssss' else max(e) end,
F=sum(F)
from(select *,flag=A+B+c from TB)t group by flag
/*
A B C D e F
-------- ------------------------------ ---- ----------- ---- -----------
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 8888 ssss 400
00COMMH3 6222610008571002 淑山 885062 LPS 600(3 行受影响)*/drop table TB
from tb
where a in(
SELECT a
FROM tb
GROUP BY a,B,c
having COUNT(1)>1
)
group by A,B,c union
select *
from tb
where a in
( select a from tb
group by a,B,c
having COUNT(1)=1)
T1.*
FROM
TB T1
JOIN
(
SELECT
A,B,C,
CASE WHEN EXISTS
(SELECT 1 FROM TB WHERE A=T.A AND B=T.B AND C=T.C AND D<>T.D)
THEN 88888 ELSE D END AS D ,
CASE WHEN EXISTS
(SELECT 1 FROM TB WHERE A=T.A AND B=T.B AND C=T.C AND D<>T.D) THEN 'SSSSS' ELSE E END AS E,
SUM(F)F
FROM TB T GROUP BY A,B,C ) AS T2ON T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C
加上那个E,
if object_id('ta')is not null drop table ta
go
create table ta(a varchar(12),b varchar(18),c varchar(12),d varchar(12),e varchar(12),f int)
go
insert ta select
'00COMMH3', '6222610008571002' , '淑山' , '885062' , 'LPS' , 600 union all select
'00CB23' , '955880159168380' , '康明', '882869', 'LPP2', 200 union all select
'00CBP3' , '6029071032483' , '德好' , '884868' , 'LERB', 300 union all select
'00CBP3' , '6029071032483' , '德好', '888687', 'LPP3', 100
select t.a,t.b,t.c,
d=case when tt.d='888888' then '888888' else t.d end,
e=case when tt.e='ssss' then 'ssss' else t.e end,f
from (
select a,b,c,
d=max(d),e=max(e),
f=sum(f) from ta group by a,b,c )t
full join (
select a,b,c,d='888888',e='ssss' from ta group by a,b,c having(count(*)>1)
)tt
on t.a=tt.a and t.b=tt.b and t.c=tt.ca b c d e f
------------ ------------------ ------------ ------------ ------------ -----------
00CB23 955880159168380 康明 882869 LPP2 200
00CBP3 6029071032483 德好 888888 ssss 400
00COMMH3 6222610008571002 淑山 885062 LPS 600(3 行受影响)
select distinct A,B,c,d='888888',E='SSSS',SUM(f) as f
from tb
where a in(
SELECT a
FROM tb
GROUP BY a,B,c
having COUNT(1)>1
)
group by A,B,c union
select *
from tb
where a in
( select a from tb
group by a,B,c
having COUNT(1)=1)
Fish as(
Select A,B,C,'888888' as d,'SSSS' as e , Sum(Isnull(F,0)) AS F
From tb
Group by a,b,c
Having Count(*) >= 2
)
Select b.*
From (Select a,b,c
From Tb
Except
Select a,b,c From fish) a,tb b
where a.a = b.a and a.b = b.b and a.c = b.c
Union all
Select * From Fish