我有下表:
column1 column2 column3 3 2 0 5 9 2 1 4 6
当我运行以下代码时:
SELECT id_function = @param, MIN(t1.column1) AS c1min, MAX(t1.column2) AS c2max, MIN(t1.column3) AS c3min FROM table1 (NOLOCK) AS t1 WHERE t1.id = @param
我明白了:
c1min c2max c3min 1 9 0
我的问题是c3min必须是大于零的最小值.我需要的结果应该是:
c1min c2max c3min 1 9 2
没有使用subselect有没有办法做到这一点?任何帮助将不胜感激.
谢谢!
我建议使用nullif(),以便您的查询
SELECT id_function = @param, MIN(t1.column1) AS c1min, MAX(t1.column2) AS c2max, MIN(NULLIF(t1.column3,0) AS c3min FROM table1 (NOLOCK) AS t1 WHERE t1.id = @param
这样你就不会冒险改变你的结果,例如,如果第3列中的真实最小值为100,则前一个答案会影响你的结果,如果你的第3栏中只有零,那么之前的答案也会产生不正确的结果
你可以使用case
所设置的0
值在您的较高值min()
条件
SELECT id_function = @param, MIN(t1.column1) AS c1min, MAX(t1.column2) AS c2max, MIN(case when t1.column3 = 0 then 99 else t1.column3 end) AS c3min FROM table1 (NOLOCK) AS t1 WHERE t1.id = @param