有这样的一个表ABC:
Field1 Field2 Field3
A 01 Y
A 01 Y
A 01 N B 01 Y
B 01 N C 01 Y
C 01 Y
C 01 Y D 01 N
D 01 Y现在要求这样:
如果Field1中A、B、C、D的Field3中同时存在Y和N值,则把所以行的Field2在原值上加上“-D”,其它不变:
Field1 Field2 Field3
A 01-D Y
A 01-D Y
A 01-D N B 01-D Y
B 01-D N C 01 Y
C 01 Y
C 01 Y D 01-D N
D 01-D Y请问SQL怎么写?
Field1 Field2 Field3
A 01 Y
A 01 Y
A 01 N B 01 Y
B 01 N C 01 Y
C 01 Y
C 01 Y D 01 N
D 01 Y现在要求这样:
如果Field1中A、B、C、D的Field3中同时存在Y和N值,则把所以行的Field2在原值上加上“-D”,其它不变:
Field1 Field2 Field3
A 01-D Y
A 01-D Y
A 01-D N B 01-D Y
B 01-D N C 01 Y
C 01 Y
C 01 Y D 01-D N
D 01-D Y请问SQL怎么写?
From ABC A
Inner Join ABC B On A.Field1 = B.Field1 And B.Field3 = 'Y'
Inner Join ABC C On A.Field1 = C.Field1 And B.Field3 = 'N'
From ABC A
Inner Join ABC B On A.Field1 = B.Field1 And B.Field3 = 'Y'
Inner Join ABC C On A.Field1 = C.Field1 And C.Field3 = 'N'
(Field1 Varchar(10),
Field2 Varchar(10),
Field3 Varchar(10))
Insert ABC Select 'A', '01', 'Y'
Union All Select 'A', '01', 'Y'
Union All Select 'A', '01', 'N'
Union All Select 'B', '01', 'Y'
Union All Select 'B', '01', 'N'
Union All Select 'C', '01', 'Y'
Union All Select 'C', '01', 'Y'
Union All Select 'C', '01', 'Y'
Union All Select 'D', '01', 'N'
Union All Select 'D', '01', 'Y'
GO
Update A Set Field2 = A.Field2 + '-D'
From ABC A
Inner Join ABC B On A.Field1 = B.Field1 And B.Field3 = 'Y'
Inner Join ABC C On A.Field1 = C.Field1 And C.Field3 = 'N'Select * From ABC
GO
Drop Table ABC
--Result
/*
Field1 Field2 Field3
A 01-D Y
A 01-D Y
A 01-D N
B 01-D Y
B 01-D N
C 01 Y
C 01 Y
C 01 Y
D 01-D N
D 01-D Y
*/
drop table tb
gocreate table tb(F1 varchar(10),F2 varchar(10),F3 varchar(10))
insert into tb(F1,F2,F3) values('A', '01', 'Y')
insert into tb(F1,F2,F3) values('A', '01', 'Y')
insert into tb(F1,F2,F3) values('A', '01', 'N')
insert into tb(F1,F2,F3) values('B', '01', 'Y')
insert into tb(F1,F2,F3) values('B', '01', 'N')
insert into tb(F1,F2,F3) values('C', '01', 'Y')
insert into tb(F1,F2,F3) values('C', '01', 'Y')
insert into tb(F1,F2,F3) values('C', '01', 'Y')
insert into tb(F1,F2,F3) values('D', '01', 'N')
insert into tb(F1,F2,F3) values('D', '01', 'Y')update tb
set f2 = f2 + '-D'
from tb,
(
select f1 , count(*) as cnt from
(
select f1,count(*) as cnt from tb where f3 = 'Y' group by f1
union all
select f1,count(*) as cnt from tb where f3 = 'N' group by f1
) t
group by f1
having count(*) > 1
) m
where tb.f1 = m.f1
select * from tbdrop table tb
/*
F1 F2 F3
---------- ---------- ----------
A 01-D Y
A 01-D Y
A 01-D N
B 01-D Y
B 01-D N
C 01 Y
C 01 Y
C 01 Y
D 01-D N
D 01-D Y(所影响的行数为 10 行)*/