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
(
select NO, min(price) as minprice
from 表格
group by NO
)
select 表格.* from 表格
inner join tb on tb.NO = 表格.NO and tb.minprice = 表格.price
解决方案 »
- SQL Server如何查看备份一个数据库用了多久?
- 视图
- 赋值语句怎么写
- 安装好SQL Server后,为什么还原数据库时,会自动关闭企业管理器工具?
- ***************请问怎么样查看一个数据表所占的页数以及索引页数.不连续的空间信息*************
- 怎么加了order by 后不能更新记录 请问
- 对于两个表的联结查询效率问题,有没有什么技巧,高手们给分析一下,有好方法,把分加满
- 请帮忙写一个简单的存储过程,谢谢
- 数据丢失的问题
- VFP中,怎么使最大化按扭无效?
- 这个怎么查询? 学表类的。。。 ################################100% 结贴
- sql中如何通过触发器实现某列禁止修改
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
*/
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
goselect 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
*/
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
*/