语句显示如下:
SELECT
CASE WHEN WCLJF > 100000
THEN (WCLJF - 100000) * 0.05+2000
ELSE CASE WHEN WCLJF BETWEEN 50000 AND 100000
THEN ( WCLJF - 50000 ) * 0.04 +1200
ELSE CASE WHEN WCLJF BETWEEN 30000 AND 50000
THEN ( WCLJF - 30000 ) * 0.03 + 600
ELSE CASE WHEN WCLJF BETWEEN 5000 AND 30000
THEN (WCLJF * 0.02)
END
END
END
END
FROM BFCRM.HYK_JFZH
运行下来后还有一些不符合上面的条件比如5000以下的,这样一来查询的结果5000以下的显示为NULL,我想让结果只显示有值的,NULL值的我不想它在结果中显示出来,还有升序语句加在哪?
SELECT
CASE WHEN WCLJF > 100000
THEN (WCLJF - 100000) * 0.05+2000
ELSE CASE WHEN WCLJF BETWEEN 50000 AND 100000
THEN ( WCLJF - 50000 ) * 0.04 +1200
ELSE CASE WHEN WCLJF BETWEEN 30000 AND 50000
THEN ( WCLJF - 30000 ) * 0.03 + 600
ELSE CASE WHEN WCLJF BETWEEN 5000 AND 30000
THEN (WCLJF * 0.02)
END
END
END
END
FROM BFCRM.HYK_JFZH
运行下来后还有一些不符合上面的条件比如5000以下的,这样一来查询的结果5000以下的显示为NULL,我想让结果只显示有值的,NULL值的我不想它在结果中显示出来,还有升序语句加在哪?
解决方案 »
- 关于SQL一些难题 大家快来看看
- sql 语句查询一段日期
- vb6中Format(数值,"###,##0.######")可以输出像这样的格式,1,234,567.89 我用SQL自定义函数实现了,请高手来挑刺。
- sqlserver2008 如何处理膨胀了很大的日志?
- 菜鸟问题,怎么保护特定数据库!
- 这个索引怎么建
- 这种表设计如何查询更快
- T-SQL怎么才能够跨服务名和跨数据库查询数据,代码怎么写?
- 左搜右搜[求助]
- 关于SQL SERVER2000数据恢复的问题
- SQL SERVER 2005 X64 没有接口Microsoft OLE DB Provider for Oracle
- 如何在SQLServer2005里查找替换多行字符?
ELSE CASE WHEN WCLJF BETWEEN 50000 AND 100000
THEN ( WCLJF - 50000 ) * 0.04 + 1200
ELSE CASE WHEN WCLJF BETWEEN 30000 AND 50000
THEN ( WCLJF - 30000 ) * 0.03 + 600
ELSE CASE WHEN WCLJF BETWEEN 5000 AND 30000
THEN ( WCLJF * 0.02 ) ELSE 0
END
END
END
END
FROM BFCRM.HYK_JFZH
WHERE WCLJF >= 5000
ORDER BY WCLJF asc这样?
CASE WHEN WCLJF > 100000 THEN (WCLJF - 100000) * 0.05+2000
CASE WHEN WCLJF BETWEEN 50000 AND 100000 THEN ( WCLJF - 50000 ) * 0.04 +1200
CASE WHEN WCLJF BETWEEN 30000 AND 50000 THEN ( WCLJF - 30000 ) * 0.03 + 600
CASE WHEN WCLJF BETWEEN 5000 AND 30000 THEN (WCLJF * 0.02)
ELSE 0 END AS WXLJF
FROM BFCRM.HYK_JFZH
直接用where过滤掉5000的值不就OK了。
CASE WHEN WCLJF > 100000 THEN (WCLJF - 100000) * 0.05+2000
CASE WHEN WCLJF BETWEEN 50000 AND 100000 THEN ( WCLJF - 50000 ) * 0.04 +1200
CASE WHEN WCLJF BETWEEN 30000 AND 50000 THEN ( WCLJF - 30000 ) * 0.03 + 600
CASE WHEN WCLJF BETWEEN 5000 AND 30000 THEN (WCLJF * 0.02)
ELSE 0 END AS WXLJF
FROM BFCRM.HYK_JFZH where WCLJF >= 5000
SELECT CASE WHEN WCLJF > 100000 THEN (WCLJF - 100000) * 0.05+2000
WHEN WCLJF BETWEEN 50000 AND 100000 THEN ( WCLJF - 50000 ) * 0.04 +1200
WHEN WCLJF BETWEEN 30000 AND 50000 THEN ( WCLJF - 30000 ) * 0.03 + 600
WHEN WCLJF BETWEEN 5000 AND 30000 THEN (WCLJF * 0.02)
END
FROM FROM BFCRM.HYK_JFZH WHERE WCLJF>5000
SELECT
CASE WHEN WCLJF > 100000 THEN ltrim((WCLJF - 100000) * 0.05+2000))
WHEN WCLJF BETWEEN 50000 AND 100000 THEN ltrim(( WCLJF - 50000 ) * 0.04 +1200)
WHEN WCLJF BETWEEN 30000 AND 50000 THEN ltrim(( WCLJF - 30000 ) * 0.03 + 600)
WHEN WCLJF BETWEEN 5000 AND 30000 THEN ltrim((WCLJF * 0.02))
else ''
END
FROM BFCRM.HYK_JFZH