我想在sql中以独特的方式获取数据。 [英] I want data in unique way in sql.

问题描述

我有两张桌子。第一张桌子有两排但不同的产品用于特定发票。

第二个表格为该发票提供借记或贷记凭证的单一条目。





所以我想要从第一行得到两行第二张表中的表格和单个数据行（列）。





InvoiceNum PartNum信用卡借方

154788 CF -000005 0 326

154788 CF-000002





怎么样？



[已添加评论]

First Table

______________________________

 InvoiceNum。 PartNum 
 154788 CF-000005 
 154788 CF-000002 

_____________________

秒表

 ARInvoiceNum。 BookCreditAmount。 BookDebitAmount 
 154788. 0.000。 326.000 

我的尝试：



选择InvoiceNum，PartNum，（选择前1 BookCreditAmount），（选择前1 BookDebitAmount）

来自GLJrnDtl h left join InvcDtl d on h.ARInvoiceNum = d.InvoiceNum

其中h.ARInvoiceNum = 154788和SegValue1 = 46804

解决方案

在进行选择时:(选择top 1 BookCreditAmount），（选择top 1 BookDebitAmount）

你将永远得不到任何东西。没有FROM FROM条款。



因此您的查询应该类似于：

 选择 h.InvoiceNum，h.PartNum，d.BookCreditAmount，d.BookDebitAmount 
 来自 GLJrnDtl  as  h 
  left   join  InvcDtl d   
 h.ARInvoiceNum = d.InvoiceNum 
 其中 h.ARInvoiceNum = 154788 
 和 h.SegValue1 = 46804 

这样就可以了解任何一行> 1也将显示金额。

所以你需要检查Row_NUMBER（）因此查询可能是：

 选择 InvoiceNum，PartNum，BookCreditAmount，BookDebitAmount 
  FROM  
（
  select  ROW_NUMBER（） OVER （ ORDER   BY  h.ARInvoiceNum） AS  ROWNR，
 h.InvoiceNum，h.PartNum，d.BookCreditAmount，d.BookDebitAmount 
 来自 GLJrnDtl  as  h 
  left   join  InvcDtl d  on  h.ARInvoiceNum = d.InvoiceNum 
 
） as  row1 
  WHERE  rownr = 1 
 和 row1.ARInvoice Num = 154788 
 和 row1.SegValue1 = 46804 
  UNION  
 选择 InvoiceNum，PartNum， NULL ， NULL  
  FROM  
（
 选择 ROW_NUMBER（） OVER （ ORDER   BY  h.ARInvoiceNum） AS  ROWNR，
 h.InvoiceNum，h.PartNum，d.BookCreditAmount，d.BookDebitAmount 
  from  GLJrnDtl  as  h 
  left   join  InvcDtl d  on  h.ARInvoiceNum = d.InvoiceNum 
） as  rowOther 
 其中 rownr>  1  和 rowOther.ARInvoiceNum = 154788 
 和 rowOther.SegValue1 = 46804 

您可以尝试以下代码，它应该适合您的预期输出。

选择A.inv，a.partnum，isnull（b.Credit，0）作为'Credit'，isnull（b.debit，0）作为借记
来自（
 SELECT inv， partnum，row_number（）over（按inv命令分区）as row_num 
 FROM＃temp1）
左连接
（SELECT inv，Credit，debit，row_number（）over（partition by inv by order）as row_num 
 FROM＃temp2）B 
 on A.row_num = B.row_num and a.inv = b.inv 
 ORDER BY A.inv 

Hi,


I have two tables.First table has two rows but different products for perticular invoice.
second table has single entry for that invoice for debit or credit entry.


so i want result both rows from first table and single data row(column) from second table.


InvoiceNum PartNum Credit Debit
154788 CF-000005 0 326
154788 CF-000002


how?

[Added from comment]
First Table
______________________________

InvoiceNum.     PartNum
154788	        CF-000005
154788          CF-000002

_____________________
Second Table

ARInvoiceNum.   BookCreditAmount.  BookDebitAmount
154788.         0.000.             326.000

What I have tried:

select InvoiceNum,PartNum,(select top 1 BookCreditAmount ),(select top 1 BookDebitAmount )
from GLJrnDtl h left join InvcDtl d on h.ARInvoiceNum=d.InvoiceNum
where h.ARInvoiceNum=154788 and SegValue1=46804

解决方案

when subselecting: (select top 1 BookCreditAmount ),(select top 1 BookDebitAmount )
You will always get nothing. There is NO FROM clause.

So your query should look something like:

select h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
from GLJrnDtl as h 
left join InvcDtl d on 
h.ARInvoiceNum=d.InvoiceNum
where h.ARInvoiceNum=154788 
and h.SegValue1=46804

That leaves you with the point that any line > 1 will show the amounts too.
So you need to check on Row_NUMBER() thus query might be:

select InvoiceNum, PartNum, BookCreditAmount, BookDebitAmount
FROM 
(
	select ROW_NUMBER() OVER (ORDER BY h.ARInvoiceNum) AS ROWNR,
		h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
	from GLJrnDtl as h 
	left join InvcDtl d on 	h.ARInvoiceNum=d.InvoiceNum
	
) as row1
WHERE rownr=1
and row1.ARInvoiceNum=154788 
	and row1.SegValue1=46804
UNION
select InvoiceNum, PartNum, NULL, NULL
FROM 
(
	select ROW_NUMBER() OVER (ORDER BY h.ARInvoiceNum) AS ROWNR,
		h.InvoiceNum,h.PartNum, d.BookCreditAmount, d.BookDebitAmount 
	from GLJrnDtl as h 
	left join InvcDtl d on 	h.ARInvoiceNum=d.InvoiceNum
) as rowOther
where rownr > 1 and rowOther.ARInvoiceNum=154788 
and rowOther.SegValue1=46804

You can try the below code it should work for your expected output.

select A.inv,a.partnum,isnull(b.Credit,0) as 'Credit',isnull(b.debit,0) as debit 
from(
    SELECT inv,partnum,row_number() over (partition by inv order by inv) as row_num
    FROM #temp1)A
left join
    (SELECT inv,Credit,debit,row_number() over (partition by inv order by inv) as row_num
    FROM #temp2)B
on  A.row_num=B.row_num and a.inv=b.inv
ORDER BY A.inv

这篇关于我想在sql中以独特的方式获取数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！