表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的要求返回
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的要求返回
when ZT='zt001' and ID='02002' then '9999'
when ZT='zt001' and ID='03' then '3434'
else '1234' end as D_ID
from 表A
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
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 A.ZT,isnull(B.D_ID,A.ID) as ID,A.Name
from A left join B on A.zt=B.S_zt and A.ID=B.S_ID
但是如何才能让B表里的
zt001 all 1234
表示余下的ID全部转为1234,这个要求用上面的语句实现不了
寻高手解决.
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
而且B表的数据,还会一随业务变化,还要变更,总不能老是改SQL语句吧
这个问题好象不好解决
针对A表的ID(编号) 对B表写个ID检查函数,如果B表有变动的就自动更新A表对应编号完毕拉!很简单的拉
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_IDZT ID
---------- ----------
zt001 8888
zt001 9999
zt002 3434
zt003 1234(所影响的行数为 4 行)