22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT
*
FROM
[中心医院疑点库2016-2017].[dbo].[一次性耗材2016-2017]
WHERE
NOT EXISTS
(
SELECT
*
FROM
[物价数据].[dbo].[一次性耗材目录]
WHERE
项目名称 LIKE '%' + 耗材名称 + '%'
OR 耗材名称 LIKE '%' + 项目名称 + '%'
);
SELECT
CASE
WHEN ta.col1 IS NULL THEN 'tb' ELSE 'ta'
END table_name,
ISNULL( dbo.ta.col1, dbo.tb.col2 ) col
FROM
dbo.ta
FULL OUTER JOIN dbo.tb ON '%' + dbo.tb.col2 + '%' LIKE '%' + dbo.ta.col1 + '%'
WHERE
dbo.ta.col1 + dbo.tb.col2 IS NULL
--测试数据
if not object_id(N'A') is null
drop table A
Go
Create table A([col1] nvarchar(27))
Insert A
select N'一次性针筒' union all
select N'一次性的管' union all
select N'一次性的输液瓶'
GO
if not object_id(N'B') is null
drop table B
Go
Create table B([col2] nvarchar(29))
Insert B
select N'(jk)一次性针筒' union all
select N'一次性管子' union all
select N'一次性输液用的瓶子'
Go
--测试数据结束
Select * from A WHERE NOT EXISTS(SELECT * FROM B WHERE col1 LIKE '%'+col2+'%' OR col2 LIKE '%'+col1+'%')
UNION ALL
Select * from B WHERE NOT EXISTS(SELECT * FROM A WHERE col1 LIKE '%'+col2+'%' OR col2 LIKE '%'+col1+'%')
USE tempdb
GO
IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE ta(
col1 NVARCHAR(500)
)
CREATE TABLE tb(
col2 NVARCHAR(500)
)
GO
INSERT INTO ta VALUES (N'一次性针筒')
INSERT INTO ta VALUES (N'甲肝疫苗')
--
INSERT INTO tb VALUES (N'(jk)一次性针筒')
INSERT INTO tb VALUES (N'丙肝疫苗')
GO
-------- 以上为测试数据 ------------
SELECT 'ta' AS tableName,col1 AS diff FROM ta WHERE NOT EXISTS(
SELECT * FROM tb WHERE ta.col1 LIKE '%'+tb.col2+'%'
)
AND NOT EXISTS (
SELECT * FROM tb WHERE tb.col2 LIKE '%'+ta.col1+'%'
)
UNION ALL
SELECT 'tb' AS tableName,col2 AS diff FROM tb WHERE NOT EXISTS(
SELECT * FROM ta WHERE ta.col1 LIKE '%'+tb.col2+'%'
)
AND NOT EXISTS (
SELECT * FROM ta WHERE tb.col2 LIKE '%'+ta.col1+'%'
)
/*
tableName diff
--------- ----------
ta 甲肝疫苗
tb 丙肝疫苗
*/