请教SQL问题。查询最小值的ID
ID NO Price
239TRA13120001490.00
240TRA13120001100.00
241TRA13120001490.00
242TRA13120001480.00
243TRA13120009490.00
244TRA13120009490.00
245TRA13120009430.00
246TRA13120009490.00
247TRA13120002450.00
248TRA13120002490.00
249TRA13120002490.00
上面是数据库数据，求教各位如何写sql语句，去除NO重复，查询出最小Price 的ID 不要使用Order By 我试了好多，都不行谢谢各位了！
得到结果
240
245
247
[解决办法]
with tb as
(
select NO, min(price) as minprice
from 表格
group by NO
)
select 表格.* from 表格
inner join tb on tb.NO = 表格.NO and tb.minprice = 表格.price
[解决办法]
--建表语句
create table tb(ID int, NO varchar(20), Price numeric(20,2))
insert into tb
select 239,'TRA13120001',490.00 union all
select 240,'TRA13120001',100.00 union all
select 241,'TRA13120001',490.00 union all
select 242,'TRA13120001',480.00 union all
select 243,'TRA13120009',490.00 union all
select 244,'TRA13120009',490.00 union all
select 245,'TRA13120009',430.00 union all
select 246,'TRA13120009',490.00 union all
select 247,'TRA13120002',450.00 union all
select 248,'TRA13120002',490.00 union all
select 249,'TRA13120002',490.00
go
--查询
select ID
from
(
select *,
ROW_NUMBER() over(partition by NO order by Price) as rownum
from tb
)t
where rownum = 1
order by ID
/*
ID
240
245
247
*/
--查询
select ID
from
(
select *,
(select COUNT(*) from tb t2
where t1.NO = t2.NO and t1.Price >= t2.Price and t1.ID <> t2.ID) as rownum
from tb t1
)t
where rownum = 0
order by ID
/*
ID
240
245
247
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-12 21:11:58
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[NO] varchar(11),[Price] numeric(5,2))
insert [huang]
select 239,'TRA13120001',490.00 union all
select 240,'TRA13120001',100.00 union all
select 241,'TRA13120001',490.00 union all
select 242,'TRA13120001',480.00 union all
select 243,'TRA13120009',490.00 union all
select 244,'TRA13120009',490.00 union all
select 245,'TRA13120009',430.00 union all
select 246,'TRA13120009',490.00 union all
select 247,'TRA13120002',450.00 union all
select 248,'TRA13120002',490.00 union all
select 249,'TRA13120002',490.00
--------------开始查询--------------------------
select * from [huang] a
WHERE EXISTS (SELECT 1 FROM(SELECT MIN(Price)Price ,[no] FROM huang GROUP BY [no])b WHERE a.price=b.Price AND a.[no]=b.[no])
----------------结果----------------------------
/*
ID NO Price
----------- ----------- ---------------------------------------
240 TRA13120001 100.00
245 TRA13120009 430.00
247 TRA13120002 450.00
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-12 21:11:58
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[NO] varchar(11),[Price] numeric(5,2))
insert [huang]
select 239,'TRA13120001',490.00 union all
select 240,'TRA13120001',100.00 union all
select 241,'TRA13120001',490.00 union all
select 242,'TRA13120001',480.00 union all
select 243,'TRA13120009',490.00 union all
select 244,'TRA13120009',490.00 union all
select 245,'TRA13120009',430.00 union all
select 246,'TRA13120009',490.00 union all
select 247,'TRA13120002',450.00 union all
select 248,'TRA13120002',490.00 union all
select 249,'TRA13120002',490.00
--------------开始查询--------------------------
select ID from [huang] a
WHERE EXISTS (SELECT 1 FROM(SELECT MIN(Price)Price ,[no] FROM huang GROUP BY [no])b WHERE a.price=b.Price AND a.[no]=b.[no])
----------------结果----------------------------
/*
ID
-----------
240
245
247
*/
create table #tb(ID int,NO varchar(20),Price numeric(12,2))
insert into #tb
select 239,'TRA13120001',490.00
union all select 240,'TRA13120001',100.00
union all select 241,'TRA13120001',490.00
union all select 242,'TRA13120001',480.00
union all select 243,'TRA13120009',490.00
union all select 244,'TRA13120009',490.00
union all select 245,'TRA13120009',430.00
union all select 246,'TRA13120009',490.00
union all select 247,'TRA13120002',450.00
union all select 248,'TRA13120002',490.00
union all select 249,'TRA13120002',490.00
go
select a.id
from #tb a
inner join(select min(price) as price,no from #tb group by no)b
on a.price=b.price and a.no=b.no
order by a.id
/*
240
245
247
*/
select a.id
from #tb a
where not exists(select 1 from #tb b where a.no=b.no and a.price>b.price)
order by a.id
/*
240
245
247
*/
