如何从sqlserver中的multiplecolum获取前两个值 [英] How to get top two values from multiplecolum in sqlserver

问题描述

你好所有



i表格中有三列标记表示



test1，其值为70，test2值为50，test3值为40



现在如何从sqlserver获得前两个值，这将是70和50









提前感谢



< b>我尝试了什么：

hello all

i have three columns in table marksheet suppose

test1 with value 70 ,test2 with value 50 and test3 with value 40

now how can i get top two values from sqlserver which will be 70 and 50




thanks in advance

What I have tried:

hello all

i have three columns in table marksheet suppose

test1 with value 70 ,test2 with value 50 and test3 with value 40

now how can i get top two values from sqlserver which will be 70 and 50




thanks in advance

推荐答案

如果我理解你是正确的

If i understand you correct

CREATE TABLE #test (
    test1 int,
   test2 int,
   test3 int
);
insert into #test (test1, test2, test3) values (70, 50, 30);
insert into #test (test1, test2, test3) values (30, 70, 50);
insert into #test (test1, test2, test3) values (50, 30, 70);
select 
case when test1 >test2 and test1>test3 then test1 when test2>test1 and test2>test3 then test2 else test3 end firstbigger ,
	case when 
		test1 >test2 and test1>test3  then case when test2>test3 then test2 else test3 end 
		when test2>test1 and test2>test3 then case when test1>test3 then test1 else test3 end  
	else case when test2>test1 then test2 else test1 end  end secondbigger

from #test

drop table #test

解决方案的替代方案1（重复表#test中每行的结果），使用相同的测试设置

An alternative to Solution 1 (which repeats the results for each row in table #test), using the same test setup

select top 2 * from
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
) q
order by 1 desc

即将所有感兴趣的值放入单个查询中，确保没有重复项（distinct和union执行此操作），然后对结果进行排序并选择前2个条目。结果：

I.e. put all of the values of interest into a single query making sure there are no duplicates (distinct and union do this) then order that result and pick the top 2 entries. Results:

datum
70
50

如果您喜欢

;WITH cte as
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
) 
select top 2 * from cte
order by 1 desc

如果您希望结果在同一行但在两列中，那么您应该使用枢。使用ROW_NUMBER有一个巧妙的技巧，可以在不使用该关键字的情况下获得TOP 2：

If you want the results in the same row but in two columns then you should use PIVOT. There is a neat trick using ROW_NUMBER which will get you the TOP 2 without using that key word:

;WITH cte as
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
)
select * from
(
	select ROW_NUMBER() OVER (ORDER BY datum desc) AS rn, datum 
	from cte
) base
PIVOT (MAX(datum) FOR rn in ([1],[2])) pvt

这篇关于如何从sqlserver中的multiplecolum获取前两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！