original data and target data
Implement SQL statement (max)
select shop, month, greatest(dz,fz,sp) as max from tablename;
Implement the SQL statement (minimum)
select shop, month, least (dz,fz,sp) as min from tablename;
Supplement: Three methods for SQL Server to get the maximum value of multiple columns in a row
Sometimes we need to get a maximum value from multiple columns in a table (the data types of these columns are the same, and each row record needs to get a maximum value). Three methods are described below, let's take a look.
First, we create a table and insert the required records into the table, as follows:
create table Demo( Guid varchar(50) not null default newid() primary key, Date1 datetime null, Date2 datetime null, Date3 datetime null )
Then we insert test data into the table
insert into Demo(Date1,Date2,Date3) values ('2016-9-5','2016-8-6','2016-10-9'), ('2015-5-6','2015-8-6','2015-6-3'), ('2016-10-6','2015-6-6','2016-9-6') select * from Demo
We need to get the maximum value of the three date columns recorded in each row above.
Method 1 (high efficiency):
select Guid,(select Max(NewDate) from (values (Date1),(Date2),(Date3)) as #temp(NewDate)) as MaxDate from Demo
Method 2 (high efficiency):
select Guid, max(NewDate) as MaxDate from Demo unpivot (NewDate for DateVal in (Date1,Date2,Date3)) as u group by Guid
Method three (low efficiency, not recommended):
select Guid, (select max(NewDate) as MaxDate from (select Demo.Date1 as NewDate union select Demo.Date2 union select Demo.Date3)ud) MaxDate from Demo
The first method uses the values clause to construct each row of data into a table with only one field, and find the maximum value later, which is very clever;
The second method uses the UNPIVOT keyword, which is often used in row to column, to convert and display;
The third method is similar to the first method, but uses union to combine the three UpdateByAppDate fields into a result set with only one field and then find the maximum value.
The above is a personal experience, I hope it can give you a reference, and I hope you can support developpaer a lot. If there are any mistakes or not considered completely, please let me know.