有一个表,其中两个字段为a,b,c
数据如下:
a b c
1 0 1
1 1 1
1 2 1
2 0 1
2 1 1
2 2 1
...
条件:当a值固定的时候,b值不重复
求一个sql,
1.查询结果中包括a,b,c
2.a值不重复,b,c值做不限制返回结果类似:
a b c
1 0 1
2 1 1
数据如下:
a b c
1 0 1
1 1 1
1 2 1
2 0 1
2 1 1
2 2 1
...
条件:当a值固定的时候,b值不重复
求一个sql,
1.查询结果中包括a,b,c
2.a值不重复,b,c值做不限制返回结果类似:
a b c
1 0 1
2 1 1
(
select distinct a,min(b) as b from table group by a
) t2
where
t1.a = t2.a and t1.b = t2.b
order by t1.a
这样得到的结果就是:
a b c
1 0 1
2 1 1
(
select distinct a,min(b) as b from table group by a
) t2
where
t1.a = t2.a and t1.b = t2.b
order by t1.a
这样得到的结果就是:
a b c
1 0 1
2 1 1
不好意思,上面少打了几个字符
以下应该就可以了!
select a,b,c from table t1,
(
select distinct a,min(b) as b from table group by a
) t2
where
t1.a = t2.a and t1.b = t2.b
SELECT A,B,C FROM
(SELECT ROW_NUMBER() OVER(PATTITION BY A ORDER BY B) RN ,A,B,C FROM YOURTABLE)
WHERE RN=1
group by a
a b c
1 2 1
2 2 1