难题:如何比较2个表里面某一列的差异?并记录id!
T1:
id, info
T2:
id, info
我想比较T1和T2 中info列的不同(T1.id = T2.id),并把结果记录在T3里(id, T1.info)
也就是说以T1为基准,如果T2.info不同于T1.info,那么就把id和T2.info写在T3表里
T1.info格式是varchar,T2.info格式是ntext
这样的语句可以写出来么?
[解决办法]
select T1.id,T1.info from T1, T2
where T1.id=T2.id and T1.info=T2.info
union all
select T2.id,T2.info from T1,T2
where T1.id=T2.id and T1.info!=T2.info
[解决办法]
insert into T3(id,info)
select T1.id,T1.info from T1, T2
where T1.id=T2.id and T1.info=T2.info
union all
select T2.id,T2.info from T1,T2
where T1.id=T2.id and T1.info!=T2.info
[解决办法]
INSERT INTO t3
(id, info)
SELECT t2.id, t2.info
FROM t1 INNER JOIN
t2 ON t1.id = t2.id
WHERE (PATINDEX(t1.info, t2.info) = 0)
[解决办法]
INSERT INTO t3
(id, info)
SELECT t2.id, t2.info
FROM t1 INNER JOIN
t2 ON t1.id = t2.id
WHERE (PATINDEX(t1.info, t2.info) = 0) AND (DATALENGTH(t1.info) <> 0) AND
(DATALENGTH(t2.info) <> 0)