select max(price) as max,min(price) as min from (select price1 from table union all select price2 from table union all select price3 from table union all select price4 from table union all select price5 from table union all select price6 from table union all) a
--这样?declare @a table ( id int, value1 int, value2 int, value3 int, value4 int, value5 int, value6 int )insert into @a select 1,2,2,2,2,2,2 union all select 1,3,1,4,3,1,3 union all select 1,5,3,1,5,3,1 union all select 1,4,6,2,1,5,7select * from @aselect id,'MAX',max(value1),max(value2),max(value3),max(value4),max(value5),max(value6) from @a group by id union select id,'Min',min(value1),min(value2),min(value3),min(value4),min(value5),min(value6) from @a group by id/* id value1 value2 value3 value4 value5 value6 ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1 2 2 2 2 2 2 1 3 1 4 3 1 3 1 5 3 1 5 3 1 1 4 6 2 1 5 7id ----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- 1 MAX 5 6 4 5 5 7 1 Min 2 1 1 1 1 1 */
楼主应该是说的 用UNION那位朋友说的那个意思。
建议楼主建一个函数,求出最小的一个价格(我给出2个参数的版本),在表中增加一个公式列,公式为([dbo].[maxc]([a], [b], ...)) 这样以后需要取最大时直接取就可以了 IF EXISTS (SELECT * FROM sysobjects WHERE name = N'maxc') DROP FUNCTION maxc GOCREATE FUNCTION maxc (@c1 int , @c2 int) RETURNS int AS BEGIN select @c1= max(c) from (select @c1 as c union select @c2) d return @c1 END GO
楼上的正确. ----函数定义 create function dbo.tmpmax(@p1 int ,@p2 int ,@p3 int) returns int begin declare @max int select @max = max(p) from(select @p1 as p union select @p2 union select @p3 ) as x return @max end go ----创建测试用的临时表 declare @t table(productid int,p1 int,p2 int,p3 int) insert @t values(1,1,2,3) insert @t values(2,2,3,4) insert @t values(3,3,4,5) insert @t values(4,4,5,6) insert @t values(5,5,6,7) select * from @t ----求最大值 select productid,dbo.tmpmax(p1,p2,p3) as 最大值 from @t drop function dbo.tmpmax
declare @tab table( PID varchar(10), P1 int, P2 int, P3 int, P4 int, P5 int, P6 int )insert @tab select 'A1', 13, 14, 11, 12, 15, 20 union select 'A2', 11, 15, 18, 30, 2, 100select PID, max(price) As max, min(price) as min from ( select PID, P1 as price from @tab union all select PID, P2 as price from @tab union all select PID, P3 as price from @tab union all select PID, P4 as price from @tab union all select PID, P5 as price from @tab union all select PID, P6 as price from @tab ) a group by a.PID
(select price1 from table union all
select price2 from table union all
select price3 from table union all
select price4 from table union all
select price5 from table union all
select price6 from table union all) a
(
id int,
value1 int,
value2 int,
value3 int,
value4 int,
value5 int,
value6 int
)insert into @a
select 1,2,2,2,2,2,2 union all
select 1,3,1,4,3,1,3 union all
select 1,5,3,1,5,3,1 union all
select 1,4,6,2,1,5,7select * from @aselect id,'MAX',max(value1),max(value2),max(value3),max(value4),max(value5),max(value6) from @a group by id
union
select id,'Min',min(value1),min(value2),min(value3),min(value4),min(value5),min(value6) from @a group by id/*
id value1 value2 value3 value4 value5 value6
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 2 2 2 2 2
1 3 1 4 3 1 3
1 5 3 1 5 3 1
1 4 6 2 1 5 7id
----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 MAX 5 6 4 5 5 7
1 Min 2 1 1 1 1 1
*/
这样以后需要取最大时直接取就可以了
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'maxc')
DROP FUNCTION maxc
GOCREATE FUNCTION maxc
(@c1 int ,
@c2 int)
RETURNS int
AS
BEGIN
select @c1= max(c) from (select @c1 as c union select @c2) d
return @c1
END
GO
----函数定义
create function dbo.tmpmax(@p1 int ,@p2 int ,@p3 int)
returns int
begin
declare @max int
select @max = max(p) from(select @p1 as p union select @p2 union select @p3 ) as x
return @max
end
go
----创建测试用的临时表
declare @t table(productid int,p1 int,p2 int,p3 int)
insert @t values(1,1,2,3)
insert @t values(2,2,3,4)
insert @t values(3,3,4,5)
insert @t values(4,4,5,6)
insert @t values(5,5,6,7)
select * from @t
----求最大值
select productid,dbo.tmpmax(p1,p2,p3) as 最大值 from @t
drop function dbo.tmpmax
PID varchar(10),
P1 int,
P2 int,
P3 int,
P4 int,
P5 int,
P6 int
)insert @tab
select 'A1', 13, 14, 11, 12, 15, 20
union select 'A2', 11, 15, 18, 30, 2, 100select PID, max(price) As max, min(price) as min from
(
select PID, P1 as price from @tab
union all
select PID, P2 as price from @tab
union all
select PID, P3 as price from @tab
union all
select PID, P4 as price from @tab
union all
select PID, P5 as price from @tab
union all
select PID, P6 as price from @tab ) a group by a.PID