select min(mMin) from ( select min(c1) as mMin from test union all select min(c2) as mMin from test . . . )A
select min(mMin) from ( select min(c1) as mMin from test union all select min(c2) as mMin from test . . . )A where a.mmin>0
select min(mMin) from ( select min(c1) as mMin from test where c1>0 union all select min(c2) as mMin from test where c2>0 . . . )A
CREATE TABLE tb(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT) INSERT tb SELECT 10,2,3,-1,-2 UNION ALL SELECT 55,3,4,21,-4--取所有行的最小正数 SELECT MIN(c1) FROM (SELECT c1 FROM tb UNION SELECT c2 FROM tb UNION SELECT c3 FROM tb UNION SELECT c4 FROM tb UNION SELECT c5 FROM tb) x WHERE c1>0--如果是每行分别取: SELECT ID=IDENTITY(INT),C1,C2,C3,C4,C5 INTO tmp FROM tb SELECT MIN(c1) mc FROM (SELECT c1,ID FROM tmp UNION SELECT c2,ID FROM tmp UNION SELECT c3,ID FROM tmp UNION SELECT c4,ID FROM tmp UNION SELECT c5,ID FROM tmp) x WHERE c1>0 GROUP BY IDDROP TABLE tb DROP TABLE tmp
(
select min(c1) as mMin from test
union all
select min(c2) as mMin from test
.
.
.
)A
(
select min(c1) as mMin from test
union all
select min(c2) as mMin from test
.
.
.
)A where a.mmin>0
(
select min(c1) as mMin from test where c1>0
union all
select min(c2) as mMin from test where c2>0
.
.
.
)A
CREATE TABLE tb(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT)
INSERT tb SELECT 10,2,3,-1,-2
UNION ALL SELECT 55,3,4,21,-4--取所有行的最小正数
SELECT MIN(c1) FROM
(SELECT c1 FROM tb
UNION
SELECT c2 FROM tb
UNION
SELECT c3 FROM tb
UNION
SELECT c4 FROM tb
UNION
SELECT c5 FROM tb) x
WHERE c1>0--如果是每行分别取:
SELECT ID=IDENTITY(INT),C1,C2,C3,C4,C5 INTO tmp FROM tb
SELECT MIN(c1) mc FROM
(SELECT c1,ID FROM tmp
UNION
SELECT c2,ID FROM tmp
UNION
SELECT c3,ID FROM tmp
UNION
SELECT c4,ID FROM tmp
UNION
SELECT c5,ID FROM tmp) x
WHERE c1>0
GROUP BY IDDROP TABLE tb
DROP TABLE tmp