我想实现的功能是,在下表中:
ID NAME PRICE
1 wang 12
1 wang 13
1 wang 14
2 zhao 12
2 zhao 13
2 zhao 14
3 qian 12
3 qian 13
3 qian 14用什么语句能取到每个相同ID的第一项呢,即结果是:
ID NAME PRICE
1 wang 12
2 zhao 12
3 qian 12
ID NAME PRICE
1 wang 12
1 wang 13
1 wang 14
2 zhao 12
2 zhao 13
2 zhao 14
3 qian 12
3 qian 13
3 qian 14用什么语句能取到每个相同ID的第一项呢,即结果是:
ID NAME PRICE
1 wang 12
2 zhao 12
3 qian 12
解决方案 »
- 数据库用户访问量突然增大导致运行缓慢,该怎么处理。
- 请教~~~~这样的 sql语句能不能这么写?
- 求一sql语句,在线等,解决马上结贴
- phantomMan(去年Delphi,年底.net,今年5月SQL,现在XML,马上Socket............)接分
- SQL2008
- sqlserver2008全文检索问题,特急!
- 昨天我不小心进行了误更新操作,把表中所有数据都变成重复的了,忘了备份,求高手帮忙!急!!!!!!
- 数据库课程设计,大家帮我出点想法!
- 有谁知道powerdesigner
- 请教怎么取得存储过程中的查询记录集,字段和表 都是动态的
- 帮忙写个数据库初始化sql,把一个时间段内的周六日插进某张表;万分感谢!
- 日期转换失败
select * from tb a where not exists(
select 1 from tb where id=a.id and name=a.name and price>a.price
)
select t.* from tb t where price = (select top 1 price from tb where id = t.id order by price) order by t.idselect t.* from tb t where price = (select min(price) from tb where id = t.id) order by t.idselect t.* from tb t where not exists(select 1 from tb where id = t.id and price < t.price) order by t.id
select id,name,min(price) from tb
group by id,name
*
from
tb t
where
PRICE=(select min(PRICE) from tb where id=t.id)
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY GETDATE()) AS NUM
FROM TAB
)
SELECT ID,NAME,PRICE
FROM CTE
WHERE NUM = 1
insert into tb values(1 ,'wang', 12)
insert into tb values(1 ,'wang', 13)
insert into tb values(1 ,'wang', 14)
insert into tb values(2 ,'zhao', 12)
insert into tb values(2 ,'zhao', 13)
insert into tb values(2 ,'zhao', 14)
insert into tb values(3 ,'qian', 12)
insert into tb values(3 ,'qian', 13)
insert into tb values(3 ,'qian', 14)
go
select t.* from tb t where price = (select top 1 price from tb where id = t.id order by price) order by t.idselect t.* from tb t where price = (select min(price) from tb where id = t.id) order by t.idselect t.* from tb t where not exists(select 1 from tb where id = t.id and price < t.price) order by t.id
drop table tb
/*
ID NAME PRICE
----------- ---------- -----------
1 wang 12
2 zhao 12
3 qian 12(所影响的行数为 3 行)ID NAME PRICE
----------- ---------- -----------
1 wang 12
2 zhao 12
3 qian 12(所影响的行数为 3 行)ID NAME PRICE
----------- ---------- -----------
1 wang 12
2 zhao 12
3 qian 12(所影响的行数为 3 行)*/
go
if object_id('tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
name char(10),
price float
)
goinsert into tb select '1', 'wang', '12'
insert into tb select '1', 'wang', '13'
insert into tb select '1', 'wang', '14'
insert into tb select '2', 'zhao', '12'
insert into tb select '2', 'zhao', '13'
insert into tb select '2', 'zhao', '14'
insert into tb select '3', 'qian', '12'
insert into tb select '3', 'qian', '13'
insert into tb select '3', 'qian', '14' select * from tb
select ID,name,price from
(select *,ROW_NUMBER()over(partition by id order by price )newid from tb)b
where newid = 1drop table tb