列值在订单后为零
如果order by子句未应用于我的列[pay_date],我正在获取列值。下面是创建表并插入虚拟值作为排雷列值在订单后为零
CREATE TABLE [payment_customer]
(
pay_date datetime ,
customer_name text,
pay_amt Memo,
bal_amt Memo
)
CREATE TABLE [report_invoice]
(
inv_no text,
inv_date datetime ,
pname text,
grand_total Memo
)
Insert Into report_invoice Values
('INV/17-17/0001',#2017-08-08 00:00:00#,'Customer 1',1000)
Insert Into report_invoice Values
('INV/17-17/0002',#2017-08-27 00:00:00#,'Customer 1',300)
Insert Into report_invoice Values
('INV/17-17/0003',#2017-08-27 00:00:00#,'Customer 1',2000)
Insert Into report_invoice Values
('INV/17-17/0004',#2017-08-27 00:00:00#,'Customer 2',500)
Insert Into report_invoice Values
('INV/17-17/0005',#2017-08-28 00:00:00#,'Customer 1',3000)
Insert Into report_invoice Values
('INV/17-17/0006',#2017-08-29 00:00:00#,'Customer 3',700)
Insert Into payment_customer Values
(#2017-08-27 00:00:00#,'Customer 1',500,1500)
Insert Into payment_customer Values
(#2017-08-28 00:00:00#,'Customer 2',200,300)
最终查询
Select * from
(
Select t1.inv_no,t1.inv_date,t1.pname,t1.grand_total , t2.pay_date,t2.customer_name,t2.pay_amt,t2.bal_amt from report_invoice t1
LEFT join payment_customer t2 on t1.inv_date = t2.pay_date and t1.pname = t2.customer_name
UNION ALL
Select t2.inv_no,t2.inv_date,t2.pname,t2.grand_total , t1.pay_date,t1.customer_name,t1.pay_amt,t1.bal_amt from payment_customer t1
LEFT join report_invoice t2 on t1.pay_date = t2.inv_date where t2.inv_date is null
) as v
order by IIF(IsNull(v.inv_date),v.pay_date,v.inv_date)
结果我收到设置相同的情况下查询,但可以看到CUSTOMER_NAME,pay_amt,bal_amt月28日/ 08/2017是但却难免重复 inv_no inv_date PNAME grand_total pay_date CUSTOMER_NAME pay_amt bal_amt
INV/17-18/00001 08/08/2017 Veena Industries Ltd. 238.832
INV/17-18/00002 27/08/2017 Excel Plants 514.95 27/08/2017 Excel Plants & Equipment Pvt. Ltd. 300 214.95
INV/17-18/00003 29/08/2017 I- Tech 400
INV/17-18/00004 28/08/2017 VEENA 514.95 28/08/2017 VEENA 300 214.95
INV/17-18/00005 28/08/2017 VEENA 600
您可能需要指定字段的顺序,并在Access SQL语法使用真正的日期表达式,做使用货币的金额:
pay_date datetime,
customer_name text,
pay_amt Currency,
bal_amt Currency
inv_no text,
inv_date datetime,
pname text,
grand_total Currency
Insert Into report_invoice (inv_no,inv_date,pname,grand_total)
Values ('INV/17-17/0001',#2017-08-08 00:00:00#,'Customer 1',1000)
更新插入查询的建议 –
试试这个
一些修改后Select * from
(
Select t1.inv_no,t1.inv_date,t1.pname,t1.grand_total , t2.pay_date,t2.customer_name,t2.pay_amt,t2.bal_amt from report_invoice t1
LEFT join payment_customer t2 on t1.inv_date = t2.pay_date and cast(t1.pname as varchar(50)) = cast(t2.customer_name as varchar(50))
UNION ALL
Select t2.inv_no,t2.inv_date,t2.pname,t2.grand_total , t1.pay_date,t1.customer_name,t1.pay_amt,t1.bal_amt from payment_customer t1
LEFT join report_invoice t2 on t1.pay_date = t2.inv_date where t2.inv_date is null
) as v
order by IIF(v.inv_date is null,v.pay_date,v.inv_date)
Output -
inv_no inv_date pname grand_total pay_date customer_name pay_amt bal_amt
INV/17-17/0001 2017-08-08 00:00:00.000 Customer 1 1000.00 NULL NULL NULL NULL
INV/17-17/0002 2017-08-27 00:00:00.000 Customer 1 300.00 2017-08-27 00:00:00.000 Customer 1 500.00 1500.00
INV/17-17/0003 2017-08-27 00:00:00.000 Customer 1 2000.00 2017-08-27 00:00:00.000 Customer 1 500.00 1500.00
INV/17-17/0004 2017-08-27 00:00:00.000 Customer 2 500.00 NULL NULL NULL NULL
INV/17-17/0005 2017-08-28 00:00:00.000 Customer 1 3000.00 NULL NULL NULL NULL
INV/17-17/0006 2017-08-29 00:00:00.000 Customer 3 700.00 NULL NULL NULL NULL
编辑
检查它是否是工作或没有
Select v.inv_no,v.inv_date,v.pname,v.grand_total , v.pay_date,v.customer_name,v.pay_amt,v.bal_amt from
(
Select t1.inv_no,t1.inv_date,t1.pname,t1.grand_total , t2.pay_date,t2.customer_name,t2.pay_amt,t2.bal_amt,ROW_NUMBER() over (partition by t2.pay_amt,t2.bal_amt,t1.inv_date order by t1.inv_date) rn from report_invoice t1
LEFT join payment_customer t2 on t1.inv_date = t2.pay_date and cast(t1.pname as varchar(50)) = cast(t2.customer_name as varchar(50))
UNION ALL
Select t2.inv_no,t2.inv_date,t2.pname,t2.grand_total , t1.pay_date,t1.customer_name,t1.pay_amt,t1.bal_amt,ROW_NUMBER() over (partition by t1.pay_amt,t1.bal_amt,t2.inv_date order by t2.inv_date) rn from payment_customer t1
LEFT join report_invoice t2 on t1.pay_date = t2.inv_date where t2.inv_date is null
) as v
where rn = 1
order by IIF(v.inv_date is null,v.pay_date,v.inv_date)
输出
inv_no inv_date pname grand_total pay_date customer_name pay_amt bal_amt
INV/17-17/0001 2017-08-08 00:00:00.000 Customer 1 1000.00 NULL NULL NULL NULL
INV/17-17/0004 2017-08-27 00:00:00.000 Customer 2 500.00 NULL NULL NULL NULL
INV/17-17/0002 2017-08-27 00:00:00.000 Customer 1 300.00 2017-08-27 00:00:00.000 Customer 1 500.00 1500.00
INV/17-17/0005 2017-08-28 00:00:00.000 Customer 1 3000.00 NULL NULL NULL NULL
INV/17-17/0006 2017-08-29 00:00:00.000 Customer 3 700.00 NULL NULL NULL NULL
正如你可以看到2017年8月27日00:00:00.000客户1 500.00 1500.00 快到了两次我希望这种情况出现一次只 –
检查编辑的代码 – Anagha
错误缺少运营商查询表达式RowNumber() –
如果我的理解是正确的,这是你所需要的。
Select distinct inv_no,inv_date,pname.grand_total,pay_date,customer_name,pay_amt,bal_amt from
(
Select t1.inv_no as inv_no,t1.inv_date as inv_date,t1.pname as pname,t1.grand_total as grand_total ,
t2.pay_date as pay_date,t2.customer_name as customer_name,t2.pay_amt as pay_amt,t2.bal_amt as bal_amt from report_invoice t1
LEFT join payment_customer t2 on t1.inv_date = t2.pay_date and t1.pname = t2.customer_name
UNION ALL
Select t2.inv_no,t2.inv_date,t2.pname,t2.grand_total , t1.pay_date,t1.customer_name,t1.pay_amt,t1.bal_amt from payment_customer t1
LEFT join report_invoice t2 on t1.pay_date = t2.inv_date where t2.inv_date is null
) as v
order by IIF(v.inv_date is null,v.pay_date,v.inv_date)
注: ISNULL将有两个参数传递到它。
CASE可跨所有SQL平台移植,而IIF是SQL SERVER 2012+专用的。
错误操作员案件时,v。inv_date为空然后v.pay_date else v.inv_date在MS Access中结束asc –
@RahulDesai现在我已更新query.once检查它 –
相同的错误错误缺少操作员案例,当v.inv_date为空时,然后v.pay_date else v .inv_date结束asc in MS Access –
您如何看待'日期为28/08/2017'是重复记录。请检查第一栏和第三栏两个记录是INV/17-18/00004,514.95和INV/17-18/00005,600哪个是完全不同的。你真的只想为每个单独的日期只显示一条记录,如果是的话,你需要显示哪一条。 –
实际上有两个不同的表,如果条件得到满足,我只想要连接一次如果还满足条件多次,我将需要28/08/2017,VEENA,300,214的行和记录两次 –
我没有详细说明你想告诉你什么。但是,当我加入上面提到的'[payment_customer]'和'[report_invoice]'表格时,需要输出的内容是什么?在上述问题中以文本格式显示输出。 –