Q1 Q2 Q3 Q4 Q5 Q6 Q7 min100 90 90 90 70 60 50 ?
100 68 77 565 12 1212 111 ?
444 44 334 66 23 23 2 ?
怎样算出每行的最小值?
SELECT [Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7] FROM [QQQ]
100 68 77 565 12 1212 111 ?
444 44 334 66 23 23 2 ?
怎样算出每行的最小值?
SELECT [Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7] FROM [QQQ]
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test](
[Q1] int,
[Q2] int,
[Q3] int,
[Q4] int,
[Q5] int,
[Q6] int,
[Q7] int,
[min] int
)
insert [test]
select 100,90,90,90,70,60,50,null union all
select 100,68,77,565,12,1212,111,null union all
select 444,44,334,66,23,23,2,null
alter table test add px int identity
go
with t
as(
select px,min(Q) as [min] from(
select px,[Q1] as Q from test
union all
select px,[Q2] from test
union all
select px,[Q3] from test
union all
select px,[Q4] from test
union all
select px,[Q5] from test
union all
select px,[Q6] from test
union all
select px,[Q7] from test)m
group by px
)update test
set [min]=t.[min] from t
where t.px=test.pxalter table test drop column px
goselect * from test/*
Q1 Q2 Q3 Q4 Q5 Q6 Q7 min
100 90 90 90 70 60 50 50
100 68 77 565 12 1212 111 12
444 44 334 66 23 23 2 2
*/
CREATE TABLE a( Q1 INT, Q2 INT, Q3 INT, Q4 INT, Q5 INT, Q6 INT, Q7 INT)
INSERT INTO a
SELECT 100, 90, 90, 90, 70, 60, 50 UNION ALL
select 100, 68, 77 ,565, 12, 1212, 111 UNION ALL
select 444 ,44 ,334 ,66 ,23 ,23, 2
select c.*,d.min_value from
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownum,* FROM a
) as c join
(
select rownum,min(value) as min_value
from
(
SELECT rownum,[type],value FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownum,*
FROM a
) AS t
UNPIVOT (VALUE FOR [type] IN (Q1,Q2,Q3,Q4,Q5,Q6,Q7)) AS p
) as b
group by b.rownum
) d on c.rownum=d.rownum
/*
rownum Q1 Q2 Q3 Q4 Q5 Q6 Q7 min_value
------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 100 90 90 90 70 60 50 50
2 100 68 77 565 12 1212 111 12
3 444 44 334 66 23 23 2 2(3 行受影响)*/
create table ea
(Q1 int, Q2 int, Q3 int, Q4 int,
Q5 int, Q6 int, Q7 int, min int)
insert into ea(Q1,Q2,Q3,Q4,Q5,Q6,Q7)
select 100, 90, 90, 90, 70, 60, 50 union all
select 100, 68, 77, 565, 12, 1212, 111 union all
select 444, 44, 334, 66, 23, 23, 2
update ea set min=
coalesce(case when Q1<Q2 and Q1<Q3 and Q1<Q4 and Q1<Q5 and Q1<Q6 and Q1<Q7 then Q1 end,
case when Q2<Q1 and Q2<Q3 and Q2<Q4 and Q2<Q5 and Q2<Q6 and Q2<Q7 then Q2 end,
case when Q3<Q1 and Q3<Q2 and Q3<Q4 and Q3<Q5 and Q3<Q6 and Q3<Q7 then Q3 end,
case when Q4<Q1 and Q4<Q2 and Q4<Q3 and Q4<Q5 and Q4<Q6 and Q4<Q7 then Q4 end,
case when Q5<Q1 and Q5<Q2 and Q5<Q3 and Q5<Q4 and Q5<Q6 and Q5<Q7 then Q5 end,
case when Q6<Q1 and Q6<Q2 and Q6<Q3 and Q6<Q4 and Q6<Q5 and Q6<Q7 then Q6 end,
case when Q7<Q1 and Q7<Q2 and Q7<Q3 and Q7<Q4 and Q7<Q5 and Q7<Q6 then Q7 end)select * from ea/*
Q1 Q2 Q3 Q4 Q5 Q6 Q7 min
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
100 90 90 90 70 60 50 50
100 68 77 565 12 1212 111 12
444 44 334 66 23 23 2 2(3 row(s) affected)
*/