sql server 2005 查询语句

select count(*) from
(select distinct id from T1) T1,
(select distinct id from T2) T2,
..............
(select distinct id from T12) T12

where T1.id=T2.id and T2.id=T3.id and T3.id=T4.id and T4.id=T5.id and T5.id=T6.id and T6.id=T7.id and T7.id=T8.id and T8.id=T9.id and T9.id=T10.id and T10.id=T11.id and T11.id=T12.id

;WITH CT1 AS
(
SELECT distinct T1_id FROM T1
)
,CT2 AS
(
SELECT distinct T2_id FROM T2,CT1 WHERE T2_id=T1_id
)
,CT3 AS
(
SELECT distinct T3_id FROM T3,CT2 WHERE T3_id=T2_id
)
,CT4 AS
(
SELECT distinct T4_id FROM T4,CT3 WHERE T4_id=T3_id
)
,CT5 AS
(
SELECT distinct T5_id FROM T5,CT4 WHERE T5_id=T4_id
)
,CT6 AS
(
SELECT distinct T6_id FROM T6,CT5 WHERE T6_id=T5_id
)
,CT7 AS
(
SELECT distinct T7_id FROM T7,CT6 WHERE T7_id=T6_id
)
,CT8 AS
(
SELECT distinct T8_id FROM T8,CT7 WHERE T8_id=T7_id
)
,CT9 AS
(
SELECT distinct T9_id FROM T9,CT8 WHERE T9_id=T8_id
)
,CT10 AS
(
SELECT distinct T10_id FROM T10,CT9 WHERE T10_id=T9_id
)
,CT11 AS
(
SELECT distinct T11_id FROM T11,CT10 WHERE T11_id=T10_id
)
,CT12 AS
(
SELECT distinct T12_id FROM T12,CT11 WHERE T12_id=T11_id
)
SELECT count(*) FROM CT12

sql2005 cte表达式 希望对你有帮助

加distinct就行了

select count(distinct t1.id) from T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12
where T1.id=T2.id and T2.id=T3.id and T3.id=T4.id and T4.id=T5.id and T5.id=T6.id and T6.id=T7.id and T7.id=T8.id and T8.id=T9.id and T9.id=T10.id and T10.id=T11.id and T11.id=T12.id

这样的数据库结构不知道是谁想出来的。简简单单的一个表就可以搞定的东西非要整成12表！
12个表做笛卡尔积，如果数据量大，都不晓得要等多久才能出来结果！！！

count(*) 改成 count(distinct T1.id)

哎，越搞越复杂……