这是我旧问题背后的一个问题,因为不清楚我在寻找什么。老问题可以在这里找到:需要知道这在SQL中是否可能
我有带有报价的销售订单,但是进行了新的报价修订,有时销售订单不会更新。因此,我需要编写一个查询来查找没有最新报价的任何订单。
目前这是在 excel 中完成的,但我希望能够运行查询以获得相同的结果。
MY SQL 或 SQL Server。
表和字段 销售订单行 - 销售订单 ID/当前报价 ID 报价 - 报价编号
销售订单 ID 是一个数字字段 XXXXX。报价 ID 字段类似于 12345,如果创建了一个新修订版,则会添加一个新报价并称为 12345-A,它按每个修订版的字母顺序上升。
示例数据
销售订单
omlSalesOrderID | omlQuoteID
53849 | 12345
31486 | 95648-A
12489 | 68745-D
68732 | 32149-E
引号
qmpQuoteID
12345
12345-A
12345-B
95648
95648-A
95648-B
95648-C
68745
68745-A
68745-B
68745-C
68745-D
32149
32149-A
32149-B
32149-C
32149-D
32149-E
我想看到的是
Sales order ID | Current Quote ID | Latest Quote ID
---------------|------------------|-----------------
53849 | 12345 | 12345-B
31486 | 95648-A | 95648-C
12489 | 68745-D | 68745-D
68732 | 32149-E | 32149-E
在 MySQL 中,您可以使用substring_index()
和聚合:
select o.quoteId, o.salesorderid,
max(q.quote_id)
from orders o left join
quotes q
on o.quoteId = substring_index(q.quoteId, '-', 1)
group by o.quoteId;
在SQL Server(或MySQL(中,您可以使用LIKE
进行比较:
select o.quoteId, o.salesorderid,
max(q.quote_id)
from orders o left join
quotes q
on q.quoteId like concat(o.quoteId, '-%')
group by o.quoteId;
表销售订单行
Select * Into #oml From (
Select '53849' [omlSalesOrderID], '12345' [omlQuoteID] Union All
Select '31486' [omlSalesOrderID], '95648-A' [omlQuoteID] Union All
Select '12489' [omlSalesOrderID], '68745-D' [omlQuoteID] Union All
Select '68732' [omlSalesOrderID], '32149-E' [omlQuoteID]
) A
表引用
Select * Into #qmp From (
Select '12345' [qmpQuoteID] Union All
Select '12345-A' [qmpQuoteID] Union All
Select '12345-B' [qmpQuoteID] Union All
Select '95648' [qmpQuoteID] Union All
Select '95648-A' [qmpQuoteID] Union All
Select '95648-B' [qmpQuoteID] Union All
Select '95648-C' [qmpQuoteID] Union All
Select '68745' [qmpQuoteID] Union All
Select '68745-A' [qmpQuoteID] Union All
Select '68745-B' [qmpQuoteID] Union All
Select '68745-C' [qmpQuoteID] Union All
Select '68745-D' [qmpQuoteID] Union All
Select '32149' [qmpQuoteID] Union All
Select '32149-A' [qmpQuoteID] Union All
Select '32149-B' [qmpQuoteID] Union All
Select '32149-C' [qmpQuoteID] Union All
Select '32149-D' [qmpQuoteID] Union All
Select '32149-E' [qmpQuoteID]
) A
这是语法查询(与您的结果相同(
Select
*,
(Select Top 1 qmpQuoteID From #qmp
where qmpQuoteID like
Case When charindex('-',omlQuoteID) = 0 Then omlQuoteID
Else substring(omlQuoteID,0,charindex('-',omlQuoteID)) End + '%'
order by qmpQuoteID Desc) qmpQuoteID
From #oml
加入并group by omlSalesOrderID, omlQuoteID
:
select
s.omlSalesOrderID `Sales order ID`,
s.omlQuoteID `Current Quote ID`,
max(q.qmpQuoteID) `Latest Quote ID`
from salesorders s left join quotes q
on substring_index(concat(s.omlQuoteID, '-'), '-', 1) = substring_index(concat(q.qmpQuoteID, '-'), '-', 1)
group by s.omlSalesOrderID, s.omlQuoteID
适用于 MySQL。
请参阅演示。
结果:
> Sales order ID | Current Quote ID | Latest Quote ID
> -------------: | :--------------- | :--------------
> 12489 | 68745-D | 68745-D
> 31486 | 95648-A | 95648-C
> 53849 | 12345 | 12345-B
> 68732 | 32149-E | 32149-E
这为您提供了例外的结果
select salesorderID as `Sales order ID`,
salesquoteID as `Current Quote ID`,
latestquoteID as `Latest Quote ID`
from(
select substring_index(q.quoteID,'-',1) as nos,
max(substring_index(q.quoteID,'-',-1)) as alp,
max(q.quoteID) as latestquoteID,
id, salesorderID, s.quoteID as salesquoteID
from salesorder s
left join quotes q
on substring_index(s.quoteID,'-',1) = substring_index(q.quoteID,'-',1)
group by nos, s.id
) as sales
order by id asc;
http://sqlfiddle.com/#!9/28a85a/1
https://www.db-fiddle.com/f/pavgTVPfHgrWP4Ph5G7rif/4