select * from a t where 字段3=(select min(字段3) from a where 字段1=t.字段1)
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-03-05 16:15:22 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[A] if object_id('[A]') is not null drop table [A] go create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime) insert [A] select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' --------------生成数据--------------------------select [字段1],MIN([字段3])[字段3] from [A] GROUP BY [字段1] ----------------结果---------------------------- /* 字段1 字段3 ----------- ----------------------- 1 2012-04-03 00:00:00.000 2 2012-05-23 00:00:00.000 */
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-03-05 16:15:22 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[A] if object_id('[A]') is not null drop table [A] go create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime) insert [A] select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' --------------生成数据-------------------------- SELECT * FROM A A WHERE EXISTS (SELECT 1 FROM ( select [字段1],MIN([字段3])[字段3] from [A] GROUP BY [字段1])B WHERE A.[字段1]=B.[字段1] AND A.[字段3]=B.[字段3]) ----------------结果---------------------------- /* 字段1 字段2 字段3 ----------- ---- ----------------------- 1 01 2012-04-03 00:00:00.000 1 02 2012-04-03 00:00:00.000 2 05 2012-05-23 00:00:00.000 */
create table 表A (字段1 int,字段2 nvarchar(5), 字段3 datetime) insert into 表A select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' select a.* from 表A a inner join ( select 字段1,dt=min(字段3) from 表A group by 字段1 ) b on a.字段1=b.字段1 and a.字段3=b.dt /* 1 01 2012-04-03 00:00:00.000 1 02 2012-04-03 00:00:00.000 2 05 2012-05-23 00:00:00.000 */
这样呢?---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-03-05 16:15:22 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[A] if object_id('[A]') is not null drop table [A] go create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime) insert [A] select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' --------------生成数据-------------------------- SELECT [字段1],MAX(字段2)字段2,字段3 FROM A A WHERE EXISTS (SELECT 1 FROM ( select [字段1],MIN([字段3])[字段3] from [A] GROUP BY [字段1])B WHERE A.[字段1]=B.[字段1] AND A.[字段3]=B.[字段3]) GROUP BY [字段1],字段3 ----------------结果---------------------------- /* 字段1 字段2 字段3 ----------- ---- ----------------------- 1 02 2012-04-03 00:00:00.000 2 05 2012-05-23 00:00:00.000 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-03-05 16:34:47 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段1] int,[字段2] varchar(2),[字段3] datetime) insert [tb] select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' --------------开始查询-------------------------- select * from tb t where not exists(select 1 from tb where 字段1=t.字段1 and (字段3<t.字段3 OR 字段3=t.字段3 AND 字段2<t.字段2)) ----------------结果---------------------------- /* 字段1 字段2 字段3 ----------- ---- ----------------------- 1 01 2012-04-03 00:00:00.000 2 05 2012-05-23 00:00:00.000(2 行受影响)*/
看不懂各算一个是啥意思--> 测试数据[A] if object_id('[A]') is not null drop table [A] go create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime) insert [A] select 1,'01','2012-04-03' union all select 1,'02','2012-04-03' union all select 2,'03','2012-06-13' union all select 2,'05','2012-05-23' --------------生成数据--------------------------select [字段1],MIN([字段2])[字段2],[字段3] from [A] GROUP BY [字段1],[字段3]/* 字段1 字段2 字段3 ----------- ---- ----------------------- 1 01 2012-04-03 00:00:00.000 2 05 2012-05-23 00:00:00.000 2 03 2012-06-13 00:00:00.000*/
比如说表A如下: 事件 人员 发生时间 a 01 2012-03-04 a 02 2012-03-04 a 03 2012-05-23 找出事件a中发生时间最早的记录,很明显 会出现前两条,那这样的话就算人员01一个,人员02一个
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-05 16:15:22
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime)
insert [A]
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23'
--------------生成数据--------------------------select [字段1],MIN([字段3])[字段3] from [A]
GROUP BY [字段1]
----------------结果----------------------------
/*
字段1 字段3
----------- -----------------------
1 2012-04-03 00:00:00.000
2 2012-05-23 00:00:00.000
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-05 16:15:22
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime)
insert [A]
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23'
--------------生成数据--------------------------
SELECT *
FROM A A
WHERE EXISTS (SELECT 1 FROM (
select [字段1],MIN([字段3])[字段3] from [A]
GROUP BY [字段1])B WHERE A.[字段1]=B.[字段1] AND A.[字段3]=B.[字段3])
----------------结果----------------------------
/*
字段1 字段2 字段3
----------- ---- -----------------------
1 01 2012-04-03 00:00:00.000
1 02 2012-04-03 00:00:00.000
2 05 2012-05-23 00:00:00.000
*/
create table 表A (字段1 int,字段2 nvarchar(5), 字段3 datetime)
insert into 表A
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23' select a.*
from 表A a
inner join (
select 字段1,dt=min(字段3) from 表A
group by 字段1
) b
on a.字段1=b.字段1 and a.字段3=b.dt
/*
1 01 2012-04-03 00:00:00.000
1 02 2012-04-03 00:00:00.000
2 05 2012-05-23 00:00:00.000
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-05 16:15:22
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime)
insert [A]
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23'
--------------生成数据--------------------------
SELECT [字段1],MAX(字段2)字段2,字段3
FROM A A
WHERE EXISTS (SELECT 1 FROM (
select [字段1],MIN([字段3])[字段3] from [A]
GROUP BY [字段1])B WHERE A.[字段1]=B.[字段1] AND A.[字段3]=B.[字段3])
GROUP BY [字段1],字段3
----------------结果----------------------------
/*
字段1 字段2 字段3
----------- ---- -----------------------
1 02 2012-04-03 00:00:00.000
2 05 2012-05-23 00:00:00.000
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-05 16:34:47
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] int,[字段2] varchar(2),[字段3] datetime)
insert [tb]
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where 字段1=t.字段1 and (字段3<t.字段3 OR 字段3=t.字段3 AND 字段2<t.字段2))
----------------结果----------------------------
/* 字段1 字段2 字段3
----------- ---- -----------------------
1 01 2012-04-03 00:00:00.000
2 05 2012-05-23 00:00:00.000(2 行受影响)*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([字段1] int,[字段2] nvarchar(4),[字段3] datetime)
insert [A]
select 1,'01','2012-04-03' union all
select 1,'02','2012-04-03' union all
select 2,'03','2012-06-13' union all
select 2,'05','2012-05-23'
--------------生成数据--------------------------select [字段1],MIN([字段2])[字段2],[字段3] from [A]
GROUP BY [字段1],[字段3]/*
字段1 字段2 字段3
----------- ---- -----------------------
1 01 2012-04-03 00:00:00.000
2 05 2012-05-23 00:00:00.000
2 03 2012-06-13 00:00:00.000*/
事件 人员 发生时间
a 01 2012-03-04
a 02 2012-03-04
a 03 2012-05-23
找出事件a中发生时间最早的记录,很明显 会出现前两条,那这样的话就算人员01一个,人员02一个