select a.*,标记=case when isnull(b.keyword,'')<>'' then 'Y' else 'N' end from tableA a left join tableB b on a.keyword=b.keyword
这样是不对的。会有多判。 i.e, --cross join: t1.keyword t2.keyword t1.id t2.id a a 1 1 a b 1 2 a c 1 3 b a 2 1 b b 2 2 b c 2 3 --left outer join: t1.keyword t2.keyword t1.id t2.id a a 1 1 null b 1 2 null c 1 3 null a 2 1 b b 2 2 null c 2 3[/code]不知我的分析对不?
--tblTmpCampKeyWords [code=sql]CampaignId KeyWord 13030082 长沙整形美容排名 13030094 治疗眼袋的方法 13070052 做胸部整形哪里好 13030085 乳晕再造 13030124 消除色斑 13030100 腋窝脱毛 13030103 埋线双眼皮价格 13030100 脸部脱毛 13029743 男生如何瘦小腿肌肉 13070049 乳晕整形费用--tblSearchWord Keyword CampaignId 上海哪里读英语 3547291 上海哪里读英语 3547291 出国英语培训 3547294 英语四级培训班 3547294 英语口语班 上海 3508397 天津成人学位英语 3568027 宝安 英语培训 3592383 深圳南山英语培训 3592383 深圳南山英语培训 3592383 深圳南山英语培训 3592383 [/code]下面是我的解决方法, 不知还有更好的吗?select T1.CampaignId, T1.SearchWord, case when exists( select 1 from @tblCampKeyWords as T2 where T1.SearchWord = T2.KeyWord and T1.CampaignId = T2.CampaignId ) then 'Y' else 'N' end as ExistsOrNot, OccurenceNumber from @tblSumCampSearchWords as T1 输出: CampaignId SearchWord ExistsOrNot OccurenceNumber 3547294 出国英语培训 N 12 3592383 深圳南山英语培训 N 8
select T1.CampaignId, T1.SearchWord, case when exists( select 1 from @tblCampKeyWords as T2 where T1.SearchWord = T2.KeyWord and T1.CampaignId = T2.CampaignId ) then 'Y' else 'N' end as ExistsOrNot, OccurenceNumber from @tblSumCampSearchWords as T1输出: CampaignId SearchWord ExistsOrNot OccurenceNumber 3547294 出国英语培训 N 12 3592383 深圳南山英语培训 N 8
select distinct T1.CampaignId, T1.SearchWord,
case when t2.KeyWord is not null and t2.CampaignId IS not null then 'Y' else 'N' end as ExistsOrNot,
OccurenceNumber
from @tblSumCampSearchWords as T1 left join @tblCampKeyWords as T2 on T1.SearchWord = T2.KeyWord and T1.CampaignId = T2.CampaignId
select a.*,标记=case when isnull(b.keyword,'')<>'' then 'Y' else 'N' end from tableA a left join (select distinct keyword from tableB) b on a.keyword=b.keyword
--存在与否的判断 CASE WHEN EXISTS(SELECT 1 FROM tableB B WHERE A.keyword = b.keyword) THEN 'Y' ELSE 'N' END
--存在与否的判断 CASE WHEN EXISTS(SELECT 1 FROM tableB B WHERE A.keyword = b.keyword) THEN 'Y' ELSE 'N' END 谢谢!
from tableA a
left join tableB b on a.keyword=b.keyword
--cross join:
t1.keyword t2.keyword t1.id t2.id
a a 1 1
a b 1 2
a c 1 3
b a 2 1
b b 2 2
b c 2 3
--left outer join:
t1.keyword t2.keyword t1.id t2.id
a a 1 1
null b 1 2
null c 1 3
null a 2 1
b b 2 2
null c 2 3[/code]不知我的分析对不?
[code=sql]CampaignId KeyWord
13030082 长沙整形美容排名
13030094 治疗眼袋的方法
13070052 做胸部整形哪里好
13030085 乳晕再造
13030124 消除色斑
13030100 腋窝脱毛
13030103 埋线双眼皮价格
13030100 脸部脱毛
13029743 男生如何瘦小腿肌肉
13070049 乳晕整形费用--tblSearchWord
Keyword CampaignId
上海哪里读英语 3547291
上海哪里读英语 3547291
出国英语培训 3547294
英语四级培训班 3547294
英语口语班 上海 3508397
天津成人学位英语 3568027
宝安 英语培训 3592383
深圳南山英语培训 3592383
深圳南山英语培训 3592383
深圳南山英语培训 3592383
[/code]下面是我的解决方法, 不知还有更好的吗?select T1.CampaignId, T1.SearchWord,
case when exists( select 1 from @tblCampKeyWords as T2 where T1.SearchWord = T2.KeyWord and T1.CampaignId = T2.CampaignId ) then 'Y' else 'N' end as ExistsOrNot, OccurenceNumber
from @tblSumCampSearchWords as T1
输出:
CampaignId SearchWord ExistsOrNot OccurenceNumber
3547294 出国英语培训 N 12
3592383 深圳南山英语培训 N 8
[code=sql]CampaignId KeyWord
13030082 长沙整形美容排名
13030094 治疗眼袋的方法
13070052 做胸部整形哪里好
13030085 乳晕再造
13030124 消除色斑
13030100 腋窝脱毛
13030103 埋线双眼皮价格
13030100 脸部脱毛
13029743 男生如何瘦小腿肌肉
13070049 乳晕整形费用--tblSearchWord
Keyword CampaignId
上海哪里读英语 3547291
上海哪里读英语 3547291
出国英语培训 3547294
英语四级培训班 3547294
英语口语班 上海 3508397
天津成人学位英语 3568027
宝安 英语培训 3592383
深圳南山英语培训 3592383
深圳南山英语培训 3592383
深圳南山英语培训 3592383
下面是我的解决方法, 不知还有更好的吗?
select T1.CampaignId, T1.SearchWord,
case when exists( select 1 from @tblCampKeyWords as T2 where T1.SearchWord = T2.KeyWord and T1.CampaignId = T2.CampaignId ) then 'Y' else 'N' end as ExistsOrNot, OccurenceNumber
from @tblSumCampSearchWords as T1输出:
CampaignId SearchWord ExistsOrNot OccurenceNumber
3547294 出国英语培训 N 12
3592383 深圳南山英语培训 N 8
select distinct
T1.CampaignId,
T1.SearchWord,
case when t2.KeyWord is not null
and t2.CampaignId IS not null
then 'Y'
else 'N'
end as ExistsOrNot,
OccurenceNumber
from @tblSumCampSearchWords as T1
left join @tblCampKeyWords as T2
on T1.SearchWord = T2.KeyWord
and T1.CampaignId = T2.CampaignId
from tableA a
left join (select distinct keyword from tableB) b on a.keyword=b.keyword
--存在与否的判断
CASE WHEN EXISTS(SELECT 1 FROM tableB B WHERE A.keyword = b.keyword) THEN 'Y' ELSE 'N' END
--存在与否的判断
CASE WHEN EXISTS(SELECT 1 FROM tableB B WHERE A.keyword = b.keyword) THEN 'Y' ELSE 'N' END
谢谢!