考虑具有以下详细信息的表Tax_Details。在此表中,每个Tax_code应该具有唯一的频率
[Tax_Code] [Frequency]
A-001
A-002
A-003
B-001
C-001
考虑另一个具有以下详细信息的表Freq-Details。在此表中,Tax_code可能具有多个频率
[Tax_Code] [Frequency]
A-001 Weekly
A-001 Bi-Weekly
A-001 Daily
A-002 Daily
A-002 Monthly
A-003 Bi-Weekly
A-003 Monthly
B-001 Daily
B-001 Monthly
C-001 Monthly
考虑带有详细信息的第三表Frequency_Sequence。在此表中,每个频率都有一定的顺序。
[Frequency] [Sequence]
Daily 1
Weekly 2
Bi-Weekly 3
Monthly 4
我想用Freq-Details表中的Frequency更新Tax_Details表中的[Frequency]列,并且频率序列应该最小。我的输出应该是这样的
[Tax_Code] [Frequency]
A-001 Daily
A-002 Daily
A-003 Bi-Weekly
B-001 Daily
C-001 Monthly
请帮助获得此。提前致谢。
您可以使用条件聚合返回所需的结果:
select fd.tax_code,
max(case when seqnum = 1 then fd.frequency end) as frequency
from (select fd.tax_code, fd.frequency,
row_number() over (partition by fd.tax_code order by fs.sequence) as seqnum
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
) fd
group by fd.tax_code;
您可以在更新中表示为:
update tax_details
set frequency = (select fd.frequency
from freq_details fd join
frequency_sequence fs
on fd.frequency = fs.frequency
where fd.tax_code = tax_details.tax_code
order by fs.sequence
fetch first 1 row only
);
这两个都是标准SQL,并且可以在大多数数据库中使用。但是,语法在任何给定的数据库中可能略有不同。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句