--你的f2字段为text,因此得用下面的方法:select f1,min(cast(f2 as varchar(8000))) from table1 group by f1
--下面是数据测试--测试数据 declare @table1 table(f1 datetime,f2 text) insert into @table1 select '2003-1-1','testasadf' union all select '2003-1-1','testadacgsa' union all select '2003-2-1','aaaaada' union all select '2003-2-1','fffff'--查询 select f1,f2=min(cast(f2 as varchar(8000))) from @table1 group by f1/*--测试结果f1 f2 ------------------------- -------------- 2003-01-01 00:00:00.000 testadacgsa 2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行) --*/
select Identity(int,1,1) as xh,* into #temp from table1 select * from #temp as aa where not exists( select 1 from #temp as bb where aa.f1=bb.f1 and aa.xh>bb.xh)
--最有效的办法是,楼主的表中增加一个标识列,然后就可以用下面的方法查询:select f1,f2 from table1 a where id=(select min(id) from table1 where f1=a.f1)
--下面是数据测试--测试数据 declare @table1 table(id int identity(1,1),f1 datetime,f2 text) insert into @table1(f1,f2) select '2003-1-1','testasadf' union all select '2003-1-1','testadacgsa' union all select '2003-2-1','aaaaada' union all select '2003-2-1','fffff'--查询 select f1,f2 from @table1 a where id=(select min(id) from @table1 where f1=a.f1)/*--测试结果f1 f2 ------------------------- -------------- 2003-01-01 00:00:00.000 testadacgsa 2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行) --*/
用min函数肯定是有问题的,不管你的MIN是怎么写的,他要是位置第一而不是最小,
select identity(int,1,1) as id,f1,f2 into #tmp from table1select f1,f2 from #tmp a where id=(select min(id) from #tmp where f1=a.f1)
select identity(int,1,1) as i_id ,t_1.* into #ttt select * from #ttt where i_id in (select max(i_id) from t_1 group by f1)
zjcxc(邹建) ,字符型也可以用min()的。
select identity(int,1,1) as i_id ,t_1.* into #ttt from t_1 select * from #ttt where i_id in (select max(i_id) from t_1 group by f1)
没注意到TEXT字段, 同意楼上create table t1(f1 datetime,f2 text) insert t1 select '2003-1-1','testasadf' union all select '2003-1-1','testadacgsa' union all select '2003-2-1','aaaaada' union all select '2003-2-1','fffff'alter table t1 add id int identity(1,1) go select f1,f2 from t1 a where id = (select min(id) from t1 where f1 = a.f1) alter table t1 drop column id2003-01-01 00:00:00.000 testasadf 2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行)
select identity(int,1,1) as id,f1,f2 into #tmp from table1select a.f1,a.f2 from #tmp a where a.id=(select min(id) from #tmp where f1=a.f1) order by a.f1
不好意思,再来一次select identity(int,1,1) as i_id ,t_1.* into #ttt from t_1 select * from #ttt where i_id in (select min(i_id) from t_1 group by f1)
to: victorycyz(中海,干活去,别在CSDN玩耍!) 没错,但text类型不可以用,你有没有看楼主的表结构?
declare @table1 table(f1 datetime,f2 text)
insert into @table1
select '2003-1-1','testasadf'
union all select '2003-1-1','testadacgsa'
union all select '2003-2-1','aaaaada'
union all select '2003-2-1','fffff'--查询
select f1,f2=min(cast(f2 as varchar(8000))) from @table1 group by f1/*--测试结果f1 f2
------------------------- --------------
2003-01-01 00:00:00.000 testadacgsa
2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行)
--*/
where id=(select min(id) from table1 where f1=a.f1)
declare @table1 table(id int identity(1,1),f1 datetime,f2 text)
insert into @table1(f1,f2)
select '2003-1-1','testasadf'
union all select '2003-1-1','testadacgsa'
union all select '2003-2-1','aaaaada'
union all select '2003-2-1','fffff'--查询
select f1,f2 from @table1 a
where id=(select min(id) from @table1 where f1=a.f1)/*--测试结果f1 f2
------------------------- --------------
2003-01-01 00:00:00.000 testadacgsa
2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行)
--*/
where id=(select min(id) from #tmp where f1=a.f1)
select * from #ttt where i_id in (select max(i_id) from t_1 group by f1)
select * from #ttt where i_id in (select max(i_id) from t_1 group by f1)
insert t1 select '2003-1-1','testasadf'
union all select '2003-1-1','testadacgsa'
union all select '2003-2-1','aaaaada'
union all select '2003-2-1','fffff'alter table t1 add id int identity(1,1)
go
select f1,f2 from t1 a where id = (select min(id) from t1 where f1 = a.f1)
alter table t1 drop column id2003-01-01 00:00:00.000 testasadf
2003-02-01 00:00:00.000 aaaaada(所影响的行数为 2 行)
where a.id=(select min(id) from #tmp where f1=a.f1)
order by a.f1
select * from #ttt where i_id in (select min(i_id) from t_1 group by f1)
text类型确实不能用聚合函数,所以我才想不出来(忘了类型转换了)。
我要的结果肯定要用到min,因为里面还有别的字段,要计算总数。
一个后来增加的报表,当初不给我,到了最后要完工了,才想到给我,弄得巨难做,郁闷。
谢谢大家的帮助:)