自定义分组 写一个查询
来源:互联网 发布:网络空间主权 编辑:程序博客网 时间:2024/06/06 10:49
有一个查询,按房屋面积范围分组查询,并且要根据面积状态类型来区分面积, 直接上图
SQL 如下:
SELECT
CASE
WHEN (MJZTLX=0 AND YCMJ_JZ>=0 AND YCMJ_JZ<60) OR (MJZTLX=1 AND SCMJ_JZ>=0 AND SCMJ_JZ<60) THEN '0-60'
WHEN (MJZTLX=0 AND YCMJ_JZ>=60 AND YCMJ_JZ<80) OR (MJZTLX=1 AND SCMJ_JZ>=60 AND SCMJ_JZ<80) THEN '60-80'
WHEN (MJZTLX=0 AND YCMJ_JZ>=80 AND YCMJ_JZ<90) OR (MJZTLX=1 AND SCMJ_JZ>=80 AND SCMJ_JZ<90) THEN '80-90'
WHEN (MJZTLX=0 AND YCMJ_JZ>=90 AND YCMJ_JZ<120)OR (MJZTLX=1 AND SCMJ_JZ>=90 AND SCMJ_JZ<120)THEN '90-120'
WHEN (MJZTLX=0 AND YCMJ_JZ>=120 AND YCMJ_JZ<144) OR (MJZTLX=1 AND SCMJ_JZ>=120 AND SCMJ_JZ<144 )THEN '120-144'
WHEN (MJZTLX=0 AND YCMJ_JZ>=144 AND YCMJ_JZ<188) OR (MJZTLX=1 AND SCMJ_JZ>=144 AND SCMJ_JZ<188)THEN '144-188'
WHEN (MJZTLX=0 AND YCMJ_JZ>=188) OR (MJZTLX=1 AND SCMJ_JZ>=188) THEN '188'
ELSE NULL END MJFW,
SUM(CASE WHEN CSZT=1 THEN 1 ELSE 0 END)KESHOU,
SUM(CASE WHEN CSZT=2 THEN 1 ELSE 0 END)YISHOU
FROM house.house GROUP BY
CASE
WHEN (MJZTLX=0 AND YCMJ_JZ>=0 AND YCMJ_JZ<60) OR (MJZTLX=1 AND SCMJ_JZ>=0 AND SCMJ_JZ<60) THEN '0-60'
WHEN (MJZTLX=0 AND YCMJ_JZ>=60 AND YCMJ_JZ<80) OR (MJZTLX=1 AND SCMJ_JZ>=60 AND SCMJ_JZ<80) THEN '60-80'
WHEN (MJZTLX=0 AND YCMJ_JZ>=80 AND YCMJ_JZ<90) OR (MJZTLX=1 AND SCMJ_JZ>=80 AND SCMJ_JZ<90) THEN '80-90'
WHEN (MJZTLX=0 AND YCMJ_JZ>=90 AND YCMJ_JZ<120)OR (MJZTLX=1 AND SCMJ_JZ>=90 AND SCMJ_JZ<120)THEN '90-120'
WHEN (MJZTLX=0 AND YCMJ_JZ>=120 AND YCMJ_JZ<144) OR (MJZTLX=1 AND SCMJ_JZ>=120 AND SCMJ_JZ<144 )THEN '120-144'
WHEN (MJZTLX=0 AND YCMJ_JZ>=144 AND YCMJ_JZ<188) OR (MJZTLX=1 AND SCMJ_JZ>=144 AND SCMJ_JZ<188)THEN '144-188'
WHEN (MJZTLX=0 AND YCMJ_JZ>=188) OR (MJZTLX=1 AND SCMJ_JZ>=188) THEN '188'
ELSE NULL END
其他 MJZTLX 为面积状态类型,YCMJ_JZ 为预测面积 SCMJ_JZ 实测面积
记录下,以后作参考。
- 自定义分组 写一个查询
- 如何用自定义标签写一个查询数据库的Table?
- Sql ,sqlserver根据一个字段分组查询
- sql查询语句里面写了自定义函数,速度不是一个慢.蛋疼!!!!
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 分组查询
- 如何写一个自定义标签
- 一个分组查询的月份报表的存储过程
- 1002:方便记忆的电话号码 ACM/百炼
- Java学习日志1.4 Scanner 数据输入的三种方法
- tabbar Item图片原色显示以及标题字体大小颜色控制
- js实现跨域(jsonp, iframe+window.name, iframe+window.domain, iframe+window.postMessage)
- Unable to instantiate application com.android.tools.fd.runtime.BootstrapApplication
- 自定义分组 写一个查询
- javascript 面向对象程序设计 (摘自js高级程序设计)
- Hibernate中多对多关系转换
- c++/c static 用法总结
- Unbuta环境下配置Nexus私服仓库
- iOS 使用图片叠加做动画,类似Tom猫
- MFC/基于对话框的MFC上位机串口通信(C++实现)简单例程
- 移动端媒体查询的一些尺寸参考
- 华为2016暑假实习机试题