如何从SQL Server数据库中检索最大输入值?
问题描述:
如果我有MSSQL数据库记录下面显示:如何从SQL Server数据库中检索最大输入值?
Id Serv-code Value
1 100 3
2 100 4
3 100 3
4 100 3
5 101 5
6 101 5
我想,当我使用serv_code 100然后输出将是平均装置3将由于值3被显示在文本框搜索记录的逻辑将被输入3次,如上所示....如果我搜索记录与serv_code 100,如果serv_code的值相等意味着3和4,那么最近输入的值将显示在文本框中..
答
你的问题很混乱。但是,为了回答与平均值的部分: 你应该用AVG-SQL-Function这样做这在数据库方面:
CREATE TABLE #Temp (
Id int,
ServCode INT,
Value INT)
INSERT INTO #Temp Values(1,100,3)
INSERT INTO #Temp Values(2,100,4)
INSERT INTO #Temp Values(3,100,3)
INSERT INTO #Temp Values(4,100,3)
INSERT INTO #Temp Values(5,101,5)
INSERT INTO #Temp Values(6,101,5)
select AVG(Value)FROM #Temp WHERE ServCode=100
drop table #Temp
使用此与平均每行的SERV-代码创建查询一个额外的列:
SELECT T1.*,
(SELECT AVG(Value)
FROM [#Temp] AS T2
WHERE T1.ServCode=T2.ServCode) AS average
FROM [#Temp] AS T1
答
with occurrences as (select value, occurrences, rank() over(order by occurrences desc) as rank
from (select value, count(*) as occurrences
from @data where serv_code = 101 group by value
) count_occurrences
)
select case when (select count(*) from occurrences where rank = 1) > 1
then (select top 1 data.value from @data data join occurrences on data.value = occurrences.value where data.serv_code = 101 and occurrences.rank = 1 order by id desc)
else (select value from occurrences where rank = 1)
end as value;
为什么AVG在这里使用/先生...... – 2011-01-10 15:52:59