将两个sql查询合并为一个查询
问题描述:
如何合并以下两个查询,以便我可以获得两列PAYMODE 和PAYTYPE。两个查询都是相似的,并且是相同的表。将两个sql查询组合成一个查询,以便我不需要执行两个单独的查询。将两个sql查询合并为一个查询
SELECT ETBL_DESC_TXT as PAYMODE
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PMODE'
AND ETBL_VALU_ID = 'RC'
select ETBL_DESC_TXT as PAYTYPE
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PTYPE'
AND ETBL_VALU_ID = 'ER'
答
你总是可以做到这一点
select
(
SELECT ETBL_DESC_TXT
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PMODE'
AND ETBL_VALU_ID = 'RC'
) as PAYMODE,
(
select ETBL_DESC_TXT
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PTYPE'
AND ETBL_VALU_ID = 'ER'
) as PAYTYPE
from SYSIBM.SYSDUMMY1
在SQL Server由于记录出现在不同的,你可以做到这一点(不能在DB2测试,抱歉)
SELECT
max(case when ETBL_VALU_ID = 'RC' and ETBL_TYP_ID = 'PMODE' then ETBL_DESC_TXT else null end) as PAYMODE,
max(case when ETBL_VALU_ID = 'ER' and ETBL_TYP_ID = 'PTYPE' then ETBL_DESC_TXT else null end) as PAYTYPE
FROM tedit
where
CO_ID = 'CP' and
(
(ETBL_VALU_ID = 'RC' and ETBL_TYP_ID = 'PMODE') or
(ETBL_VALU_ID = 'ER' and ETBL_TYP_ID = 'PTYPE')
)
+0
为了清楚起见,当条件具有“OR”时,请始终使用括号。而在DB2中,表引用是_always_必需的(您的第一个查询会引发错误 - 您可以使用'sysibm/sysdummy1')。 –
+0
非常感谢,更新 –
答
源数据的行,我很难在单个结果行中检索它们。您可以在一个查询中得到两行结果。试试这个:
SELECT ETBL_DESC_TXT as PAYFIELD
FROM tedit
WHERE CO_ID = 'CP' AND (
(ETBL_TYP_ID = 'PMODE' AND ETBL_VALU_ID = 'RC')
OR (ETBL_TYP_ID = 'PTYPE' AND ETBL_VALU_ID = 'ER')
)
ORDER BY ETBL_TYP_ID
第一行将包含paymode,第二行将包含paytype。两个查询的
答
SELECT 'PAYMODE' as RowType,ETBL_DESC_TXT as PayValue
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PMODE'
AND ETBL_VALU_ID = 'RC'
union all
select 'PAYTYPE' as RowType, ETBL_DESC_TXT as PayValue
FROM tedit
WHERE CO_ID = 'CP'
AND ETBL_TYP_ID = 'PTYPE'
AND ETBL_VALU_ID = 'ER'
制作UNION –
使用'或':'AND((X = 'PMODE' 和y = 'RC')或(x = 'PTYPE' 和y = 'ER'))' –