希望我能正确解释这一点,我的输出当前显示ID和另一个表上每个ID的一堆结果的平均值。它还抓住了这些结果的平均值和板号。
现在,我想做的是设置一个阈值。.因此,只向我展示了哪个id,其中平均是他们应该成为的50%的50%....
这是我当前的代码
select k.ncr_identifier, avg(j.total_mass) as MassTotalAvg, l.TOTAL_COMBINATION_MASS , k.plate_number
From iapm_mass_data j
inner join iapm_data_link k on j.record_id = k.mass_record_id
inner join iacd_ncr_sdtcm_rec l on k.ncr_record_id = l.NCR_ID
Where k.ivu_date >= date '2015-03-01'
and k.ivu_date <= date '2015-03-31'
and l.VEHICLE_CATEGORY_CODE = '12'
group by k.ncr_identifier
我试图像在
中一样把它and l.TOTAL_COMBINATION_MASS < 0.5(avg(j.total_mass))
但似乎不喜欢它,有什么想法吗?
对于该条件,您需要使用关键字。请尝试以下尝试:
select k.ncr_identifier, avg(j.total_mass) as MassTotalAvg, l.TOTAL_COMBINATION_MASS , k.plate_number
From iapm_mass_data j
inner join iapm_data_link k on j.record_id = k.mass_record_id
inner join iacd_ncr_sdtcm_rec l on k.ncr_record_id = l.NCR_ID
Where k.ivu_date >= date '2015-03-01'
and k.ivu_date <= date '2015-03-31'
and l.VEHICLE_CATEGORY_CODE = '12'
group by k.ncr_identifier, l.TOTAL_COMBINATION_MASS , k.plate_number
having l.TOTAL_COMBINATION_MASS < 0.5(avg(j.total_mass))
尝试以下:
select k.ncr_identifier, avg(j.total_mass) as MassTotalAvg, l.TOTAL_COMBINATION_MASS , k.plate_number
From iapm_mass_data j
inner join iapm_data_link k on j.record_id = k.mass_record_id
inner join iacd_ncr_sdtcm_rec l on k.ncr_record_id = l.NCR_ID
Where k.ivu_date >= date '2015-03-01'
and k.ivu_date <= date '2015-03-31'
and l.VEHICLE_CATEGORY_CODE = '12'
group by k.ncr_identifier, l.TOTAL_COMBINATION_MASS , k.plate_number
having l.TOTAL_COMBINATION_MASS < 0.5*avg(j.total_mass)
请注意,您需要在"组"条款中要提到的所有非聚集字段。