select distinct a.id,a.name from tablea a join tableb b on a.id=b.id
where (brief like '%friday%' or brief like '%9%') and state= 'U' and status= 'UN'
where (brief like '%friday%' or brief like '%9%') and state= 'U' and status= 'UN'
select id,name from tablea where (brief like '%friday%' or brief like '%9%') and (state='U' or state='UN')
union
select id,name from tableb where status='U' or status='UN'
create table #tablea (id int,name varchar(10),brief varchar(10),state varchar(10))
insert into #tablea
select 1,'adf','friday','U'
union all
select 2,'jkl','9,16','U'
union all
select 3,'gde','9,22','N'create table #tableb(id int,name varchar(10),status varchar(10))
insert into #tableb
select 1,'adf','U'
union all
select 2,'jkl','UN'
select a.id,a.name from #tablea a left join #tableb b on a.id=b.id
where (brief like '%friday%' or brief like '%9%') and state='U'and status='U'--结果
id name
----------- ----------
1 adf(所影响的行数为 1 行)
where (a.brief like '%friday%' or a.brief like '%9%') and a.state= 'U' and b.status= 'U '
谢谢你们
有两张表:tablea,tableb 其中tablea 表中有字段
id name brief state ;
1 adf thursday U
2 jkl 16 U
3 gde 22 N
4 hhh friday U
tableb表中有字段
id name status
1 adf U
2 jkl UN
3 gde UN
现在要查询两张表中brief like 'friday或9 ' 且 state= 'U ' 或者status= 'UN '的id和name;
希望得到的结果是
4 hhh (属于a表)
2 jkl (b表)
3 gde (b表)
這樣看來,我在2樓的回復是正確的
--------------------------------
不用union,未必正確,樓主你試下,如果數據是下面這樣
id name brief state ;
1 adf friday U
2 jkl 9,16 U
tableb表中有字段
id name status
1 adf U
2 jkl UN
3 gde UN
期待高人入內
where brief like '%friday%'or brief like '%9%' and state='u'or state='vu' and savea.id=savea1.id