表book结构如下:
ID A B TIME C_ID
1 a 11 2012/1/1 06:00:36 2
2 a 11 2012/1/1 06:00:36 1
3 cc 16 2012/1/1 22:17:36 2
4 cc 16 2012/1/1 22:17:36 4
5 22 15 2012/1/1 08:10:40 10
如果我想得到如下的查询结果,即去除TIME字段(DATE类型)重复的项:ID A B TIME C_ID
1 a 11 2012/1/1 06:00:36 2
3 cc 16 2012/1/1 22:17:36 2
5 22 15 2012/1/1 23:17:36 10
我现在写的报错的SQL语句是:
select b.ID, b.A, b.B, b.C_ID, count(distinct b.TIME)
from book b
group by count(distinct b.TIME)
ID A B TIME C_ID
1 a 11 2012/1/1 06:00:36 2
2 a 11 2012/1/1 06:00:36 1
3 cc 16 2012/1/1 22:17:36 2
4 cc 16 2012/1/1 22:17:36 4
5 22 15 2012/1/1 08:10:40 10
如果我想得到如下的查询结果,即去除TIME字段(DATE类型)重复的项:ID A B TIME C_ID
1 a 11 2012/1/1 06:00:36 2
3 cc 16 2012/1/1 22:17:36 2
5 22 15 2012/1/1 23:17:36 10
我现在写的报错的SQL语句是:
select b.ID, b.A, b.B, b.C_ID, count(distinct b.TIME)
from book b
group by count(distinct b.TIME)
select b.ID, b.A, b.B,b.TIME, b.C_ID, count(distinct b.TIME)
from book b
group by b.TIME
自己写了一个
select ID,A,B,TIME,C_ID from(
select ID,A,B,TIME,C_ID,row_number()over(partition by TIME order by C_ID asc)rn
from book)
where rn=1