table1:
id name
1 123
2 456
3 789
4 789
table2:
id name
1 345
2 789
3 456
4 976
select table1.name,table3.age
from
(
select table1.name from table1
where table1.name in (select table2.name from table2 group by table2.name)
group by table1.name
)t1
left join table3
on t1.name= table3.name
order by t1.name desc
这样可以实现成：
result:
id name age
1 456 28
2 789 19
我现在想统计一个这个result里面name字段在table1和table2中出现的次数
我想要的结果是：
id name age times_table1 times_table2
1 456 28 1 1
2 789 19 2 1
请问怎么修改这个？谢谢了
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-03-01 11:37:35
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([id] int,[name] int)
insert [table1]
select 1,123 union all
select 2,456 union all
select 3,789 union all
select 4,789
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[name] int)
insert [table2]
select 1,345 union all
select 2,789 union all
select 3,456 union all
select 4,976
--> 测试数据：[table3]
if object_id('[table3]') is not null drop table [table3]
go
create table [table3]([id] int,[name] int,[age] int)
insert [table3]
select 1,456,28 union all
select 2,789,19 union all
select 3,867,32
--------------开始查询--------------------------
SELECT c.id,a.name,c.age,a.num AS times_table1,b.num AS times_table2
FROM
(SELECT name,COUNT(name) AS num FROM table1 GROUP BY name) AS a
INNER JOIN
(SELECT name,COUNT(name) AS num FROM table2 GROUP BY name) AS b
ON
a.name=b.name
INNER JOIN
table3 AS c
ON
a.name=c.name
----------------结果----------------------------
/* id name age times_table1 times_table2
----------- ----------- ----------- ------------ ------------
1 456 28 1 1
2 789 19 2 1
(2 行受影响)
*/
------解决方案--------------------
试试这个：
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 ,789
create 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