先谢谢了!
10 个解决方案
#1
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
(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
#2
--这样?
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,7
select * from @a
select 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 7
id
----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 MAX 5 6 4 5 5 7
1 Min 2 1 1 1 1 1
*/
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,7
select * from @a
select 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 7
id
----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 MAX 5 6 4 5 5 7
1 Min 2 1 1 1 1 1
*/
#3
楼主应该是说的 用UNION那位朋友说的那个意思。
#4
建议楼主建一个函数,求出最小的一个价格(我给出2个参数的版本),在表中增加一个公式列,公式为([dbo].[maxc]([a], [b], ...))
这样以后需要取最大时直接取就可以了
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'maxc')
DROP FUNCTION maxc
GO
CREATE 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
这样以后需要取最大时直接取就可以了
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'maxc')
DROP FUNCTION maxc
GO
CREATE 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
#5
楼上的正确.
----函数定义
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
----函数定义
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
#6
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, 100
select 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
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, 100
select 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
#7
楼上的庖丁解牛!
#8
正解了!
#9
多谢各位!
#10
奇怪,我想给分,却发现自己一分都没有了,炸回事?
#1
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
(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
#2
--这样?
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,7
select * from @a
select 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 7
id
----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 MAX 5 6 4 5 5 7
1 Min 2 1 1 1 1 1
*/
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,7
select * from @a
select 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 7
id
----------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 MAX 5 6 4 5 5 7
1 Min 2 1 1 1 1 1
*/
#3
楼主应该是说的 用UNION那位朋友说的那个意思。
#4
建议楼主建一个函数,求出最小的一个价格(我给出2个参数的版本),在表中增加一个公式列,公式为([dbo].[maxc]([a], [b], ...))
这样以后需要取最大时直接取就可以了
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'maxc')
DROP FUNCTION maxc
GO
CREATE 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
这样以后需要取最大时直接取就可以了
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'maxc')
DROP FUNCTION maxc
GO
CREATE 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
#5
楼上的正确.
----函数定义
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
----函数定义
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
#6
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, 100
select 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
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, 100
select 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
#7
楼上的庖丁解牛!
#8
正解了!
#9
多谢各位!
#10
奇怪,我想给分,却发现自己一分都没有了,炸回事?