UNION类型不匹配

本文介绍了UNION类型不匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当我在查询下面运行时，出现此错误

When I run below query I am getting this error

UNION类型的文本和bigint无法匹配

UNION types text and bigint cannot be matched

SELECT 1 AS STEP , '' AS ProviderName , '' AS Procedurecode , Claimid , Patient_First_Name , Patient_Last_Name , DOS , SUM(COALESCE(Total_Charge,0)) , SUM(COALESCE(PaidAmount,0)) , PostedDate , CheckEFTDate , CheckEFTNo FROM table_name GROUP BY ProviderName, Claimid, Patient_First_Name, Patient_Last_Name, DOS, PostedDate, CheckEFTDate, CheckEFTNo UNION ALL SELECT 2 AS STEP , '' AS ProviderName , '' AS Procedurecode , COUNT(Claimid) , '' AS Patient_First_Name , '' AS Patient_Last_Name , NULL::date AS DOS , SUM(COALESCE(Total_Charge,0)) , SUM(COALESCE(PaidAmount,0)) , NULL::date AS PostedDate , NULL::date AS CheckEFTDate , '' AS CheckEFTNo FROM table_name GROUP BY Claimid

推荐答案

我的错误是，在联合中列名无关紧要，但是顺序却很重要（也许我错了，我找不到文档）

My mistake was is that in union name of columns doesnt matter, but order does matter (maybe I'm wrong, I can't find documentation)

示例：

1）很好

select 1 :: integer as someint, '1' :: text as sometext union select 2 :: integer as someint, '2' :: text as sometext

返回

someint sometext 1 1 1 2 2 2

2）这不是很好

select 1 :: integer as someint, '1' :: text as sometext union select '2' :: text as sometext, 2 :: integer as someint

抛出

Error(s), warning(s): 42804: UNION types integer and text cannot be matched

尝试一下 rextester/l/postgresql_online_compiler