8wDlpd.png
首页
8wDFp9.png
分类
8wDEOx.png
8wDMfH.png
消息
8wDKte.png
我的
  2. sql
  3. 查询 - 同一张表两次

查询 - 同一张表两次

franzageek 1月前
18 0

这是来自 SAP Business One - Hana 数据库，但概念与 SQL 相同。我有一个查询，列出了系统中的所有发票和付款。SELECT T2.\'TotalExpns\' AS \'

这是来自 SAP Business One - Hana 数据库，但概念对于 SQL 是相同的。

我有一个查询，列出了系统中所有的发票和付款。 

SELECT T2."TotalExpns" AS "Total Freight",T2."NumAtCard", T6."LineTotal",T7."SlpName", 
'' AS "CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 
13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", 
T1."ReconSum", '' AS "DocDate", '' AS "CheckNo" FROM OITR T0  
INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum" 
INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry" 
INNER JOIN INV1 T6 ON T2."DocEntry" = T6."DocEntry"
INNER JOIN OSLP T7 ON T2."SlpCode" = T7."SlpCode"
WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C' AND 
T1."SrcObjTyp" = '13'

UNION ALL
SELECT 0 AS "Total Freight", '' AS "NumAtCard",0 AS "LineTotal", '' AS "SlpName", 
T4."CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13 
Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum", 
T3."DocDate", T3."CounterRef" AS "CheckNo" FROM OITR T0  INNER JOIN ITR1 T1 ON 
T0."ReconNum" = T1."ReconNum" INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN ORCT T3 ON T3."DocEntry" = T1."SrcObjAbs"
INNER JOIN OINV T4 ON T2."DocNum" = T4."DocNum"
WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C'  AND 
T1."SrcObjTyp" = '24'
ORDER BY T0."ReconNum"

结果在屏幕截图中，我需要的是类型 24 位于所有类型 13 记录的同一行。系统只将文件日期和支票号码放在第 24 行

任何帮助都将不胜感激。

如果你有 SQL 格式的，我可以将其转换为 Hana

enter image description here

帖子版权声明 1、本帖标题：查询 - 同一张表两次
    本站网址：http://xjnalaquan.com/
2、本网站的资源部分来源于网络，如有侵权，请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点，并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
5、站长邮箱：yeweds@126.com 除非注明，本帖由franzageek在本站《sql》版块原创发布， 转载请注明出处！
收藏的用户（0） X
正在加载信息~
最新回复 (0)
  • 引用 2楼

    我不确定我是否理解了该要求。您可以分享一个您想要获得的结果示例，而不是描述它吗？

  • 引用 3楼

    使用 CTE/子查询而不是联合： 

    ;WITH CHECKDATE
AS
(
SELECT 0 AS "Total Freight", '' AS "NumAtCard",0 AS "LineTotal", '' AS "SlpName", 
T4."CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 13 
Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", T1."ReconSum", 
T3."DocDate", T3."CounterRef" AS "CheckNo" FROM OITR T0  INNER JOIN ITR1 T1 ON 
T0."ReconNum" = T1."ReconNum" INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry"
INNER JOIN ORCT T3 ON T3."DocEntry" = T1."SrcObjAbs"
INNER JOIN OINV T4 ON T2."DocNum" = T4."DocNum"
WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C'  AND 
T1."SrcObjTyp" = '24'
ORDER BY T0."ReconNum"
)

SELECT T2."TotalExpns" AS "Total Freight",T2."NumAtCard", T6."LineTotal",T7."SlpName", 
'' AS "CardName", T0."ReconNum",T1."SrcObjAbs" AS "DocEntry", T1."SrcObjTyp" AS "Type 
13 Inv 24 Incoming Payment", T2."DocNum", T2."FatherCard", T2."CardCode", 
T1."ReconSum", 
CHECKDATE."DocDate",
CHECKDATE."CheckNo"

FROM OITR T0  
INNER JOIN ITR1 T1 ON T0."ReconNum" = T1."ReconNum" 
INNER JOIN OINV T2 ON T1."SrcObjAbs" = T2."DocEntry" 
INNER JOIN INV1 T6 ON T2."DocEntry" = T6."DocEntry"
INNER JOIN OSLP T7 ON T2."SlpCode" = T7."SlpCode"

LEFT JOIN CHECKDATE ON CHECKDATE."ReconNum" = T0."ReconNum"

WHERE T0."ReconNum" ='52837' AND  T0."Canceled" = 'N' AND  T0."IsCard" = 'C' AND 
T1."SrcObjTyp" = '13'
返回
作者最近主题：