sql 求解释 !求解释!
select
e.unit_id,
e.unit_name,
f.temp_id,
f.temp_name,
f.temp_type,
g.pro_id,
g.pro_cn_name + '(' + g.pro_en_name + ')' pro_name,
h.admin_id, h.name,
d.lost,
convert(varchar(10), d.min_data_time, 20) min_data_time
from
(
select a.unit_id, a.min_data_time, COUNT(*) lost
from
(
select unit_id, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(day, -1, MIN(data_time))), 0) min_data_time
from lz_product_unit_value
group by unit_id
) a cross join
(
select distinct DATEADD(wk, DATEDIFF(wk, 0, DATEADD(day, -1, day_time)), 0) day_time
from lz_workday
where work = '1' --1 表示周一到周五的工作日
) b
left join lz_product_unit_value c
on a.unit_id = c.unit_id and b.day_time = DATEADD(wk, DATEDIFF(wk, 0, DATEADD(day, -1, c.data_time)), 0)
where b.day_time >= a.min_data_time
and b.day_time <= DATEADD(wk, DATEDIFF(wk, 0, DATEADD(day, -1, GETDATE())), 0)
and c.unit_id is null
group by a.unit_id, a.min_data_time
) d
join lz_product_unit e on d.unit_id = e.unit_id
join lz_product_template f on e.temp_id = f.temp_id
join lz_product g on f.pro_id = g.pro_id
join lz_admin h on e.admin_id = h.admin_id
where f.temp_type = '1' -- 1表示按照‘周’频率计算