Sql查询显示没有产品交付的发票表 [英] Sql query to display invoice table where no products delivered
问题描述
大家好,
我有两张桌子如下:
INV(发票标题表)包含:
INV号码,名称
1,Micheal
2,Josh
发票项目表:
INV号码,项目,数量,交付数量
1,PEN,10,0
1,PENCIL,10,1
2,PEN,10,0
现在我需要的是显示没有交付产品的发票(发票标题)。
如果发票内有一件物品,我不想看到那张发票。
只有所有物品都是发票的发票没有交付。
上表的查询应该给我:
2,Josh
如何为此编写SQL查询。
感谢您的帮助。
Hadi
我的尝试:
很多,但无法找到解决方案
尝试:
SELECT a。[Inv Number],a。[Name] FROM INV a
JOIN ( SELECT [INV NUMBER],SUM([已交货数量])< span class =code-keyword> AS SumDQ
FROM 项目
GROUP BY [INV NUMBER])b
ON a。[Inv Number] = b。[Inv Number]
WHERE b.SumDQ = 0
- 降温表格如果已经存在
IF OBJECT_ID (' tempdb .. #InvHeader') IS NOT NULL
DROP 表 #InvHeader
IF OBJECT_ID (' tempdb ..#InvItems') IS NOT NULL
DROP TABLE #InIItems
- 创建临时表
CREATE TABLE #InvHeader
(
I nvNumber INT ,
[名称] NVARCHAR ( 50 )
)
CREATE 表# InvItems
(
InvNumber INT ,
Item NVARCHAR ( 50 ),
数量 INT ,
已发送 INT
)
- 填充临时表您的示例数据
INSERT INTO #InvHeader(InvNumber,[Name])
VALUES ( 1 ,' Michael'),( 2 , ' Josh')
INSERT INTO #InIItems(InvNumber,Item,Quantity,Delivered)
VALUES ( 1 ,' 笔', 10 , 0 ),( 1 ,' Pencil', 10 , 1 ), ( 2 ,' Pen',< span class =code-digit> 10 , 0 )
- - 您的查询中不需要此评论上方的所有内容。它
- 只是包含在这里,所以你可以测试我做了什么
- 这是您感兴趣的代码。如果您对此感到满意,替换
- 适当的表格和列名。
- 创建一个公用表表达式来对项目进行分组,以便每个发票
- 数字只有一个交付值代表总项目
- 已交付
; WITH cte AS
(
SELECT invnumber,SUM(Delivered) AS totalDelivered
FROM #InIItems
GROUP < span class =code-keyword> BY invnumber
)
- 从标题表中选择发票编号和名称
SELECT h.InvNumber,
h。[名称]
FROM #InvHeader AS h
- 并加入tpo公用表表达式
JOIN cte AS i
- 但仅限于发票编号匹配和总数交付值为0
ON i.invnumber = h.InvNumber AND i .TotalDelivered = 0 跨度>
Hi all,
I have two tables as following :
INV (invoice header table) contains :
INV NUMBER , NAME
1 , Micheal
2 , Josh
Invoice items tables :
INV NUMBER , ITEM , QUANTITY , DELIVERED QUANTITY
1 , PEN , 10 , 0
1 , PENCIL , 10 , 1
2 , PEN , 10 , 0
Now what i need is display the invoices (invoice header) where there are no delivered products .
If there are one item delivered within the invoice , i don't want to see that invoice .
Only invoices where all items were not delivered .
The query for the above tables shall give me the :
2 , Josh
How to write the SQL query for that .
Thanks for helping .
Hadi
What I have tried:
A lot , but wasn't able to find the solution
Try:
SELECT a.[Inv Number], a.[Name] FROM INV a JOIN (SELECT [INV NUMBER], SUM([Delivered Quantity]) AS SumDQ FROM Items GROUP BY [INV NUMBER]) b ON a.[Inv Number] = b.[Inv Number] WHERE b.SumDQ = 0
-- drop the temp tables if they already exist IF OBJECT_ID('tempdb..#InvHeader') IS NOT NULL DROP TABLE #InvHeader IF OBJECT_ID('tempdb..#InvItems') IS NOT NULL DROP TABLE #InvItems -- create the temp tables CREATE TABLE #InvHeader ( InvNumber INT, [Name] NVARCHAR(50) ) CREATE TABLE #InvItems ( InvNumber INT, Item NVARCHAR(50), Quantity INT, Delivered INT ) -- populate the temp tables with your sample data INSERT INTO #InvHeader ( InvNumber, [Name]) VALUES (1, 'Michael'), (2, 'Josh') INSERT INTO #InvItems (InvNumber,Item,Quantity,Delivered) VALUES (1, 'Pen', 10, 0), (1, 'Pencil', 10, 1), (2, 'Pen', 10, 0) -- everything above this comment will not be needed in your query. It is -- simply included here so you could test what I did -- here's the code you're interested in. If you're happy with it, substitute -- your table and column names where appropriate. -- create a common table expression to group the items so that each invoice -- number has exactly one delivered value that represents the total items -- delivered ;WITH cte AS ( SELECT invnumber, SUM(Delivered) AS totalDelivered FROM #InvItems GROUP BY invnumber ) -- select the invoice number and name from the header table SELECT h.InvNumber, h.[Name] FROM #InvHeader AS h -- and join tpo the common table expression JOIN cte AS i -- but only in the invoice number matches and the total delivered value is 0 ON i.invnumber = h.InvNumber AND i.TotalDelivered = 0
这篇关于Sql查询显示没有产品交付的发票表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!