寻高手解些问题
表A:ZT(帐套) ID(编号) NAME(名称)
zt001 02001 ...
zt001 02002 ...
zt001 02003 ...
zt001 02004 ...
zt001 02005 ...
zt002 03 ...
zt003 023 ...
..... ... ...
表B:S_ZT(原来的帐套) S_ID(原来的编号) D_ID(现在的编号)
zt001 02001 8888 原来zt001帐套的02001转为8888
zt001 02002 9999 原来zt001帐套的02002转为9999
zt001 all 1234 表示余下的全部转为1234
zt002 03 3434 原来zt002帐套的03转为3434
写select把表A的ID按表B的要求返回
[解决办法]
这样吗！？
select case when ZT= 'zt001 ' and ID= '02001 ' then '8888 '
when ZT= 'zt001 ' and ID= '02002 ' then '9999 '
when ZT= 'zt001 ' and ID= '03 ' then '3434 '
else '1234 ' end as D_ID
from 表A
[解决办法]
select S_ZT,S_ID,D_ID =
case when (S_ZT= 'zt001 ' and S_ID= '02001 ') then '8888 '
case when (S_ZT= 'zt001 ' and S_ID= '02002 ') then '9999 '
case when (S_ZT= 'zt002 ' and S_ID= '03 ') then '3434 '
else '1234 '
end
from 表A
[解决办法]
update A set
ID= '8888 '
where ZT= 'zt001 ' and ID= '02001 '
update A set
ID= '9999 '
where ZT= 'zt001 ' and ID= '02002 '
update A set
ID= '1234 '
where ZT= 'zt001 ' and ID <> '8888 ' and ID <> '9999 '
update A set
ID= '3434 '
where ZT= 'zt002 ' and ID= '03 '
[解决办法]
select case when ZT= 'zt001 ' and ID= '02001 ' then '8888 '
when ZT= 'zt001 ' and ID= '02002 ' then '9999 '
when ZT= 'zt002 ' and ID= '03 ' then '3434 '
when ZT= 'zt001 ' and ID <> '02001 'and ID <> '02002 'and ID <> '03 ' then '1234 '
from 表A
[解决办法]
create table A
(ZT VARCHAR(10), ID VARCHAR(10))
INSERT INTO A
SELECT 'zt001 ', '02001 ' UNION ALL
SELECT 'zt001 ', '02002 ' UNION ALL
SELECT 'zt001 ', '02003 ' UNION ALL
SELECT 'zt001 ', '02004 ' UNION ALL
SELECT 'zt001 ', '02005 ' UNION ALL
SELECT 'zt002 ', '03 ' UNION ALL
SELECT 'zt003 ', '023 '
CREATE TABLE B
(S_ZT VARCHAR(10), S_ID VARCHAR(10), D_ID VARCHAR(10))
INSERT INTO B
SELECT 'zt001 ', '02001 ', '8888 ' UNION ALL
SELECT 'zt001 ', '02002 ', '9999 ' UNION ALL
SELECT 'zt003 ', '023 ', '1234 ' UNION ALL
SELECT 'zt002 ', '03 ', '3434 '
SELECT A.ZT,ID=(CASE WHEN A.ZT=B.S_ZT AND A.ID=B.S_ID THEN B.D_ID ELSE ' ' END)
FROM A JOIN B
ON A.ZT=B.S_ZT AND A.ID=B.S_ID
ZT ID
---------- ----------
zt001 8888
zt001 9999
zt002 3434
zt003 1234
（所影响的行数为 4 行）
