数据库中有多个字段:ID、NUM,Year
假设:ID NUM Year
100 1 1995
100 2 1996
100 3 1997
101 1 1990
101 2 1996
........
请问我如何创建一个视图只把同一个ID的NUM的最大值所对应的行显示出来??
得到的结果应该像这样
ID NUM Year
100 3 1997
101 2 1996
假设:ID NUM Year
100 1 1995
100 2 1996
100 3 1997
101 1 1990
101 2 1996
........
请问我如何创建一个视图只把同一个ID的NUM的最大值所对应的行显示出来??
得到的结果应该像这样
ID NUM Year
100 3 1997
101 2 1996
insert into tableA select 100,'1','1995'
union all select 100,'2','1996'
union all select 100,'3','1997'
union all select 101,'1','1990'
union all select 101,'2','1996'
go
--创建视图
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE VIEW dbo.[as]
AS
SELECT id, MAX(num) AS num, MAX([year]) AS year
FROM dbo.tableA
GROUP BY idGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
use test
go
create table tb
(
ID int,
NUM int,
Year int
)
insert into tb select 100,1,1995
union all select 100,2,1996
union all select 100,3,1997
union all select 101,1,1990
union all select 101,2,1996
select * from tb ta where not exists(select 1 from tb where ID=ta.ID and NUM>ta.NUM)
drop table tb
/*
ID NUM Year
----------- ----------- -----------
100 3 1997
101 2 1996(所影响的行数为 2 行)
*/
AS
SELECT *
FROM T
JOIN
(
SELECT ID , MAX(NUM) AS NUM
FROM T
GROUP BY ID
) AS B
ON T.ID = B.ID
AND T.NUM = B.NUM
(
A int,
B int
)insert A select 1,2
insert A select 1,3
insert A select 1,4
insert A select 2,2
insert A select 2,3
insert A select 2,4
insert A select 3,5
select * from A B where not exists(select 1 from A where A=B.A and B>B.B)
drop table A
/*
A B
----------- -----------
1 4
2 4
3 5(所影响的行数为 3 行)
*/
create view t_v
asselect b.* from t b
inner join
(select max(num) num , id
from t) a
on a.id = b.id and a.num = b.num