sql取最小值的问题
regionid regionname day_code_date starttime g0402
----------- ------------- ---------------- ------------------- ---------------------------------
10 海口地区 2008-6-17 2008-6-17 下午9:00:00 95.144140462660826017664790120439
11 海甸地区 2008-6-17 2008-6-17 下午9:00:00 95.41592657700821675497077559356
create table #test(
regionid int primary key,
regionname varchar(20),
day_code_date datetime,
starttime datetime,
g0402 varchar(120)
)
go
insert into #test select
'10','海口地区','2008-6-17','2008-6-17 9:00:00','95.144140462660826017664790120439' union all
select '11','海甸地区','2008-6-17','2008-6-17 9:00:00','95.41592657700821675497077559356'
go
select regionname,min(regionid) from #test group by regionname
--结果
/*
海甸地区11
海口地区10
*/
go
上面这样查询会显示出两条结果，而我只需要10的这条记录，并且显示regionname，不知道有什么好方法呀？
[解决办法]
海甸地区11
海口地区10
一个海口，一个海甸 不是同一组
[解决办法]
select regionname,min(regionid) from #test group by regionname
having min(regionid) = 10
[解决办法]
select regionid,regionname from #test a where not exists(select 1 from #test b where a.regionid>b.regionid)
[解决办法]
没看到题目的最小值，应该如下
select top 1 regionname,min(regionid) from #test group by regionname
order by min(regionid)
[解决办法]
select regionid,regionname from #test a where
not exists
(select 1 from #test b where a.regionid>b.regionid and a.regionname = b.regionname)
CREATE TABLE #test
(
regionid INT PRIMARY KEY ,
regionname VARCHAR(20) ,
day_code_date DATETIME ,
starttime DATETIME ,
g0402 VARCHAR(120)
)
go
INSERT INTO #test
SELECT '10','海口地区','2008-6-17','2008-6-17 9:00:00','95.144140462660826017664790120439' union all
SELECT '17','海甸地区','2008-6-17','2008-6-17 9:00:00','95.41592657700821675497077559356' union all
SELECT '14','海口地区','2008-6-17','2008-6-17 9:00:00','95.144140462660826017664790120439' union all
SELECT '11','海甸地区','2008-6-17','2008-6-17 9:00:00','95.41592657700821675497077559356'
go
SELECT TOP 1 regionid,regionname FROM #test ORDER BY regionid
/*
regionid regionname
----------- --------------------
10 海口地区
*/
DROP TABLE #test
