现有一表数据 如下,
id name type s_time e_time tel txt
1 萧萧 汽车保险 2006-1-4 2007-1-5 13800000001 11
2 黄芬 人身意外险 2005-3-1 2006-3-1 13800000002 22
3 黄芬 人身意外险 2006-3-2 2007-3-2 13800000002 33
4 萧萧 汽车保险 2007-1-6 2008-1-6 13800000001 44
5 黄芬 汽车保险 2005-1-1 2006-1-1 13800000002 55
6 李琼 责任险 2004-11-13 2005-11-13 13799999991 66
7 张三 汽车保险 2008-1-7 2009-1-7 13500000002 77
8 刘牛 财产保险 2005-12-13 2006-12-13 13700000001 88
9 刘牛 财产保险 2004-12-12 2005-12-12 13700000001 99
10 萧萧 汽车保险 2008-1-7 2009-1-8 13800000002 100
11 关之林 汽车保险 2004-11-13 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2009-1-10 2010-1-10 13800066666 其他险我需要按照这张表里,每个人特定保险项目的 险期截止时间(e_time)的最大值,来获取 所有列的数据比如:对 名叫 萧萧的人总共有2种保险,
一种是 汽车保险,在数据库里有3条相应的记录第1 条,第4条 和第10条,那么 我只需要找到10条,其余的 1,4条过滤掉
另一种保险 是 第12条的 意外险, 因为只有一条记录, 不需要再摔选 理想结果应该是是:id name type s_time e_time tel txt
3 黄芬 人身意外险 2006-3-2 2007-3-2 13800000002 33
5 黄芬 汽车保险 2005-1-1 2006-1-1 13800000002 55
6 李琼 责任险 2004-11-13 2005-11-13 13799999991 66
7 张三 汽车保险 2008-1-7 2009-1-7 13500000002 77
8 刘牛 财产保险 2005-12-13 2006-12-13 13700000001 88
10 萧萧 汽车保险 2008-1-7 2009-1-8 13800000002 100
11 关之林 汽车保险 2004-11-13 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2009-1-10 2010-1-10 13800066666 其他险 select name,type,max(e_time)as time from yabx group by type,name
结果是
关之林 汽车保险 2005-11-13
黄芬 汽车保险 2006-01-01
黄芬 人身意外险 2007-03-02
李琼 责任险 2005-11-13
刘牛 财产保险 2006-12-13
萧萧 汽车保险 2009-01-08
萧萧 意外险 2010-01-10
张三 汽车保险 2009-01-07 没法 显示 tel,和 txt 字段 , 我需要 显示所有字段, 不知道如何能实现。存储过程 和 语句都可以。
id name type s_time e_time tel txt
1 萧萧 汽车保险 2006-1-4 2007-1-5 13800000001 11
2 黄芬 人身意外险 2005-3-1 2006-3-1 13800000002 22
3 黄芬 人身意外险 2006-3-2 2007-3-2 13800000002 33
4 萧萧 汽车保险 2007-1-6 2008-1-6 13800000001 44
5 黄芬 汽车保险 2005-1-1 2006-1-1 13800000002 55
6 李琼 责任险 2004-11-13 2005-11-13 13799999991 66
7 张三 汽车保险 2008-1-7 2009-1-7 13500000002 77
8 刘牛 财产保险 2005-12-13 2006-12-13 13700000001 88
9 刘牛 财产保险 2004-12-12 2005-12-12 13700000001 99
10 萧萧 汽车保险 2008-1-7 2009-1-8 13800000002 100
11 关之林 汽车保险 2004-11-13 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2009-1-10 2010-1-10 13800066666 其他险我需要按照这张表里,每个人特定保险项目的 险期截止时间(e_time)的最大值,来获取 所有列的数据比如:对 名叫 萧萧的人总共有2种保险,
一种是 汽车保险,在数据库里有3条相应的记录第1 条,第4条 和第10条,那么 我只需要找到10条,其余的 1,4条过滤掉
另一种保险 是 第12条的 意外险, 因为只有一条记录, 不需要再摔选 理想结果应该是是:id name type s_time e_time tel txt
3 黄芬 人身意外险 2006-3-2 2007-3-2 13800000002 33
5 黄芬 汽车保险 2005-1-1 2006-1-1 13800000002 55
6 李琼 责任险 2004-11-13 2005-11-13 13799999991 66
7 张三 汽车保险 2008-1-7 2009-1-7 13500000002 77
8 刘牛 财产保险 2005-12-13 2006-12-13 13700000001 88
10 萧萧 汽车保险 2008-1-7 2009-1-8 13800000002 100
11 关之林 汽车保险 2004-11-13 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2009-1-10 2010-1-10 13800066666 其他险 select name,type,max(e_time)as time from yabx group by type,name
结果是
关之林 汽车保险 2005-11-13
黄芬 汽车保险 2006-01-01
黄芬 人身意外险 2007-03-02
李琼 责任险 2005-11-13
刘牛 财产保险 2006-12-13
萧萧 汽车保险 2009-01-08
萧萧 意外险 2010-01-10
张三 汽车保险 2009-01-07 没法 显示 tel,和 txt 字段 , 我需要 显示所有字段, 不知道如何能实现。存储过程 和 语句都可以。
id name type e_time tel txt
1 萧萧 汽车保险 2007-1-5 13800000001 11
2 黄芬 人身意外险 2006-3-1 13800000002 22
3 黄芬 人身意外险 2007-3-2 13800000002 33
4 萧萧 汽车保险 2008-1-6 13800000001 44
5 黄芬 汽车保险 2006-1-1 13800000002 55
6 李琼 责任险 2005-11-13 13799999991 66
7 张三 汽车保险 2009-1-7 13500000002 77
8 刘牛 财产保险 2006-12-13 13700000001 88
9 刘牛 财产保险 2005-12-12 13700000001 99
10 萧萧 汽车保险 2009-1-8 13800000002 100
11 关之林 汽车保险 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2010-1-10 13800066666 其他险我需要按照这张表里,每个人特定保险项目的 险期截止时间(e_time)的最大值,来获取 所有列的数据
比如:
对 名叫 萧萧的人
总共有2种保险,
一种是 汽车保险,在数据库里有3条相应的记录第1 条,第4条 和第10条,那么 我只需要找到10条,其余的 1,4条过滤掉
另一种保险 是 第12条的 意外险, 因为只有一条记录, 不需要再摔选
理想结果应该是是:
id name type e_time tel txt
3 黄芬 人身意外险 2007-3-2 13800000002 33
5 黄芬 汽车保险 2006-1-1 13800000002 55
6 李琼 责任险 2005-11-13 13799999991 66
7 张三 汽车保险 2009-1-7 13500000002 77
8 刘牛 财产保险 2006-12-13 13700000001 88
10 萧萧 汽车保险 2009-1-8 13800000002 100
11 关之林 汽车保险 2005-11-13 13900000022 beyond11
12 萧萧 意外险 2010-1-10 13800066666 其他险
create table tb1
(id int ,
name varchar(100),
type varchar(100),
s_time datetime,
e_time datetime,
tel varchar(15),
txt varchar(100)
)
insert into tb1
select '1','萧萧','汽车保险','2006-1-4','2007-1-5','13800000001','11' union all
select '2','黄芬','人身意外险','2005-3-1','2006-3-1','13800000002','22' union all
select '3','黄芬','人身意外险','2006-3-2','2007-3-2','13800000002','33' union all
select '4','萧萧','汽车保险','2007-1-6','2008-1-6','13800000001','44' union all
select '5','黄芬','汽车保险','2005-1-1','2006-1-1','13800000002','55' union all
select '6','李琼','责任险','2004-11-13','2005-11-13','13799999991','66' union all
select '7','张三','汽车保险','2008-1-7','2009-1-7','13500000002','77' union all
select '8','刘牛','财产保险','2005-12-13','2006-12-13','13700000001','88' union all
select '9','刘牛','财产保险','2004-12-12','2005-12-12','13700000001','99' union all
select '10','萧萧','汽车保险','2008-1-7','2009-1-8','13800000002','100' union all
select '11','关之林','汽车保险','2004-11-13','2005-11-13','13900000022','beyond11' union all
select '12','萧萧','意外险','2009-1-10','2010-1-10','13800066666','其他险'go
select max(a.id) as id,name,type,max(e_time)as time,
(select top 1 tel from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as tel,
(select top 1 txt from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as txt
from tb1 a group by type,name
order by max(a.id)/*3 黄芬 人身意外险 2007-03-02 00:00:00.000 13800000002 33
5 黄芬 汽车保险 2006-01-01 00:00:00.000 13800000002 55
6 李琼 责任险 2005-11-13 00:00:00.000 13799999991 66
7 张三 汽车保险 2009-01-07 00:00:00.000 13500000002 77
9 刘牛 财产保险 2006-12-13 00:00:00.000 13700000001 99--这里是要99还是88???
10 萧萧 汽车保险 2009-01-08 00:00:00.000 13800000002 100
11 关之林 汽车保险 2005-11-13 00:00:00.000 13900000022 beyond11
12 萧萧 意外险 2010-01-10 00:00:00.000 13800066666 其他险*/
drop table tb1
(select top 1 tel from tb1 where id=max(a.id)) as tel,
(select top 1 txt from tb1 where id=max(a.id)) as txt
from tb1 a group by type,name
order by max(a.id)
create view bx as
(
select max(a.id) as id,name,type,max(e_time)as time,
(select top 1 tel from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as tel,
(select top 1 txt from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as txt
from tb1 a group by type,name
order by max(a.id)
)会报错,只能写成:
create view as
(
select top 100000000 max(a.id) as id,name,type,max(e_time)as time,
(select top 1 tel from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as tel,
(select top 1 txt from tb1 where checksum(name,type)=checksum(a.name,a.type) and id=max(a.id)) as txt
from tb1 a group by type,name
order by max(a.id))
有没有 更好的办法??
--建立測試環境
Create Table tb1
(id int ,
name Nvarchar(100),
type Nvarchar(100),
s_time datetime,
e_time datetime,
tel varchar(15),
txt Nvarchar(100)
)
--插入數據
Insert Into tb1
Select '1',N'萧萧',N'汽车保险','2006-1-4','2007-1-5','13800000001','11' Union All
Select '2',N'黄芬',N'人身意外险','2005-3-1','2006-3-1','13800000002','22' Union All
Select '3',N'黄芬',N'人身意外险','2006-3-2','2007-3-2','13800000002','33' Union All
Select '4',N'萧萧',N'汽车保险','2007-1-6','2008-1-6','13800000001','44' Union All
Select '5',N'黄芬',N'汽车保险','2005-1-1','2006-1-1','13800000002','55' Union All
Select '6',N'李琼',N'责任险','2004-11-13','2005-11-13','13799999991','66' Union All
Select '7',N'张三',N'汽车保险','2008-1-7','2009-1-7','13500000002','77' Union All
Select '8',N'刘牛',N'财产保险','2005-12-13','2006-12-13','13700000001','88' Union All
Select '9',N'刘牛',N'财产保险','2004-12-12','2005-12-12','13700000001','99' Union All
Select '10',N'萧萧',N'汽车保险','2008-1-7','2009-1-8','13800000002','100' Union All
Select '11',N'关之林',N'汽车保险','2004-11-13','2005-11-13','13900000022','beyond11' Union All
Select '12',N'萧萧',N'意外险','2009-1-10','2010-1-10','13800066666',N'其他险'
GO
--測試
--方法一:
Select
A.*
From tb1 A
Inner Join (Select Name,type,Max(e_time) As e_time From tb1 Group By Name,type )B
On A.Name=B.Name And A.type=B.type And A.e_time=B.e_time
Order By A.id
--方法二:
Select * From tb1 A
Where Not Exists(Select 1 From tb1 Where Name=A.Name And type=A.type And e_time>A.e_time)
GO
--刪除測試環境
Drop Table tb1
--結果
/*
id name type s_time e_time tel txt
3 黄芬 人身意外险 2006-03-02 00:00:00.000 2007-03-02 00:00:00.000 13800000002 33
5 黄芬 汽车保险 2005-01-01 00:00:00.000 2006-01-01 00:00:00.000 13800000002 55
6 李琼 责任险 2004-11-13 00:00:00.000 2005-11-13 00:00:00.000 13799999991 66
7 张三 汽车保险 2008-01-07 00:00:00.000 2009-01-07 00:00:00.000 13500000002 77
8 刘牛 财产保险 2005-12-13 00:00:00.000 2006-12-13 00:00:00.000 13700000001 88
10 萧萧 汽车保险 2008-01-07 00:00:00.000 2009-01-08 00:00:00.000 13800000002 100
11 关之林 汽车保险 2004-11-13 00:00:00.000 2005-11-13 00:00:00.000 13900000022 beyond11
12 萧萧 意外险 2009-01-10 00:00:00.000 2010-01-10 00:00:00.000 13800066666 其他险
*/
Create View bx
As
Select TOP 100 Percent
A.*
From tb1 A
Inner Join (Select Name,type,Max(e_time) As e_time From tb1 Group By Name,type )B
On A.Name=B.Name And A.type=B.type And A.e_time=B.e_time
Order By A.id
GO
--方法二:
Create View bx
As
Select TOP 100 Percent * From tb1 A
Where Not Exists(Select 1 From tb1 Where Name=A.Name And type=A.type And e_time>A.e_time)
Order By id
GO