时间戳

首页 > 解决方案 > 查询以获取所有未全额支付的发票

sql-server - 查询以获取所有未全额支付的发票

问题描述

我有这些表，，，，invoices该表包含创建合同时用户应支付的所有发票，该合同可能有 1 张或更多发票，付款表包含合同的所有付款（用户可能支付payments更多每张发票的付款），最后一个表格在表格 EG 中有每笔付款的详细信息，付款可能有不同的付款方式，例如现金、现金和签证，或现金和签证和支票。我通过从 payment_details 获取付款方式值的总和来获得付款值，这是我的表格脚本：payments_detailsinvoicespayments_detailspayments

CREATE TABLE [dbo].[invoices](
[ID] [int] IDENTITY(1,1) NOT NULL,
[con_id] [int] NOT NULL,
[value] [decimal](10, 2) NOT NULL,
[due_date] [date] NULL,
[date] [date] NULL,
 CONSTRAINT [PK_due_payments] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payments]    Script Date: 30/04/2021 09:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payments](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [inv_id] [int] NULL,
    [note] [nvarchar](max) NULL,
    [date] [date] NULL,
    [app_user] [nvarchar](20) NULL,
 CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payments_details]    Script Date: 30/04/2021 09:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payments_details](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [pay_id] [int] NOT NULL,
    [value] [decimal](10, 2) NULL,
    [method] [nvarchar](20) NULL,
    [date] [date] NULL,
 CONSTRAINT [PK_Payment_yearly_details] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

这是数据库的表图： 在此处输入图像描述

我想获得所有invoices未全额付款的发票payments。

这是我在测试中所做的：

select inv.ID,inv.con_id,inv.due_date 
from invoices inv
cross apply(
select inv_id from payments pay) payy
cross apply(
select coalesce(sum(pd.value), 0) as paid
from payments_details pd group by pd.pay_id) pdd
where inv.ID = payy.inv_id and inv.value != pdd.paid  group by inv.ID, inv.con_id,inv.due_date

标签： sql-servertsql

解决方案

试试这个：

SELECT inv.ID, inv.con_id, inv.due_date 
FROM invoices inv
LEFT OUTER JOIN payments pay ON pay.inv_id = inv.ID
LEFT OUTER JOIN payments_details pd ON pd.pay_id = pay.ID
GROUP BY inv.ID, inv.con_id, inv.due_date, inv.value
HAVING SUM(ISNULL(pd.value, 0)) < inv.value

推荐阅读