除了 SQL*Plus 中指定的列之外，是否可以 BREAK ON 一列？
这个很难解释，所以我将尝试使用示例来展示我的意思。 请注意，我并不是在询问是否可以在 BREAK 语句中使用多个列 - 我知道可以。
假设我有一个如下所示的查询：
SELECT invoice_no, invoice_date, vendor, account, amount
FROM invoice
ORDER BY vendor, invoice_no, account
假设结果集是：
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 Alpha 1000 125.00
0003 30-JAN-2009 Alpha 3000 33.33
0006 02-FEB-2009 Alpha 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
0002 30-JAN-2009 Charlie 3000 5.00
0004 30-JAN-2009 Charlie 1000 900.50
因此您可以看到一些供应商有多个发票，而一些发票有多个帐户。
要隐藏重复的供应商名称和发票编号，我可以使用 SQL*Plus 的 BREAK 命令如下所示：
BREAK ON vendor ON invoice_no
产生此结果集：
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 1000 125.00
30-JAN-2009 3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
30-JAN-2009 3000 5.00
0004 30-JAN-2009 1000 900.50
到目前为止，一切顺利。 我还想隐藏重复的发票日期，以便仅显示每张发票的第一个日期。 但是，如果我使用此命令：
BREAK ON vendor ON invoice_no ON invoice_date
它会走得太远并隐藏发票 0003 和 0004 的日期，只是因为它们与各自供应商的先前发票相同：
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 1000 125.00
3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
3000 5.00
0004 Charlie 1000 900.50
我真正想要的是像这样的命令语法以下（我创造了术语AND）：
BREAK ON vendor ON invoice_no AND invoice_date
目的是，每当它在invoice_no上中断时，也会在invoice_date上中断（因为我知道一个发票号码不能有两个发票日期） ：
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 1000 125.00
3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
3000 5.00
0004 30-JAN-2009 Charlie 1000 900.50
现在发票 0003 和 0004 的日期已正确显示。
在 SQL*Plus 中是否有任何方法可以完成此操作？
This one's tough to explain, so I'll try to show what I'm after using examples. Please note that I'm not asking if it's possible to use multiple columns in a BREAK statement--I know that it is.
Suppose I have a query like the following:
SELECT invoice_no, invoice_date, vendor, account, amount
FROM invoice
ORDER BY vendor, invoice_no, account
And suppose the result set from that is:
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 Alpha 1000 125.00
0003 30-JAN-2009 Alpha 3000 33.33
0006 02-FEB-2009 Alpha 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
0002 30-JAN-2009 Charlie 3000 5.00
0004 30-JAN-2009 Charlie 1000 900.50
So you can see that some vendors have multiple invoices, and some invoices have multiple accounts.
To hide the duplicate vendor names and invoice numbers, I can use SQL*Plus's BREAK command like so:
BREAK ON vendor ON invoice_no
Which produces this result set:
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 1000 125.00
30-JAN-2009 3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
30-JAN-2009 3000 5.00
0004 30-JAN-2009 1000 900.50
So far, so good. I would also like to hide duplicate invoice dates, so that just the first date for each invoice is shown. However, if I use this command:
BREAK ON vendor ON invoice_no ON invoice_date
It will go too far and hide the date for invoices 0003 and 0004, just because they are the same as the previous invoices from their respective vendors:
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 1000 125.00
3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
3000 5.00
0004 Charlie 1000 900.50
What I'd really like is a command syntax like the following (I made up the term AND):
BREAK ON vendor ON invoice_no AND invoice_date
The intention is that, whenever it breaks on invoice_no, to also break on invoice_date (since I know that a single invoice number can't have two invoice dates):
INVOICE_NO INVOICE_DATE VENDOR ACCOUNT AMOUNT
---------- ------------ ------- ------- ------
0001 30-JAN-2009 Alpha 1000 50.00
0003 30-JAN-2009 1000 125.00
3000 33.33
0006 02-FEB-2009 2000 40.00
0005 31-JAN-2009 Bravo 1000 40.00
0002 30-JAN-2009 Charlie 2000 120.75
3000 5.00
0004 30-JAN-2009 Charlie 1000 900.50
And now the dates are correctly displayed for invoices 0003 and 0004.
Is there any way to accomplish this in SQL*Plus?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你想要这样的东西：
您将
列 DUMMY设置为不打印的 帐户然后在您的选择中将其定义为您需要检查的两个字段的串联。
DUMMY 只是一个任意名称，但通常用于这样的情况，即您需要计算某些内容但不显示它
额外的“
ONvendorONinvoice_no”允许您控制与虚拟休息。 同样，您可以使用DUMMY进行计算以获得总计等。You want something like this:
where you are setting the
column DUMMYas not printedand then defining it in your select as a concatenation of the two fields you need to check.
DUMMY is just an arbitrary name, but common use for cases like this where you need to calculate something but not display it
The extra "
ON vendor ON invoice_no" allow you to control dups on those cols separate from the DUMMY break. Similarly you could compute usingDUMMYto get totals etc.您可以
BREAK ON一个表达式，因此通过将感兴趣的字段转换为字符串并将它们与||连接起来，您应该能够组合一个表达式来让您继续“他们的价值观”。You can
BREAK ONan expression, so by turning the fields of interest into strings and concatenating them with||you should be able to put together an expression that lets you break on "both of their values".SQL*Plus 似乎无法做到这一点。 当发票号码为空时，我最终使用 sed 来删除发票日期：
It doesn't seem like SQL*Plus can do the trick. I ended up using
sedto get rid of the invoice dates whenever the invoice number was blank: