表Tab_education
---------------------
ID person_name comefrom school InSchool_datetime
1 张三 上海人 王庄小学 1988-9-1 9:30:20
2 张三 上海人 上海大学 2009-10-1 10:30:20
3 张三 北京人 北京大学 2009-10-1 10:30:20
Tab_education表中,“张三” 同名了,有两个人都叫张三,一个是北京人,一个是上海人,
现在想查找“张三”的最高学历,将返回2条记录 即 查找条件是 person_name='张三',
想要的结果是:
ID person_name comefrom school InSchool_datetime
2 张三 上海人 上海大学 2009-10-1 10:30:20
3 张三 北京人 北京大学 2009-10-1 10:30:20
---------------------
ID person_name comefrom school InSchool_datetime
1 张三 上海人 王庄小学 1988-9-1 9:30:20
2 张三 上海人 上海大学 2009-10-1 10:30:20
3 张三 北京人 北京大学 2009-10-1 10:30:20
Tab_education表中,“张三” 同名了,有两个人都叫张三,一个是北京人,一个是上海人,
现在想查找“张三”的最高学历,将返回2条记录 即 查找条件是 person_name='张三',
想要的结果是:
ID person_name comefrom school InSchool_datetime
2 张三 上海人 上海大学 2009-10-1 10:30:20
3 张三 北京人 北京大学 2009-10-1 10:30:20
where not exists(select 1 from Tab_education where person_name=t.person_name and
InSchool_datetime >t.InSchool_datetime )
from tb t
where not exists(select 1 from tb where person_name=t.person_name and comefrom=t.comfrom and InSchool_datetime>t.InSchool_datetime)
select * from Tab_education t
where not exists(select 1 from Tab_education where person_name=t.person_name and
and comefrom =t.comefrom and InSchool_datetime >t.InSchool_datetime )
declare @Tab_education table (ID int,person_name varchar(4),comefrom varchar(6),school varchar(8),InSchool_datetime datetime)
insert into @Tab_education
select 1,'张三','上海人','王庄小学','1988-9-1 9:30:20' union all
select 2,'张三','上海人','上海大学','2009-10-1 10:30:20' union all
select 3,'张三','北京人','北京大学','2009-10-1 10:30:20'select * from @Tab_education as a where not
exists(select top 1 1 from @Tab_education where person_name=a.person_name and InSchool_datetime>a.InSchool_datetime)/*
ID person_name comefrom school InSchool_datetime
2 张三 上海人 上海大学 2009-10-01 10:30:20.000
3 张三 北京人 北京大学 2009-10-01 10:30:20.000
*/
go
create table [Tab_education]([ID] int,[person_name] varchar(4),[comefrom] varchar(6),[school] varchar(8),[InSchool_datetime] datetime)
insert [Tab_education]
select 1,'张三','上海人','王庄小学','1988-9-1 9:30:20' union all
select 2,'张三','上海人','上海大学','2009-10-1 10:30:20' union all
select 3,'张三','北京人','北京大学','2009-10-1 10:30:20'select *
from Tab_education t
where not exists(select 1 from Tab_education where person_name=t.person_name and comefrom=t.comefrom and InSchool_datetime>t.InSchool_datetime)--测试结果:
/*
ID person_name comefrom school InSchool_datetime
----------- ----------- -------- -------- -----------------------
2 张三 上海人 上海大学 2009-10-01 10:30:20.000
3 张三 北京人 北京大学 2009-10-01 10:30:20.000*/
-->Author:wufeng4552
-->Date :2009-10-13 14:40:20
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[person_name] nvarchar(2),[comefrom] nvarchar(3),[school] nvarchar(4),[InSchool_datetime] Datetime)
Insert tb
select 1,N'张三',N'上海人',N'王庄小学','1988-9-1 9:30:20' union all
select 2,N'张三',N'上海人',N'上海大学','2009-10-1 10:30:20' union all
select 3,N'张三',N'北京人',N'北京大学','2009-10-1 10:30:20'
Go
select * from tb t
where not exists(select 1 from tb where person_name=t.person_name and
comefrom =t.comefrom and InSchool_datetime >t.InSchool_datetime )
/*
ID person_name comefrom school InSchool_datetime
----------- ----------- -------- ------ -----------------------
2 张三 上海人 上海大学 2009-10-01 10:30:20.000
3 张三 北京人 北京大学 2009-10-01 10:30:20.000(2 個資料列受到影響)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-13 14:40:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[person_name] varchar(4),[comefrom] varchar(6),[school] varchar(8),[InSchool_datetime] datetime)
insert [tb]
select 1,'张三','上海人','王庄小学','1988-9-1 9:30:20' union all
select 2,'张三','上海人','上海大学','2009-10-1 10:30:20' union all
select 3,'张三','北京人','北京大学','2009-10-1 10:30:20'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where person_name=t.person_name and comefrom=t.comefrom and [InSchool_datetime]>t.[InSchool_datetime])
----------------结果----------------------------
/* ID person_name comefrom school InSchool_datetime
----------- ----------- -------- -------- -----------------------
2 张三 上海人 上海大学 2009-10-01 10:30:20.000
3 张三 北京人 北京大学 2009-10-01 10:30:20.000(2 行受影响)*/
where InSchool_datetime in
(
select max(InSchool_datetime) InSchool_datetime from Tab_education where person_name='张三'
group by person_name,comefrom,school
) tab
declare @Tab_education table (ID int,person_name varchar(4),comefrom varchar(6),school varchar(8),InSchool_datetime datetime)
insert into @Tab_education
select 1,'张三','上海人','王庄小学','1988-9-1 9:30:20' union all
select 2,'张三','上海人','上海大学','2009-10-1 10:30:20' union all
select 3,'张三','北京人','北京大学','2009-10-1 10:30:20'select * from @Tab_education as a where not
exists(select top 1 1 from @Tab_education where person_name=a.person_name and comefrom=a.comefrom and InSchool_datetime>a.InSchool_datetime)select a.* from @Tab_education as a
inner join (
select person_name,comefrom,MAX(InSchool_datetime) as InSchool_datetime
from @Tab_education group by person_name,comefrom
) as b on a.person_name=b.person_name and a.comefrom=b.comefrom and a.InSchool_datetime=b.InSchool_datetime
if object_id('[Tab_education]') is not null drop table [tb]
go
create table [Tab_education]([ID] int,[person_name] varchar(4),[comefrom] varchar(6),[school] varchar(8),[InSchool_datetime] datetime)
insert [Tab_education]
select 1,'张三','上海人','王庄小学','1988-9-1 9:30:20' union all
select 2,'张三','上海人','上海大学','2009-10-1 10:30:20' union all
select 3,'张三','北京人','北京大学','2009-10-1 10:30:20'
--------------开始查询--------------------------select * from Tab_education
where InSchool_datetime in
(
select max(InSchool_datetime) InSchool_datetime from Tab_education where person_name='张三'
group by person_name,comefrom
) /*
ID person_name comefrom school InSchool_datetime
----------- ----------- -------- -------- ------------------------------------------------------
2 张三 上海人 上海大学 2009-10-01 10:30:20.000
3 张三 北京人 北京大学 2009-10-01 10:30:20.000
*/
select 1 as n/*
n
-----------
1
*/
你看一下就明白了
--> 测试数据:[tc]
if object_id('[tc]') is not null drop table [tc]
go
create table [tc]([ID] int,[Name] varchar(4))
insert [tc]
select 1,'张三' union all
select 2,'张三' union all
select 3,'张三'select 1 from [tc] --返回常量1,记录条数为tc表的记录/*
-----------
1
1
1(3 行受影响)*/
另外问一下 select 1 from tb 的 “select 1 ” 是什么意思啊? 多谢
是不是等于 select top 1 啊?
wufeng4552 大侠的答案 不太好懂,郁闷