select CASE_ID,USER_ID,max(CASE_PRICE) as CASE_PRICE from tb group by CASE_ID,USER_ID
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[T_HISTORY]') AND type in (N'U')) --U 代表你查询的是表 DROP TABLE T_HISTORY GO---->建表 CREATE TABLE T_HISTORY( [CASE_ID] [nvarchar](50) NOT NULL, --货物名 [CASE_PRICE] INT NULL, --货物价格 [USER_ID] [nvarchar](50) NULL) insert into T_HISTORY select '货物1','100','收货人' union all select '货物1','22','收货人' union all select '货物1','50','2收货人' union all select '货物2','82','收货人4' union all select '货物2','26','2收货人4' union all select '货物3','321','收货人5' GO--> 查询结果 SELECT * FROM T_HISTORY t where not exists (select 1 from T_HISTORY where t.[CASE_ID]=[CASE_ID] and t.[CASE_PRICE]<[CASE_PRICE] ) --> 删除表格 --DROP TABLE [TB]
select CASE_ID,min(USER_ID),max(CASE_PRICE) as CASE_PRICE from tb group by CASE_ID
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[T_HISTORY]') AND type in (N'U')) --U 代表你查询的是表 DROP TABLE T_HISTORY GO---->建表 CREATE TABLE T_HISTORY( [CASE_ID] [nvarchar](50) NOT NULL, --货物名 [CASE_PRICE] INT NULL, --货物价格 [USER_ID] [nvarchar](50) NULL) insert into T_HISTORY select '货物1','100','收货人' union all select '货物1','22','收货人' union all select '货物1','50','2收货人' union all select '货物2','82','收货人4' union all select '货物2','82','2收货人4' union all select '货物3','321','收货人5' GO--> 查询结果 SELECT [CASE_ID],[CASE_PRICE],min([USER_ID])as [USER_ID] FROM T_HISTORY t where not exists (select 1 from T_HISTORY where t.[CASE_ID]=[CASE_ID] and t.[CASE_PRICE]<[CASE_PRICE] --加个条件就好了 ) group by [CASE_ID],[CASE_PRICE] --> 删除表格 --DROP TABLE [TB]
;WITH CTE AS ( SELECT DR=DENSE_RANK() OVER(PARTITION BY CASE_ID ORDER BY CASE_PRICE DESC),* FROM TB ) SELECT * FROM CTE WHERE DR=1
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[T_HISTORY]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE T_HISTORY
GO---->建表
CREATE TABLE T_HISTORY(
[CASE_ID] [nvarchar](50) NOT NULL, --货物名
[CASE_PRICE] INT NULL, --货物价格
[USER_ID] [nvarchar](50) NULL)
insert into T_HISTORY
select '货物1','100','收货人' union all
select '货物1','22','收货人' union all
select '货物1','50','2收货人' union all
select '货物2','82','收货人4' union all
select '货物2','26','2收货人4' union all
select '货物3','321','收货人5'
GO--> 查询结果
SELECT * FROM T_HISTORY t
where not exists (select 1 from T_HISTORY where t.[CASE_ID]=[CASE_ID] and t.[CASE_PRICE]<[CASE_PRICE] )
--> 删除表格
--DROP TABLE [TB]
您这个不行,min吧USER_ID最小的取出来,但没把MAX(CASE_PRICE)对应的那个USER_ID取出来
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[T_HISTORY]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE T_HISTORY
GO---->建表
CREATE TABLE T_HISTORY(
[CASE_ID] [nvarchar](50) NOT NULL, --货物名
[CASE_PRICE] INT NULL, --货物价格
[USER_ID] [nvarchar](50) NULL)
insert into T_HISTORY
select '货物1','100','收货人' union all
select '货物1','22','收货人' union all
select '货物1','50','2收货人' union all
select '货物2','82','收货人4' union all
select '货物2','82','2收货人4' union all
select '货物3','321','收货人5'
GO--> 查询结果
SELECT [CASE_ID],[CASE_PRICE],min([USER_ID])as [USER_ID] FROM T_HISTORY t
where not exists (select 1 from T_HISTORY where t.[CASE_ID]=[CASE_ID] and t.[CASE_PRICE]<[CASE_PRICE]
--加个条件就好了
)
group by [CASE_ID],[CASE_PRICE]
--> 删除表格
--DROP TABLE [TB]
;WITH CTE AS
(
SELECT DR=DENSE_RANK() OVER(PARTITION BY CASE_ID ORDER BY CASE_PRICE DESC),* FROM TB
)
SELECT * FROM CTE WHERE DR=1