select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)这样是不是应该也可以?哪个速度和性能快?如果这个可以的话,我想把返回的结果再left join一下:select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name) left join t3 on left(table1.name,7)=t3.name 但是现在报错,第一个left附近有语法错误,怎么改啊?谢谢
select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)这样是不是应该也可以?哪个速度和性能快?如果这个可以的话,我想把返回的结果再left join一下:select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name) left join t3 on left(table1.name,7)=t3.name 但是现在报错,第一个left附近有语法错误,怎么改啊?谢谢用exists应该会更快一点: select table1.name from table1 where exists (select table2.name from table2 where table1.name = table2.name)
如果还要关联,可以这样: select * from ( select table1.name from table1 where exists (select table2.name from table2 where table1.name = table2.name) )table1 left join t3 on left(table1.name,7)=t3.name
SELECT DISTINCT a.* FROM table1 a JOIN table2 b ON a.NAME=b.name
问下t3是那个表 为什么on后不见table1 用left函数的作用是什么捏 ?
这样选出来两个表共同的值,但是我还想在这个新生成的table1表中加入两列,就是这些同样的值在两个表中出现的次数id name times_t1 times_t2请问该怎么办?谢谢
这样选出来两个表共同的值,但是我还想在这个新生成的table1表中加入两列,就是这些同样的值在两个表中出现的次数id name times_t1 times_t2请问该怎么办?谢谢 table1: id name 1 123 2 456 3 789 4 789table2: id name 1 345 2 789 3 456 4 976result: id name times_t1 times_t2 1 456 1 1 2 789 2 1
这样选出来两个表共同的值,但是我还想在这个新生成的table1表中加入两列,就是这些同样的值在两个表中出现的次数id name times_t1 times_t2请问该怎么办?谢谢 table1: id name 1 123 2 456 3 789 4 789table2: id name 1 345 2 789 3 456 4 976result: id name times_t1 times_t2 1 456 1 1 2 789 2 1试试这个: create table table1(id int, name int)insert into table1 select 1 ,123 union all select 2 ,456 union all select 3 ,789 union all select 4 ,789create table table2(id int, name int) insert into table2 select 1 ,345 union all select 2 ,789 union all select 3 ,456 union all select 4 ,976 create table table3(id int, name int,age int) insert into table3 select 1 ,456 ,28 union all select 2 ,789 ,19 union all select 3 ,867 ,32 go select t3.id, t3.name, t3.age, count(distinct t1.id) as times_table1, COUNT(distinct t2.id) as times_table2
from table2 t2 inner join table1 t1 on t2.name = t1.name inner join table3 t3 on t2.name = t3.name group by t3.id,t3.name,t3.age /* id name age times_table1 times_table2 1 456 28 1 1 2 789 19 2 1 */
select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)这样是不是应该也可以?哪个速度和性能快?如果这个可以的话,我想把返回的结果再left join一下:select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)
left join t3 on left(table1.name,7)=t3.name 但是现在报错,第一个left附近有语法错误,怎么改啊?谢谢
select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)这样是不是应该也可以?哪个速度和性能快?如果这个可以的话,我想把返回的结果再left join一下:select table1.name from table1 where table1.name in (select table2.name from table2 group by table2.name)
left join t3 on left(table1.name,7)=t3.name 但是现在报错,第一个left附近有语法错误,怎么改啊?谢谢用exists应该会更快一点:
select table1.name from table1
where exists (select table2.name from table2 where table1.name = table2.name)
select *
from
(
select table1.name from table1
where exists (select table2.name from table2 where table1.name = table2.name)
)table1
left join t3 on left(table1.name,7)=t3.name
SELECT DISTINCT a.*
FROM table1 a
JOIN table2 b ON a.NAME=b.name
问下t3是那个表 为什么on后不见table1 用left函数的作用是什么捏 ?
table1:
id name
1 123
2 456
3 789
4 789table2:
id name
1 345
2 789
3 456
4 976result:
id name times_t1 times_t2
1 456 1 1
2 789 2 1
问下t3是那个表 为什么on后不见table1 用left函数的作用是什么捏 ?呵呵,这个我也不清楚,只是按照楼主的代码,修改了一下
table1:
id name
1 123
2 456
3 789
4 789table2:
id name
1 345
2 789
3 456
4 976result:
id name times_t1 times_t2
1 456 1 1
2 789 2 1试试这个:
create table table1(id int, name int)insert into table1
select 1 ,123 union all
select 2 ,456 union all
select 3 ,789 union all
select 4 ,789create table table2(id int, name int)
insert into table2
select 1 ,345 union all
select 2 ,789 union all
select 3 ,456 union all
select 4 ,976
create table table3(id int, name int,age int)
insert into table3
select 1 ,456 ,28 union all
select 2 ,789 ,19 union all
select 3 ,867 ,32
go
select t3.id,
t3.name,
t3.age,
count(distinct t1.id) as times_table1,
COUNT(distinct t2.id) as times_table2
from table2 t2
inner join table1 t1
on t2.name = t1.name
inner join table3 t3
on t2.name = t3.name
group by t3.id,t3.name,t3.age
/*
id name age times_table1 times_table2
1 456 28 1 1
2 789 19 2 1
*/