求一查询语句。

Question

从下表(a_tab)中:
编号     状态       注册时间       注销时间      姓名        类别
001       1        2009-01-01         -          zhang        1
002       1        2009-02-06         -          wu           2
003       1        2009-06-06         -          lin          1
006       1        2009-09-09         -          hou          1
007       0            -          2009-10-10     mo           1
008       1        2009-11-11         -          li           2
002       0            -          2009-12-12     wu           1
006       0            -          2010-01-01     hou          1
001       1        2009-11-01         -          zhang        2
008       0            -          2010-01-02     li           2
008       1        2010-02-02         -          li           3
得到:
编号      姓名        类别
001       zhang        2
003       lin          1
008       li           3得到结果的条件:
1.未被注销的记录.
2.同一编号有多条记录则取日期最大的记录.

Answer 1

select *
from a_tab t
where  not exists (select 1 from a_tab where 姓名=t.姓名 and (注册时间>t.注册时间 or 注销时间 is not null))

Answer 2

ACMAIN_CHM:
谢谢你!我试过结果不对.
 
003 1 2009-06-06 NULL lin 1
001 1 2009-11-01 NULL zhang 2
少
008 1 2010-02-02 - li 3

Answer 3

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-02 22:45:29
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[状态] INT,[注册时间] NVARCHAR(10),[注销时间] NVARCHAR(10),[姓名] NVARCHAR(10),[类别] INT)
INSERT [tb]
SELECT '001',1,N'2009-01-01',N'-','zhang',1 UNION ALL
SELECT '002',1,N'2009-02-06',N'-','wu',2 UNION ALL
SELECT '003',1,N'2009-06-06',N'-','lin',1 UNION ALL
SELECT '006',1,N'2009-09-09',N'-','hou',1 UNION ALL
SELECT '007',0,N'-',N'2009-10-10','mo',1 UNION ALL
SELECT '008',1,N'2009-11-11',N'-','li',2 UNION ALL
SELECT '002',0,N'-',N'2009-12-12','wu',1 UNION ALL
SELECT '006',0,N'-',N'2010-01-01','hou',1 UNION ALL
SELECT '001',1,N'2009-11-01',N'-','zhang',2 UNION ALL
SELECT '008',0,N'-',N'2010-01-02','li',2 UNION ALL
SELECT '008',1,N'2010-02-02',N'-','li',3
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb t where [状态]=1
and 注册时间=(
select top 1 注册时间 from tb 
where 编号=t.编号 and [状态]=1 
order by 注册时间 desc)
/*
编号         状态          注册时间       注销时间       姓名         类别
---------- ----------- ---------- ---------- ---------- -----------
002        1           2009-02-06 -          wu         2
003        1           2009-06-06 -          lin        1
006        1           2009-09-09 -          hou        1
001        1           2009-11-01 -          zhang      2
008        1           2010-02-02 -          li         3(5 行受影响)
*/这什么不是这个结果,这些人不同姓名,也都是有效状态.

Answer 4

编号         状态          注册时间       注销时间       姓名         类别
---------- ----------- ---------- ---------- ---------- -----------
002        1           2009-02-06 -          wu         2
003        1           2009-06-06 -          lin        1
006        1           2009-09-09 -          hou        1
001        1           2009-11-01 -          zhang      2
008        1           2010-02-02 -          li         3
这什么不是这个结果,这些人不同姓名,也都是有效状态.--------------003,006已注销

Answer 5

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[状态] INT,[注册时间] NVARCHAR(10),[注销时间] NVARCHAR(10),[姓名] NVARCHAR(10),[类别] INT)
INSERT [tb]
SELECT '001',1,N'2009-01-01',N'-','zhang',1 UNION ALL
SELECT '002',1,N'2009-02-06',N'-','wu',2 UNION ALL
SELECT '003',1,N'2009-06-06',N'-','lin',1 UNION ALL
SELECT '006',1,N'2009-09-09',N'-','hou',1 UNION ALL
SELECT '007',0,N'-',N'2009-10-10','mo',1 UNION ALL
SELECT '008',1,N'2009-11-11',N'-','li',2 UNION ALL
SELECT '002',0,N'-',N'2009-12-12','wu',1 UNION ALL
SELECT '006',0,N'-',N'2010-01-01','hou',1 UNION ALL
SELECT '001',1,N'2009-11-01',N'-','zhang',2 UNION ALL
SELECT '008',0,N'-',N'2010-01-02','li',2 UNION ALL
SELECT '008',1,N'2010-02-02',N'-','li',3
GO
select * from tb t where 
not exists (select 1 from tb a
where a.编号=t.编号 
and a.注销时间 <>'-' 
and a.注销时间 >(select max(c.注册时间) from tb c where c.编号=a.编号))
and 注册时间=(
    select top 1 注册时间 from tb 
    where 编号=t.编号
    order by 注册时间 desc)编号         状态          注册时间       注销时间       姓名         类别          
