Ⅰ sql更新最小值所在行的數據
--樓主這個需求 不是很難 需要指出的是：已經有Check=1的數據，則不需再更新
--沒有必要這么判斷 把1更新為1 並沒什麼影響廳碰 而且如果要排除扮或談的話 語句會更長 影響性能
--處理思想就是 不用判斷 滿足條件就更新 你只需把tbname 改為你的表名就可以了
--有什麼問題可以隨時問我 希望團模採納
with tmp as(select pn,price,date ,row_number() over (partition by price order by date asc) bz
from (
select pn,price,min(date) date from tbname group by pn,price) a)
update a set a.check=bz
from tbname a join tmp b on a.PN=b.PN and a.price=b.price and a.date=b.date
Ⅱ sql取最大值和最小值
select
g_table.max_so2
,t_so2.date
,g_table.min_so2
,t_so2_min.date
....
(
select
max(so2) max＿so2
,min(so2) min_so2
,max(pm2.5) max_pm25
,min(pm2.5) min_pm25
...
,max(co) max_no2
,min(co) min_co
from table_name
) g_table
,table_name t_so2
,table_name t_so2_min
...
where g_table.max_so2 = t_so2.so2(+)
and g_table.min_so2 = t_so2_min.so2(+)
....
你這個需求有點費勁，這樣能實現，但是效率很低。
Ⅲ 求：從查詢結果里,再查詢最小值的SQL語句
select year month from 表格名
where month=(select Min(month) from 表格名 where year=(select Min(year) from 表格名))
Ⅳ sql 查詢一行內幾列的平均值，最大值，最小值，怎麼寫
oracle的話直接用greatest函數，sql server的話照這么寫：
select t1.name, max(t1.a) maxnum,min(t1.a) minnum,avg(t1.a) avgnum from
(select name,num1 a from tb1
union all
select name,num2 a from tb1
union all
select name,num3 a from tb1
union all
select name,num4 a from tb1
union all
select name,num5 a from tb1
union all
select name,num6 a from tb1
union all
select name,num7 a from tb1) t1
group by t1.name;
測試數據：
create table tb1(name varchar(50),num1 int,num2 int,num3 int,num4 int,num5 int,num6 int,num7 int)
insert into tb1 select 'Tom',1,2,6,7,9,0,3
insert into tb1 select 'Jacky',7,6,5,4,3,2,1
結果：
name maxnum minnum avgnum
Jacky 7 1 4
Tom 9 0 4
Ⅳ sql 取某些行中某列的最大值和最小值
select top 300 * from 表名 order by desc
select top 300 * from 表名 order by asc
' 為列名
Ⅵ 用SQL語句查詢最小值，最大值不能用min，max函數怎麼查
1.
--大於等於所有(最大值)
select*fromApo_city
wherecity_id>=all(selectcity_idfromApo_city)
--小於等於所有(最小值)
select*fromApo_city
wherecity_id<=all(selectcity_idfromApo_city)
--2.
--降序取第一個(最大值)
select*fromApo_city
wherecity_id=(selecttop1city_idfromApo_cityorderbycity_iddesc)
--升序取第一個(最小值)
select*fromApo_city
wherecity_id=(selecttop1city_idfromApo_cityorderbycity_idAsc)
--3.
--最大值
selectTop1city_idfromApo_cityorderbycity_iddesc
--最小值
selectTop1city_idfromApo_cityorderbycity_idAsc
--4.
--最大值
WithT
As
(
select*,ROW_NUMBER()over(orderbycity_idDesc)asidfromApo_city
)
select*fromTwhereid=1
--最小值
WithT
As
(
select*,ROW_NUMBER()over(orderbycity_idAsc)asidfromApo_city
)
select*fromTwhereid=1
5.
--不小於任何一個(最大值)
select*fromApo_city
wherenotcity_id<any(selectcity_idfromApo_city)
--不大於任何一個(最小值)
select*fromApo_city
wherenotcity_id>any(selectcity_idfromApo_city)
Ⅶ SQL從查詢結果中查最小值
SELECT C.CategoryID, C.CategoryName, SUM(F.CommentNO) AS SumComment
FROM Category AS C, Feedback AS F, Article AS A
WHERE C.CategoryID=A.CategoryID AND A.ArticleID=F.ArticleID
GROUP BY C.CategoryID, C.CategoryName having SUM(F.CommentNO)=
(select min(t1.SumComment1) from
(SELECT SUM(F.CommentNO) AS SumComment1
FROM Category AS C, Feedback AS F, Article AS A
WHERE C.CategoryID=A.CategoryID AND A.ArticleID=F.ArticleID
GROUP BY C.CategoryID, C.CategoryName) as t1)
這樣試試
Ⅷ 如何使用sql函數平均值、總數、最小值、最大值、總和、標准差
avg函數：計算查詢中某一特定欄位資料的算術平均值。
count函數：計算符合查詢條件的記錄數。
min, max函數：傳回指定欄位值中符合查詢條件的第一條、最末條記錄的資料。
first, last函數：傳回指定欄位值中符合查詢條件的最小值、最大值。
stdev函數：計算指定欄位值中符合查詢條件的標准差。
sum函數：計算指定欄位值中符合查詢條件的資料總和。
var,函數：計算指定欄位值中符合查詢條件的變異數估計值。
Ⅸ sql 求最小值 min group by
SELECT yr,min(fp1) as fp1 FROM pmyst where stcd='70221400 'group by yr order by min(fp1) desc
SQL stcd 這個欄位是什麼類型啊
如果是字元就但單引
如沒寬果是數襲困字型的 你的SQL就沒有錯誤拍察念 完全可以執行的
Ⅹ 求sql語句 多列取最小值
請查閱這里:求最小值的方法
裡面舉三個例子:
1 使用values子句生成臨時表
2使用行列轉換
3使用union all拼接臨時表
createtabletest
(namevarchar(10),time1int,time2int,time3int)
insertintotest(name,time1,time2,time3)
values
('a',1,2,3), ('b',8,9,6), ('c',11,22,8), ('d',101,201,38),
('e',6,7,9), ('f',8,8,13), ('g',2,2,30), ('h',82,56,53)
go
---方法1:使用values子句構建臨時表
selectname,(selectmin(timeMin)from(values(time1),(time2),(time3))as#temp(timeMin))astimeMinfromtest
---方法2行轉列
selectname,min(timeMin)as[最小數]fromtestunpivot(timeMinfortimeMintin(time1,time2,time3))asugroupbyname
--方法3：使用unionall組合新表
selectname,(selectmin(timeMin)
as[最小數]from(
selecttest.time1astimeMin
unionall
selecttest.time2
unionall
selecttest.time3)ud)
MaxDatefromtest
go
droptabletest
如有疑問，及時溝通！