---------------------------------------------------------------- -- 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 ,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 */
table3:
id name age
1 456 28
2 789 19
3 867 32
-- 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 ,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
*/