高分 求一条SQL语句

[nnnsu]

如表A有如下数据：
星期一 1
星期一 4
星期二 3
星期二 5
星期三 3
星期三 2
星期三 4

求一条语句，可以得到以下结果：
星期一 1
星期二 3
星期三 2
（即取出每天最小的那个数）

[304的的哥]

with t as(
     select '星期一' week,1 num from dual union all
     select '星期一',4 from dual union all
     select '星期二',3 from dual union all
     select '星期二',5 from dual union all
     select '星期三',3 from dual union all
     select '星期三',2 from dual union all
     select '星期三',4 from dual)
select week,min(num)
from t
group by week;

WEEK     MIN(NUM)
------ ----------
星期二          3
星期三          2
星期一          1

[304的的哥]

with t as(
     select 3 a,1900001212 b,1900001209 c,'新街变' d,'001' e,to_date('2010-11-29 11:34:00','yyyy-mm-dd hh24:mi:ss') f from dual union all
     select 13,1900001222,1900001209,'新街变','001',to_date('2010-11-29 21:32:00','yyyy-mm-dd hh24:mi:ss') from dual union all
     select 7,1900001222,1900001220,'新街变','001',to_date('2010-11-29 22:39:00','yyyy-mm-dd hh24:mi:ss') from dual union all
     select 27,1900001231,1900001209,'新街变','001',to_date('2010-11-30 14:23:00','yyyy-mm-dd hh24:mi:ss') from dual union all
     select 21,1900001231,1900001220,'新街变','001',to_date('2010-11-30 11:41:00','yyyy-mm-dd hh24:mi:ss') from dual union all
     select 4,1900001231,1900001229,'新街变','001',to_date('2010-11-30 11:36:00','yyyy-mm-dd hh24:mi:ss') from dual)
select *
from t
where a in(select min(a) from t group by b)
--
         A          B          C D      E   F
---------- ---------- ---------- ------ --- -----------
         3 1900001212 1900001209 新街变 001 2010-11-29
         7 1900001222 1900001220 新街变 001 2010-11-29
         4 1900001231 1900001229 新街变 001 2010-11-30

[304的的哥]

SQL> with t as(
  2       select 3 a,'1900001212' b,'1900001209' c,'新街变' d,'001' e,to_date('2010-11-29 11:34:00','yyyy-mm-dd hh24:mi:ss') f from dual union all
  3       select 13,'1900001222','1900001209','新街变','001',to_date('2010-11-29 21:32:00','yyyy-mm-dd hh24:mi:ss') from dual union all
  4       select 7,'1900001222','1900001220','新街变','001',to_date('2010-11-29 22:39:00','yyyy-mm-dd hh24:mi:ss') from dual union all
  5       select 27,'1900001231','1900001209','新街变','001',to_date('2010-11-30 14:23:00','yyyy-mm-dd hh24:mi:ss') from dual union all
  6       select 21,'1900001231','1900001220','新街变','001',to_date('2010-11-30 11:41:00','yyyy-mm-dd hh24:mi:ss') from dual union all
  7       select 4,'1900001231','1900001229','新街变','001',to_date('2010-11-30 11:36:00','yyyy-mm-dd hh24:mi:ss') from dual)
  8  select *
  9  from t
 10  where a in(select min(a) from t group by b)
 11  /

         A B          C          D      E   F
---------- ---------- ---------- ------ --- -----------
         3 1900001212 1900001209 新街变 001 2010-11-29
         7 1900001222 1900001220 新街变 001 2010-11-29
         4 1900001231 1900001229 新街变 001 2010-11-30

[liuhuabai100]

SELECT week,min(num) FROM t GROUP BY week

[vivai2010]

select week,min(num)
from t
group by week;

[真的不知道取什么名]

[Quote=引用 1 楼 bobo12082119 的回复:]
SQL code

with t as(
select '星期一' week,1 num from dual union all
select '星期一',4 from dual union all
select '星期二',3 from dual union all
select '星期二',5 from dual union all
……
[/Quote]
正解

楼主没有用心想吧...

[迷失的空间]

SELECT week,min(num) FROM t GROUP BY week

[lianganglin]

楼上的几位的答案都是正确的。。group by 是基础用法啊。。楼主好好复习下啊！

[taikongxinke]

SELECT week,min(num) FROM t GROUP BY week

[ssqtjffcu]

with t as(
     select '星期一' week,1 num from dual union all
     select '星期一',4 from dual union all
     select '星期二',3 from dual union all
     select '星期二',5 from dual union all
     select '星期三',3 from dual union all
     select '星期三',2 from dual union all
     select '星期三',4 from dual)
--对week字段分组，用min函数求的分组中的最小值
select week,min(num)
from t group by week

[ssqtjffcu]

week    min(num)
星期二	3
星期三	2
星期一	1

[jielovehuan]

SELECT week,min(num) FROM t GROUP BY week