---------- ----------- ---------- ---------- ---------- ----------- 
003        1           2009-06-06 -          lin        1
001        1           2009-11-01 -          zhang      2
008        1           2010-02-02 -          li         3（所影响的行数为 3 行）

Answer 6

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[状态] INT,[注册时间] NVARCHAR(10),[注销时间] NVARCHAR(10),[姓名] NVARCHAR(10),[类别] INT)
INSERT [tb]
SELECT '001',1,N'2009-01-01',N'-','zhang',1 UNION ALL
SELECT '002',1,N'2009-02-06',N'-','wu',2 UNION ALL
SELECT '003',1,N'2009-06-06',N'-','lin',1 UNION ALL
SELECT '006',1,N'2009-09-09',N'-','hou',1 UNION ALL
SELECT '007',0,N'-',N'2009-10-10','mo',1 UNION ALL
SELECT '008',1,N'2009-11-11',N'-','li',2 UNION ALL
SELECT '002',0,N'-',N'2009-12-12','wu',1 UNION ALL
SELECT '006',0,N'-',N'2010-01-01','hou',1 UNION ALL
SELECT '001',1,N'2009-11-01',N'-','zhang',2 UNION ALL
SELECT '008',0,N'-',N'2010-01-02','li',2 UNION ALL
SELECT '008',1,N'2010-02-02',N'-','li',3
GO
SELECT 
    *
FROM 
    [tb] a
where 
    状态 = 1
    and 类别 <> 1
    and 注册时间 = (select max(注册时间) from tb where 编号 = a.编号 and 状态 = 1 and 类别 <> 1)--结果
/*
编号         状态          注册时间       注销时间       姓名         类别
---------- ----------- ---------- ---------- ---------- -----------
008        1           2010-02-02 -          li         3
002        1           2009-02-06 -          wu         2
001        1           2009-11-01 -          zhang      2(3 行受影响)*/

Answer 7

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[状态] INT,[注册时间] NVARCHAR(10),[注销时间] NVARCHAR(10),[姓名] NVARCHAR(10),[类别] INT)
INSERT [tb]
SELECT '001',1,N'2009-01-01',N'-','zhang',1 UNION ALL
SELECT '002',1,N'2009-02-06',N'-','wu',2 UNION ALL
SELECT '003',1,N'2009-06-06',N'-','lin',1 UNION ALL
SELECT '006',1,N'2009-09-09',N'-','hou',1 UNION ALL
SELECT '007',0,N'-',N'2009-10-10','mo',1 UNION ALL
SELECT '008',1,N'2009-11-11',N'-','li',2 UNION ALL
SELECT '002',0,N'-',N'2009-12-12','wu',1 UNION ALL
SELECT '006',0,N'-',N'2010-01-01','hou',1 UNION ALL
SELECT '001',1,N'2009-11-01',N'-','zhang',2 UNION ALL
SELECT '008',0,N'-',N'2010-01-02','li',2 UNION ALL
SELECT '008',1,N'2010-02-02',N'-','li',3
GOselect  zc.编号,zc.姓名,zc.类别
from 
    (SELECT a.编号,a.姓名,a.类别,a.注册时间
    FROM tb a,(select MAX(注册时间) 注册时间,编号 from tb group by 编号) t
    WHERE 状态='1' and a.编号=t.编号 and a.注册时间=t.注册时间) zc
    left join
    (select a.编号,a.姓名,a.类别,a.注销时间
    FROM tb a,(select MAX(注销时间) 注销时间,编号 from tb group by 编号) t
    WHERE 状态='0' and a.编号=t.编号 and a.注销时间=t.注销时间) zx
    on zc.编号=zx.编号
group by zc.编号,zc.姓名,zc.类别
having (max(注册时间) >= isnull(max(注销时间),'19000101'))编号           姓名         类别          
---------- ----------- ----------- 
003          lin          1
001          zhang        2
008          li           3