34,576
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [tab2]
IF OBJECT_ID('[tab2]') IS NOT NULL
DROP TABLE [tab2]
GO
CREATE TABLE [tab2] ([ItemName] [nvarchar](10),[ItemValue] [int])
INSERT INTO [tab2]
SELECT '张三','1' UNION ALL
SELECT '张三','2' UNION ALL
SELECT '李四','1' UNION ALL
SELECT '李四','5' UNION ALL
SELECT '王五','1'
--SELECT * FROM [tab2]
-->SQL查询如下:
--1.
;WITH T AS
(
SELECT RN=ROW_NUMBER()OVER(PARTITION BY ItemName ORDER BY GETDATE()),*
FROM tab2
)
SELECT [ItemName],[ItemValue]
FROM T
WHERE RN=1
/*
ItemName ItemValue
---------- -----------
李四 1
王五 1
张三 1
(3 行受影响)
*/
--> 测试数据: #tab2
if object_id('tempdb.dbo.#tab2') is not null drop table #tab2
create table #tab2 (ItemName varchar(4),ItemValue int)
insert into #tab2
select '张三',1 union all
select '张三',2 union all
select '李四',1 union all
select '李四',5 union all
select '王五',1
with cte as (select row_number() over(order by getdate()) id,itemname,itemvalue from #tab2 t )
select itemname,itemvalue from cte t where not exists(select 1 from cte where t.itemname=itemname and t.id>id)
itemname itemvalue
-------- -----------
张三 1
李四 1
王五 1
(3 行受影响)
select ItemName, ItemValue from
(select row=row_number() over(partition by ItemName order by ItemValue)
,* from tab2) T
where row=1
select ItemName ,max(ItemValue ) from tab2 group by ItemName
select ItemName ,min(ItemValue ) from tab2 group by ItemName
select *
from tab2 t
where not exists(select 1
from tab2
where ItemName =t.ItemName and ItemValue>t.ItemValue)
select itemname,min(Itemvalue) from [Table] group by itemname
--> 测试数据: #tab2
if object_id('tempdb.dbo.#tab2') is not null drop table #tab2
create table #tab2 (ItemName varchar(4),ItemValue int)
insert into #tab2
select '张三',1 union all
select '张三',2 union all
select '李四',1 union all
select '李四',5 union all
select '王五',1
select * from #tab2 t
where not exists(select 1 from #tab2 where t.itemname=itemname and t.itemvalue>itemvalue)
(5 行受影响)
ItemName ItemValue
-------- -----------
张三 1
李四 1
王五 1
(3 行受影响)
select ItemName ,min(ItemValue ) from tab2 group by ItemName
select *
from tab2 t
where ItemValue=(select min(ItemValue)
from tab2
where ItemName =t.ItemName )