我有下面的(简化的)数据库表 A,其中包含母批次纯度值和批次纯度(所有批次均由之前的“母批次”制成。
我试图弄清楚如何通过 SQL 提取批次纯度以及批次纯度与前一个母批次之间的差异。样品名称始终保持不变,但可以有多个批次号
Table A
Sample Name | Purity | Date (DD/MMM/YY)
---------------+-----------+-----------------
Mother Batch | 100 |10-Oct-20
Batch 1 | 96 |11-Oct-20
Batch 2 | 94 |13-Oct-20
Mother Batch | 98 |14-Oct-20
Batch 1 | 94 |16-Oct-20
Many thanks
Bob
使用条件窗口函数:
select t.*,
datediff(date,
max(case when samplename like 'Mother%' then date end) over (order by date)
) as diff
from t;
EDIT:
在 SQL Server 中,您将使用:
datediff(day,
max(case when samplename like 'Mother%' then date end) over (order by date),
date
) as diff
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)