mysql - 如何使用 Union all 获得两列结果
问题描述
我需要在结果上获取(Debe 和 Haber)列,我正在使用 Union All 但我总是只得到这两列之一。
这是我的查询:
(SELECT tblfacturas.id AS ID,
tblclientes.razonsocial AS Cliente,
tblfacturas.fecha AS Fecha,
tblfacturas.tipocomprobante AS Tipo,
tblfacturas.nrofacturacompleto AS Numero,
tblfacturas.total AS Debe
FROM tblfacturas
left join tblclientes
ON tblclientes.id = tblfacturas.id_cliente
WHERE tblclientes.razonsocial = 'S.A.')
UNION ALL
(SELECT tblrecibos.id AS ID,
tblclientes.razonsocial AS Cliente,
tblrecibos.fecha AS Fecha,
'R' AS Tipo,
tblrecibos.nrorecibocompleto AS Numero,
tblrecibos.total AS Haber
FROM tblrecibos
left join tblclientes
ON tblclientes.id = tblrecibos.id_cliente
WHERE tblclientes.razonsocial = 'S.A.')
ORDER BY fecha
解决方案
您需要 null 才能在不存在的情况下添加 Debe 和 Haber 列,否则您会在同一列中获得两个结果(Debe)
SELECT TblFacturas.id as ID
, TblClientes.razonsocial as Cliente
,TblFacturas.fecha as Fecha
,TblFacturas.tipocomprobante as Tipo
,TblFacturas.nrofacturacompleto as Numero
, TblFacturas.total as Debe
, null as Haber
FROM TblFacturas
left join TblClientes on TblClientes.id = TblFacturas.id_cliente
where TblClientes.razonsocial = 'S.A.'
UNION ALL
SELECT TblRecibos.id
, TblClientes.razonsocial
,TblRecibos.fecha
, 'R'
,TblRecibos.nrorecibocompleto
, null
, TblRecibos.total
FROM TblRecibos
left join TblClientes on TblClientes.id = TblRecibos.id_cliente
where TblClientes.razonsocial = 'S.A.'
order by fecha
还有另一个建议..您在 where 子句中的左连接中涉及列,如果左连接值不存在,则作为内部连接排除行,您应该
或移动 on 子句中的 where 条件(如果您还需要不是加工行)
SELECT TblRecibos.id
, TblClientes.razonsocial
,TblRecibos.fecha
, 'R'
,TblRecibos.nrorecibocompleto
, null
, TblRecibos.total
FROM TblRecibos
left join TblClientes on TblClientes.id = TblRecibos.id_cliente
AND TblClientes.razonsocial = 'S.A.'
或者如果您只需要匹配的行,请使用内连接
SELECT TblRecibos.id
, TblClientes.razonsocial
,TblRecibos.fecha
, 'R'
,TblRecibos.nrorecibocompleto
, null
, TblRecibos.total
FROM TblRecibos
INNER join TblClientes on TblClientes.id = TblRecibos.id_cliente
AND TblClientes.razonsocial = 'S.A.'
推荐阅读
- java - 变量 id 可能尚未使用 lombok 初始化 Spring Boot Controller
- python - 仅打印查询
- angular - angular form control required 验证器仅在它们是表单输入时才有效
- putty - 使用 PuTTY 的 -m 参数在登录时运行脚本
- python - 将任何经过训练的模型存储在本地机器上,模型不一定是机器学习模型
- r - 如何在r中按组减去多列
- python - 在 Selenium Python 中选择下拉菜单
- c - 找到 BLE 扫描仪设备 0
- shopify - 在 Shopify 中为不同类型的客户发送不同的确认电子邮件(基本)
- android - 在 Flutter 中同时访问多个摄像头