PartNo UnitPrice CutIn_From Seller Expr1 Buyer Cnt
0001 10 2009-4-23 AAA M BBB 22
0001 12 2009-4-25 AAB M BBB 24
0002 20 2009-4-27 AAA M BBB 26
0002 22 2009-4-29 AAB M BBB 28
0002 24 2009-4-30 AAB M BBB 29如何找出每个PartNo中Cnt最小的行?结果为:
PartNo UnitPrice CutIn_From Seller Expr1 Buyer Cnt
0001 10 2009-4-23 AAA M BBB 22
0002 20 2009-4-27 AAA M BBB 26谁能告诉我,谢谢!
0001 10 2009-4-23 AAA M BBB 22
0001 12 2009-4-25 AAB M BBB 24
0002 20 2009-4-27 AAA M BBB 26
0002 22 2009-4-29 AAB M BBB 28
0002 24 2009-4-30 AAB M BBB 29如何找出每个PartNo中Cnt最小的行?结果为:
PartNo UnitPrice CutIn_From Seller Expr1 Buyer Cnt
0001 10 2009-4-23 AAA M BBB 22
0002 20 2009-4-27 AAA M BBB 26谁能告诉我,谢谢!
解决方案 »
- SQL查询语句
- SQL2005打SP1或SP2补丁后有什么区别,不打可以吗
- 一個有趣的問題:請問CMD下at命令可以執行sql server裏的procedure嗎?
- 怎么进行模糊查询?
- 存储过程中一点语法的问题,急呀!
- 求问一个关于在某一段日期内查询的语句!!
- 急求:没有主键,怎么做到发布复制一个表?
- 如何能select出来一个空列,用来以后放入名列的合计值?
- 关于SQL分布式表分区的问题(分不够可以再加)
- 难度查询问题的继续给分贴子,回答过问题的请进
- 多网卡服务器,如何让MS-SQL只接受从其中一个网卡的连接请求(多IP服务器,如何实现单IP监听)?[不用防火墙]
- 设置字段的默认值为随机100以内的整数??
from tb t
where not exists(
select 1
from tb
where partno=t.partno
and UnitPrice<t.UnitPrice)
Cnt<t.Cnt)
PartNo,CutIn_From,Seller,Expr1,Buyer,Cnt
from tb t
where not exists(
select 1
from tb
where partno=t.partno
and cnt<t.cnt)modify.不好意思,看错.
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-06 17:41:12
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([PartNo] varchar(4),[UnitPrice] int,[CutIn_From] datetime,[Seller] varchar(3),[Expr1] varchar(1),[Buyer] varchar(3),[Cnt] int)
insert [tb]
select '0001',10,'2009-4-23','AAA','M','BBB',22 union all
select '0001',12,'2009-4-25','AAB','M','BBB',24 union all
select '0002',20,'2009-4-27','AAA','M','BBB',26 union all
select '0002',22,'2009-4-29','AAB','M','BBB',28 union all
select '0002',24,'2009-4-30','AAB','M','BBB',29
--------------开始查询--------------------------
select * from tb a where Cnt=(select min(Cnt) from tb where PartNo=a.PartNo)
----------------结果----------------------------
/*PartNo UnitPrice CutIn_From Seller Expr1 Buyer Cnt
------ ----------- ------------------------------------------------------ ------ ----- ----- -----------
0001 10 2009-04-23 00:00:00.000 AAA M BBB 22
0002 20 2009-04-27 00:00:00.000 AAA M BBB 26(所影响的行数为 2 行)
*/
只查出来了1条数据
select * from tb a where Cnt=(select min(Cnt) from tb where PartNo=a.PartNo)
select * from tb t
where not exists(select 1 from tb where partno=t.partno and cnt<t.cnt)
因为min只能查到表里最小的那个值吧这个一条都没有
select * from tb t
where not exists(select 1 from tb where partno=t.partno and cnt<t.cnt)
FROM DataTable
GROUP BY DataTable.PartNo;这样写的话可以找出我所需要的20条数据了
但是要怎么把其它字段的值也显示?
declare @tb table([PartNo] varchar(4),[UnitPrice] int,[CutIn_From] datetime,[Seller] varchar(3),[Expr1] varchar(1),[Buyer] varchar(3),[Cnt] int)
insert @tb
select '0001',10,'2009-4-23','AAA','M','BBB',22 union all
select '0001',10,'2009-4-23','AAC','M','BBB',22 union all
select '0001',12,'2009-4-25','AAB','M','BBB',24 union all
select '0002',20,'2009-4-27','AAA','M','BBB',26 union all
select '0002',22,'2009-4-29','AAB','M','BBB',28 union all
select '0002',24,'2009-4-30','AAB','M','BBB',29select [PartNo],[UnitPrice],[CutIn_From],[Seller],[Expr1],[Buyer],[Cnt]
from
(
select *,ord=(row_number() over (partition by PartNo order by cnt)) from @tb
) t
where ord=1/*
PartNo UnitPrice CutIn_From Seller Expr1 Buyer Cnt
0001 10 2009-04-23 00:00:00.000 AAA M BBB 22
0002 20 2009-04-27 00:00:00.000 AAA M BBB 26
*/