sql - 联合结果的 SQL 逻辑。如果表 A 和表 B 中的 ID、日期、时间相同,则使用表 B 和
问题描述
我正在处理一个查询(结果将被导出到另一个供应商)我发现对于同一个 ID,我不能在同一日期和时间拥有 2 种不同的交易类型。如果存在冲突,我需要让逻辑从结果 B 传递 ID、日期、时间、劳动代码、“打卡”类型,并忽略结果 A 的冲突
当前代码:
select
E.idnum,
CONVERT(VARCHAR(10), at.DTTMSTAMP, 111) as 'date',
at.DTTMSTAMP,
'',
'',
CASE when (select Count (*) from ARCHIVES A1, ARCTRANSACTIONS AT1 where at1.ownerid = a1.UNIQUEID and a1.eventdate =a.EVENTDATE and a1.FILEKEY = a.filekey and at.DTTMSTAMP <= at1.DTTMSTAMP)%2 =0 then 'In Punch' Else 'Out Punch' End as 'Type' ,
''
from ARCHIVES A, ARCTRANSACTIONS AT, Employees E
where E.filekey = at.FILEKEY and at.ownerid = a.UNIQUEID and a.eventdate between '2020-06-21' and '2020-06-27'
UNION
select
E.IDNUM,
s.EVENTDATE as 'date',
s.STARTTIME,
'/'+WG5.code+'///'+WG4.CODE+'//'+WG6.CODE,
'',
'Transfer',
''
from WORKGROUPTRANS S, Employees E, WORKGROUP1 wg1, WORKGROUP2 wg2,WORKGROUP3 wg3, WORKGROUP4 wg4, WORKGROUP5 wg5, WORKGROUP6 wg6
where EVENTDATE between '2020-06-21' and '2020-06-27' and e.filekey = s.FILEKEY and wg1.WGNUM = s.WG1 and wg2.WGNUM = s.WG2 and wg3.WGNUM = s.WG3 and wg4.WGNUM = s.WG4 and wg5.WGNUM = s.WG5 and wg6.WGNUM = s.WG6
order by 1,2,3,4
解决方案
这是一个未经测试的查询,可能包含拼写错误:
;with a as (
select
E.idnum,
CONVERT(VARCHAR(10), at.DTTMSTAMP, 111) as 'date',
at.DTTMSTAMP,
'' Code,
'' Col1,
CASE when (select Count (*) from ARCHIVES A1, ARCTRANSACTIONS AT1 where at1.ownerid = a1.UNIQUEID and a1.eventdate =a.EVENTDATE and a1.FILEKEY = a.filekey and at.DTTMSTAMP <= at1.DTTMSTAMP)%2 =0 then 'In Punch' Else 'Out Punch' End as 'Type' ,
'' Col2
from ARCHIVES A, ARCTRANSACTIONS AT, Employees E
where E.filekey = at.FILEKEY and at.ownerid = a.UNIQUEID and a.eventdate between '2020-06-21' and '2020-06-27'
),
b as (
select
E.IDNUM,
s.EVENTDATE as 'date',
s.STARTTIME,
'/'+WG5.code+'///'+WG4.CODE+'//'+WG6.CODE as Code,
'',
'Transfer' as Type,
''
from WORKGROUPTRANS S, Employees E, WORKGROUP1 wg1, WORKGROUP2 wg2,WORKGROUP3 wg3, WORKGROUP4 wg4, WORKGROUP5 wg5, WORKGROUP6 wg6
where EVENTDATE between '2020-06-21' and '2020-06-27' and e.filekey = s.FILEKEY and wg1.WGNUM = s.WG1 and wg2.WGNUM = s.WG2 and wg3.WGNUM = s.WG3 and wg4.WGNUM = s.WG4 and wg5.WGNUM = s.WG5 and wg6.WGNUM = s.WG6
)
select a.idnum, a.date, a.DTTMSTAMP,
isnull(b.Code,'' as )Code, a.Col1,
case when b.Type is not null then 'In Punch' else a.Type end as Type,
a.Col2
from a
left join b on b.idnum=a.idnum and b.STARTTIME=a.DTTMSTAMP
union
select * from b
where not exists(select * from a where a.idnum=b.idnum and a.DTTMSTAMP=b.STARTTIME)
order by 1,2,3,4