34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE filter_mobile(
filtertype int DEFAULT 0,
mobile nvarchar(20)
)
CREATE TABLE task(
taskid int DEFAULT 0,
filtertype int DEFAULT 0
)
CREATE TABLE task_mobile(
taskid int DEFAULT 0,
mobile nvarchar(20)
)
CREATE TABLE task_mobile_content(
taskid int DEFAULT 0,
mobile nvarchar(20),
txt nvarchar(50)
)
insert filter_mobile(filtertype,mobile) values(-1,'13800138000')
insert filter_mobile(filtertype,mobile) values(-1,'13800138001')
insert filter_mobile(filtertype,mobile) values(-1,'13800138002')
insert filter_mobile(filtertype,mobile) values(-1,'13800138003')
insert filter_mobile(filtertype,mobile) values(-1,13800138004)
insert filter_mobile(filtertype,mobile) values(0,13800138005)
insert filter_mobile(filtertype,mobile) values(0,13800138006)
insert filter_mobile(filtertype,mobile) values(0,13800138008)
insert filter_mobile(filtertype,mobile) values(0,13800138009)
insert filter_mobile(filtertype,mobile) values(-1,13800138010)
insert filter_mobile(filtertype,mobile) values(-1,13800138011)
insert filter_mobile(filtertype,mobile) values(-1,13800138012)
insert filter_mobile(filtertype,mobile) values(0,13800138014)
insert filter_mobile(filtertype,mobile) values(0,13800138015)
insert filter_mobile(filtertype,mobile) values(0,13800138007)
insert filter_mobile(filtertype,mobile) values(-1,13800138013)
insert task_mobile(taskid,mobile) values(1,13800138020)
insert task_mobile(taskid,mobile) values(1,13800138022)
insert task_mobile(taskid,mobile) values(1,13800138015)
insert task_mobile(taskid,mobile) values(1,13800138018)
insert task_mobile(taskid,mobile) values(1,13800138045)
insert task_mobile(taskid,mobile) values(1,13800138098)
insert task_mobile(taskid,mobile) values(1,13800138000)
insert task_mobile(taskid,mobile) values(1,13800138001)
insert task_mobile(taskid,mobile) values(1,13800138005)
insert task_mobile(taskid,mobile) values(1,13800138010)
insert task_mobile(taskid,mobile) values(1,13800138011)
insert task_mobile(taskid,mobile) values(1,13800138015)
insert task_mobile(taskid,mobile) values(2,13800138005)
insert task_mobile(taskid,mobile) values(1,13800138005)
insert task_mobile(taskid,mobile) values(1,13800138000)
insert task_mobile(taskid,mobile) values(2,13800138003)
insert task_mobile(taskid,mobile) values(2,13800138007)
insert task_mobile(taskid,mobile) values(2,13800138023)
insert task_mobile(taskid,mobile) values(2,13800138009)
insert task_mobile_content(taskid,mobile,txt) values(1,null,'测试')
insert task_mobile_content(taskid,mobile,txt) values(2,null,'4444')
insert task_mobile_content(taskid,mobile,txt) values(3,'13800138100','测试EXCEL')
insert task_mobile_content(taskid,mobile,txt) values(3,'13800138099','测试EXCEL')
insert task_mobile_content(taskid,mobile,txt) values(3,'13800138000','测试EXCEL')
insert task_mobile_content(taskid,mobile,txt) values(3,'13800138006','测试EXCEL')
insert task(taskid, filtertype) values(1,1)
insert task(taskid, filtertype) values(2,1)
insert task(taskid, filtertype) values(3,1)
-- 要求得到如下结果
/*
1,13800138020,测试
1,13800138022,测试
0,13800138015,测试
1,13800138018,测试
1,13800138045,测试
1,13800138098,测试
-1,13800138000,测试
-1,13800138001,测试
0,13800138005,测试
-1,13800138010,测试
-1,13800138011,测试
0,13800138015,测试
0,13800138005,4444
0,13800138005,测试
-1,13800138000,测试
-1,13800138003,4444
1,13800138100,测试EXCEL
1,13800138099,测试EXCEL
-1,13800138000,测试EXCEL
0,13800138006,测试EXCEL
0,13800138007,4444
1,13800138023,4444
0,13800138009,4444
*/
--测试数据我就不贴了
--建议楼主以后发帖说下规则,我们好理解点
select isnull(f.filtertype,h.filtertype) as filtertype,
isnull(r.mobile,t.mobile) as mobile,
r.txt
from task_mobile_content r left join task_mobile t
on r.taskid=t.taskid join task h
on r.taskid=h.taskid left join filter_mobile f
on isnull(r.mobile,t.mobile)=f.mobile
------------------------------------------------
1 13800138020 测试
1 13800138022 测试
0 13800138015 测试
1 13800138018 测试
1 13800138045 测试
1 13800138098 测试
-1 13800138000 测试
-1 13800138001 测试
0 13800138005 测试
-1 13800138010 测试
-1 13800138011 测试
0 13800138015 测试
0 13800138005 测试
-1 13800138000 测试
0 13800138005 4444
-1 13800138003 4444
0 13800138007 4444
1 13800138023 4444
0 13800138009 4444
1 13800138100 测试EXCEL
1 13800138099 测试EXCEL
-1 13800138000 测试EXCEL
0 13800138006 测试EXCEL
select isnull(b.filtertype,c.filtertype),a.mobile,d.txt
from task_mobile a left join filter_mobile b on a.mobile=b.mobile
inner join task c on a.taskid=c.taskid
inner join task_mobile_content d on a. taskid=d.taskid
union all
select isnull(b.filtertype,c.filtertype),a.mobile,a.txt
from task_mobile_content a left join filter_mobile b on a.mobile=b.mobile
inner join task c on a.taskid=c.taskid
where a.mobile is not null
1> select isnull(b.filtertype,c.filtertype),a.mobile,d.txt
2> from task_mobile a left join filter_mobile b on a.mobile=b.mobile
3> inner join task c on a.taskid=c.taskid
4> inner join task_mobile_content d on a. taskid=d.taskid
5> union all
6> select isnull(b.filtertype,c.filtertype),a.mobile,a.txt
7> from task_mobile_content a left join filter_mobile b on a.mobile=b.mobil
8> inner join task c on a.taskid=c.taskid
9> where a.mobile is not null
10> go
|mobile |txt
-----------|--------------------|----------------------------------------
1|13800138020 |测试
1|13800138022 |测试
0|13800138015 |测试
1|13800138018 |测试
1|13800138045 |测试
1|13800138098 |测试
-1|13800138000 |测试
-1|13800138001 |测试
0|13800138005 |测试
-1|13800138010 |测试
-1|13800138011 |测试
0|13800138015 |测试
0|13800138005 |4444
0|13800138005 |测试
-1|13800138000 |测试
-1|13800138003 |4444
0|13800138007 |4444
1|13800138023 |4444
0|13800138009 |4444
1|13800138100 |测试EXCEL
1|13800138099 |测试EXCEL
-1|13800138000 |测试EXCEL
0|13800138006 |测试EXCEL
(23 rows affected)
1>