急求一复杂语句

[500公里之外]

表a
ID Time
1 2006.1.1
1 2006.2.1
2 2006.1.1

计算出每个月每一天的不同ID记录总数。

[paoluo]

要得到的結果是怎樣的？

[500公里之外]

得到的结果就是
总数 时间（哪一天）

[zsforever]

select convert(varchar(10),Time,120),count(ID)
from (select distinct ID,Time from 表a) a
group by convert(varchar(10),Time,120)

[paoluo]

Select
Count(Distinct ID) As 总数,
[Time]
From
a
Group By
[Time]

[gahade]

--试试

select distinct time,(select count(*) from (select distinct id from a aa where aa.time =a.time)t)
from a

[rookie_one]

select count(distinct id) 总数,Time 时间
from a
group by Time

[paoluo]

Create table a
(ID Int,
[Time] Varchar(10))
Insert a Select 1, '2006.1.1'
Union All Select 1, '2006.1.1'
Union All Select 1, '2006.2.1'
Union All Select 2, '2006.1.1'
Union All Select 2, '2006.1.1'
Union All Select 2, '2006.1.2'
Union All Select 3, '2006.1.2'
GO
Select
Count(Distinct ID) As 总数,
[Time]
From
a
Group By
[Time]
GO
Drop Table a
--Result
/*
总数 Time
2 2006.1.1
2 2006.1.2
1 2006.2.1
*/

[rookie_one]

select count(distinct id) 总数,Time 时间
from a
group by Time
order by Time

[paoluo]

gahade(与君共勉), 你是不是想複雜了？

直接一個group by就可以，怎麼還用子查詢.

[gahade]

好像是的(想複雜了)

[asdfsadfasdfafa]

SELECT COUNT(id) AS Expr1, dt, id
FROM td
GROUP BY dt, id