每列的最后一个值查出！！！

Question

CREATE TABLE [dbo].[abc](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[D_Temp1] [nchar](10) NULL,
	[D_Temp2] [nchar](10) NULL,
	[D_Temp3] [nchar](10) NULL,
	[D_Temp4] [nchar](10) NULL,
 CONSTRAINT [PK_abc] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY],



insert into abc values(N'森罗万/象尖7',null,null,null)
insert into abc values(N'森罗万/象尖6','sdf',null,null)
insert into abc values(N'森罗万/象尖5',null,'ddd',null)
insert into abc values(N'森罗万/象尖4',null,null,'ddd')
insert into abc values(N'森罗万/象尖3',null,null,null)
insert into abc values(N'森罗万/象尖2',null,null,null)
insert into abc values(N'森罗万/象尖1',null,null,null)

我想查出每列的最后一个值，但是我写的只能一列一列的查，

select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null)
 select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null) 
 select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null) 
 select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)

请问要怎么才可以将所有列的最后个个值查出

 

Answer 1

select

 

MAX(d_temp1),MAX(D_Temp2),MAX(D_Temp3),MAX(D_Temp4) FROM abc

Answer 2

但是我要查出每列的最后一个值，你这查出的是最大值啊！！！！！

Answer 3

自己还是用

select * from (
 select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null) ) a ,
 (select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null) ) b,
( select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null) ) c ,
 (select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)) d

不是知道各位有没有更好的方法。

Answer 4

自己还是用

select * from (
 select D_Temp1 from abc where id in (select max(id) from abc where D_Temp1 is not null) ) a ,
 (select D_Temp2 from abc where id in (select max(id) from abc where D_Temp2 is not null) ) b,
( select D_Temp3 from abc where id in (select max(id) from abc where D_Temp3 is not null) ) c ,
 (select D_Temp4 from abc where id in (select max(id) from abc where D_Temp4 is not null)) d

不是知道各位有没有更好的方法。

既然可以用这个, 那就可以用这个

select D_Temp1 as a, D_Temp2 as b, D_Temp3 as c, D_Temp4 as d
 from abc
where id = (select max(id) from abc where D_Temp1 is not null)

Answer 5

... 没有注意 NULL 值的过滤

因为要考虑每个列的 NULL 值, 所以确实需要针对每个列查询

Answer 6

SQL2005以上版本可以row_number

 

;WITH Cte
AS
(
select
* ,
row1=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp1 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC),
row2=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp2 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC),
row4=ROW_NUMBER()over(ORDER BY CASE WHEN D_Temp4 IS NOT NULL THEN 1 ELSE 0 END DESC,ID DESC) 
from abc
)
SELECT
  max(CASE WHEN row1=1 THEN [D_Temp1] END) AS [D_Temp1],
  max(CASE WHEN row2=1 THEN [D_Temp2] END) AS [D_Temp2],
  max(CASE WHEN row4=1 THEN [D_Temp4] END) AS [D_Temp4]
FROM Cte
WHERE 1 IN(row1,row2,row4)

/*
D_Temp1  D_Temp2  D_Temp4
森罗万/象尖1    sdf      ddd   
*/

 

SQL2000樓主要一個語句實現，把條件放到select 后用case when 處理

---

ROY WU(吳熹 )

Answer 7

row_number 也是针对了每个列处理, 对于楼主要求的更好的方法而言并无很大的帮助.