有表结构如下
id UserId ProductId price
1 1 1 14
2 1 1 15
3 3 1 16
4 5 1 17
5 6 2 12
6 4 2 15
7 4 5 20
id为主键
UserID为用户id..
Product为商品id
price是该用户为该商品的出价.
现在想得到每一种商品的所对应的最高价格.所对应的出价人.例如..
id UserId ProductId price
4 5 1 17
6 4 2 15
7 4 5 20
id UserId ProductId price
1 1 1 14
2 1 1 15
3 3 1 16
4 5 1 17
5 6 2 12
6 4 2 15
7 4 5 20
id为主键
UserID为用户id..
Product为商品id
price是该用户为该商品的出价.
现在想得到每一种商品的所对应的最高价格.所对应的出价人.例如..
id UserId ProductId price
4 5 1 17
6 4 2 15
7 4 5 20
-- Author : htl258(Tony)
-- Date : 2010-03-30 23:03:13
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[UserId] INT,[ProductId] INT,[price] INT)
INSERT [tb]
SELECT 1,1,1,14 UNION ALL
SELECT 2,1,1,15 UNION ALL
SELECT 3,3,1,16 UNION ALL
SELECT 4,5,1,17 UNION ALL
SELECT 5,6,2,12 UNION ALL
SELECT 6,4,2,15 UNION ALL
SELECT 7,4,5,20
GO
--SELECT * FROM [tb]-->SQL查询如下:
select *
from tb t
where id=(
select MAX(id) from tb
where [ProductId]=t.[ProductId])
select *
from tb t
where not exists(
select 1 from tb
where [ProductId]=t.[ProductId]
and id>t.id)
/*
id UserId ProductId price
----------- ----------- ----------- -----------
4 5 1 17
6 4 2 15
7 4 5 20(3 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[UserId] int,[ProductId] int,[price] int)
insert [tb]
select 1,1,1,14 union all
select 2,1,1,15 union all
select 3,3,1,16 union all
select 4,5,1,17 union all
select 5,6,2,12 union all
select 6,4,2,15 union all
select 7,4,5,20select * from [tb] a where
not exists
(
select 1 from tb where [ProductId]=a.[ProductId] and [price]>a.[price]
)
/*
id UserId ProductId price
----------- ----------- ----------- -----------
4 5 1 17
6 4 2 15
7 4 5 20(3 行受影响)
*/