34,587
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#TB1') is null
drop table #TB1
Go
Create table #TB1([ID] int,[Name] nvarchar(22),[Com] nvarchar(23),[Dep] nvarchar(23),[RID] int)
Insert #TB1
select 1,N'张三',N'101',N'001',10 union all
select 2,N'李四',N'101',N'%',11 union all
select 3,N'王二',N'%',N'%',11 union all
select 4,N'木头',N'102',N'001',10 union all
select 5,N'木头',N'101',N'001',10
Go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#TB2') is null
drop table #TB2
Go
Create table #TB2([Com] NVARCHAR(10),[Dep] nvarchar(23),[name] nvarchar(24))
Insert #TB2
select 101,N'001',N'连锁1店' union all
select 101,N'002',N'连锁2店' union all
select 102,N'001',N'自营1店' union all
select 102,N'002',N'自营2店'
Go
SELECT *
FROM #TB1 AS a
INNER JOIN #TB2 AS b ON b.Com LIKE a.Com
AND b.[Dep] LIKE a.[Dep]
WHERE a.name='李四' ;
/*
ID Name Com Dep RID Com Dep name
2 李四 101 % 11 101 001 连锁1店
2 李四 101 % 11 101 002 连锁2店
*/
with t1 as
(
select '1' id, 'a' xh union all
select '2' id, 'b' xh union all
select '3' id, 'c' xh union all
select '%' id, 'd' xh
)
, t2 as
(
select '1' id, 'aa' ms union all
select '2' id, 'bb' ms union all
select '3' id, 'cc' ms union all
select '4' id, 'dd' ms
)
select * from t1 inner join t2 on t2.id like t1.id
--借水果版主数据
create table a(aid int, aname varchar(10))
create table b(bid int, bname varchar(10))
go
insert into a values(1,'xx'),(2,'yy'),(3,'zz')
insert into b values(111,'AAA'),(222,'BBB'),(333,'CCC')
go
--SELECT * from a inner join b on a.aid = a.aid
SELECT * from a ,b
drop table a,b
go
create table a(aid int, aname varchar(10))
create table b(bid int, bname varchar(10))
go
insert into a values(1,'xx'),(2,'yy'),(3,'zz')
insert into b values(111,'AAA'),(222,'BBB'),(333,'CCC')
go
select * from a inner join b on a.aid = a.aid
go
drop table a,b
go
aid aname bid bname
----------- ---------- ----------- ----------
1 xx 111 AAA
1 xx 222 BBB
1 xx 333 CCC
2 yy 111 AAA
2 yy 222 BBB
2 yy 333 CCC
3 zz 111 AAA
3 zz 222 BBB
3 zz 333 CCC
(9 行受影响)
select * T1 A inner join T2 B ON A.Com LIKE B.Com AND A.Dept LIKE B.Dept where A.name='XXXXXX'
我不知道你业务数据说明样,不过最好用charindex