表A
col1 col2 col3 ...
A1 B1 C1
A1 B2 C2
A2 B2 C1
A2 B2 C2
A2 B3 C4
...
现在想查出每种col1的第一条(按col2排序),即
col1 col2 col3 ...
A1 B1 C1
A2 B2 C1
...
col1 col2 col3 ...
A1 B1 C1
A1 B2 C2
A2 B2 C1
A2 B2 C2
A2 B3 C4
...
现在想查出每种col1的第一条(按col2排序),即
col1 col2 col3 ...
A1 B1 C1
A2 B2 C1
...
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col1] [nvarchar](10),[col2] [nvarchar](10),[col3] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A1','B1','C1' UNION ALL
SELECT 'A1','B2','C2' UNION ALL
SELECT 'A2','B2','C1' UNION ALL
SELECT 'A2','B2','C2' UNION ALL
SELECT 'A2','B3','C4'
-->SQL查询如下:
SELECT *
FROM [tb] t
WHERE col2 = (
SELECT MIN(col2)
FROM tb
WHERE t.col1 = col1
)/*
col1 col2 col3
---------- ---------- ----------
A1 B1 C1
A2 B2 C1
A2 B2 C2(3 行受影响)
*/
where not exists(select 1 from a where col1=a.col1 and col2<a.col2)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(10),[col2] varchar(20),[col3] varchar(10))insert tb
select 'A1', 'B1', 'C1' union all
select 'A1', 'B2', 'C2' union all
select 'A2', 'B2', 'C1' union all
select 'A2', 'B2', 'C2' union all
select 'A2', 'B3', 'C4'
select col1,col2,col3 from(
select *,rn=row_number()over(partition by col1 order by getdate()) from tb
) t where rn=1
order by col2/*
col1 col2 col3
---------- -------------------- ----------
A1 B1 C1
A2 B2 C1(所影响的行数为 2 行)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col1] [nvarchar](10),[col2] [nvarchar](10),[col3] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A1','B1','C1' UNION ALL
SELECT 'A1','B2','C2' UNION ALL
SELECT 'A2','B2','C1' UNION ALL
SELECT 'A2','B2','C2' UNION ALL
SELECT 'A2','B3','C4'
-->SQL查询如下:
SELECT *
FROM [tb] t
WHERE col2+col3 = (
SELECT MIN(col2+col3)
FROM tb
WHERE t.col1 = col1
)/*
col1 col2 col3
---------- ---------- ----------
A1 B1 C1
A2 B2 C1(2 行受影响)
*/modify
select * from tb a
where not exists(select 1 from tb where col1 = a.col1 and col2 < a.col2)
select * from
(select id = row_number() over(PARTITION BY col1 ORDER BY getdate()),* from A ) t
where id = 1
select * , id = identity(int,1,1) into tmp from tb order by col1 , col2 , col3select t.* from tmp t where id = (select min(id) from tmp where col1 = t.col1)
select t.* from tmp t where not exists (select 1 from tmp where col1 = t.col1 and id < t.id)--sql 2005大致为:select * from
(
select t.* , id = row_number() over(partition by col1 order by col2 , col3) from tb t
) m
where id = 1