CASE types character varying and numeric cannot be matched
来源:互联网 发布:阿里云最便宜的服务器 编辑:程序博客网 时间:2024/06/06 03:15
问题的描述:
为什么会出现题目当中的CASE type character varying and numeric cannot be matched?
在Postgresql中针对拼接字符串的操作才出现字符串与数字不匹配的概念。
本以为使用concat函数就没问题,但是使用的地方错误造成的原因
下面看一段代码就一目了然了:
EXPLAIN SELECT
se.enroll_number,
s.student_name,
CASE
WHEN pay.pay_method = '支付高校' THEN
0
ELSE
pay.sum_pay
END AS sum_pay,
concat (
'支付高校',
CASE
WHEN pay.pay_method != '支付高校' THEN
''
ELSE
'' || pay.sum_pay
END
) AS remark,
pay.sum_deduct
FROM
(
SELECT
student_id,
pay_method,
SUM (pay_money) AS sum_pay,
SUM (channel_deduct) AS sum_deduct
FROM
payment_detail
WHERE
college_settlement_id = 28
GROUP BY
student_id,
pay_method
) AS pay
LEFT JOIN student AS s ON pay.student_id = s.student_id
LEFT JOIN student_enroll AS se ON pay.student_id = se.student_id
上述这段代码中使用了concat方法,是因为之前在case when的时候在else拼接上的concat方法,造成上述原因,还以为使用的方式不对,虚惊一场。
上述sql算是一个错误的示范,一般情况下不建议这样写,这样太影响性能,拼接的一张表,可以使用union all单步对表数据进行查询(对于上述sql有想法的)可以多提提意见,看看实现的方式有几种?
- CASE types character varying and numeric cannot be matched
- XSD Numeric Data Types
- 第五章 Numeric Types
- 3.4.1 - Numeric Types
- DIMMs must be installed in pairs of matched memory size, speed, and technolo
- Python built-in types - Numeric Types
- Chapter 3: Numeric Types & Operations
- Some characters cannot be mapped using "Cp1252" character encoding 解决方法
- Some characters cannot be mapped using "GBK" character encoding
- Some characters cannot be mapped using "GBK" character encoding.
- Some characters cannot be mapped using "GBK" character encoding.
- Some characters cannot be mapped using "Cp1252" character encoding 解决方法
- some characters cannot be mapped using “Cp1252″ character encoding.
- Some characters cannot be mapped using "GBK" character encoding.
- Some characters cannot be mapped using "GBK" character encoding
- Some characters cannot be mapped using "GBK" character encoding
- java.lang.Character cannot be cast to java.lang.String
- cannot be mapped using "ISO-8859-1" character encoding
- 在 VS2015 中使用 Qt4
- Joystick 虚拟操纵杆
- angular的添加,模糊查询,年龄区间查询,投票
- [状压DP]【NOIP2016D2T3】愤怒的小鸟 题解
- Ambari架构源码解析
- CASE types character varying and numeric cannot be matched
- Angular JS 添加、删除、查询
- 背包九讲(附Cpp代码)
- javadoc生成帮助文档的时候,有哪些命名行开关?
- 被误解的int(10)
- 购物车
- requests获取保存网页代码
- LightOj-1028-Trailing Zeroes (I)
- AS3摄像头拍照保存到本地