/**
* 获取按年数分类，通用
* @return
*/
private Field getYear() {
Field useTime = DSL.field("YEAR(now()) - YEAR(TBL_ELEVATOR.service_date)");
Field year = DSL.decode()
.when(useTime.between(0, 4), "5年以内")
.when(useTime.between(5, 9), "5年至10年")
.when(useTime.between(10, 14), "10年至15年")
.otherwise("15年以上")
.as("name");
return year;
}
/**
* 按投入使用年限分的电梯总数
* @param condition
* @return
*/
private Table<Record> getYearsTotalCountTable(Map<String, Object> condition) {
Field<String> year = getYear();
SelectJoinStep<Record> record = dsl.select(DSL.count().as("yearsCount"))
.select(year)
.from(TBL_ELEVATOR)
.leftJoin(TBL_USER_DEV)
.on(TBL_ELEVATOR.USER_DEV_ID.eq(TBL_USER_DEV.ID));
return record.groupBy(year)
.asTable("totalCount");
}
/**
* 按投入使用年限分：获取故障数量
* @param condition
* @return
*/
private Table<Record> getYearsDisplayCountTable(Map<String, Object> condition) {
Field<String> year = getYear();
SelectJoinStep<Record> record = dsl.select(
DSL.count().as("malfunctionCount")
)
.select(year)
.from(TBL_WARNING)
.leftJoin(TBL_USER_DEV)
.on(TBL_WARNING.USER_DEV_ID.eq(TBL_USER_DEV.ID))
.leftJoin(TBL_ELEVATOR)
.on(TBL_ELEVATOR.USER_DEV_ID.eq(TBL_USER_DEV.ID));
List<Condition> conditions = getTypeConditions(condition);
return record.where(DSL.and(conditions))
//.and(TBL_WARNING.STATUS.eq(CommonCodeConstants.WARNING_STATUS_CONFIRM))
.groupBy(year)
.asTable("dispalyCount");
}
/**
* 投入使用年限
* @param condition
* @param page
* @return
*/
@SuppressWarnings("unchecked")
public List<MalfunctionStatistics> getUseYearsStatistics(Map<String, Object> condition, Pagination page) {
Table<Record> totalCount = getYearsTotalCountTable(condition);
Field<String> years = (Field<String>) totalCount.field("name");
Field<Integer> yearsCount = (Field<Integer>) totalCount.field("yearsCount");
Table<Record> dispalyCount = getYearsDisplayCountTable(condition);
Field<String> dispalyYears = (Field<String>) dispalyCount.field("name");
Field<Integer> dispalyYearsCount = (Field<Integer>) dispalyCount.field("malfunctionCount");
SelectJoinStep<Record> reacord = dsl
.select(
DSL.decode()
.when(dispalyYearsCount.isNull(), 0)
.otherwise(dispalyYearsCount)
.as("malfunctionCount")
)
.select(
DSL.decode()
.when(yearsCount.isNull(), 0)
.otherwise(yearsCount)
.as("eleCount")
)
.select(years)
.from(totalCount)
.leftJoin(dispalyCount)
.on(years.eq(dispalyYears));
return reacord.groupBy(years,dispalyYearsCount,yearsCount)
.fetch()
.into(MalfunctionStatistics.class);
}
上面的方法转化成sql语句：
select
case when dispalyCount.malfunctionCount is null then 0
else dispalyCount.malfunctionCount
end malfunctionCount,
case when totalCount.yearsCount is null then 0
else totalCount.yearsCount
end eleCount,
totalCount.name
from (
select
count(*) yearsCount,
case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上'
end name
from emsm.tbl_elevator
left outer join emsm.tbl_user_dev
on emsm.tbl_elevator.user_dev_id = emsm.tbl_user_dev.id
group by name
) totalCount
left outer join (
select
count(*) malfunctionCount,
case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上'
end name
from emsm.tbl_warning
left outer join emsm.tbl_user_dev
on emsm.tbl_warning.user_dev_id = emsm.tbl_user_dev.id
left outer join emsm.tbl_elevator
on emsm.tbl_elevator.user_dev_id = emsm.tbl_user_dev.id
where 1 = 1
group by name
) dispalyCount
on totalCount.name = dispalyCount.name
group by
totalCount.name,
dispalyCount.malfunctionCount,
totalCount.yearsCount
会报错
执行失败(语句1)
第42 行附近出现错误[-2111]:
无效的列名[name]
怎么解决，求助各位大佬？
group by 里面不能用到select 里面的别名
改了一下，你试试：
select
case when dispalyCount.malfunctionCount is null then 0
else dispalyCount.malfunctionCount
end malfunctionCount,
case when totalCount.yearsCount is null then 0
else totalCount.yearsCount
end eleCount,
totalCount.name
from (
select
count(*) yearsCount,
case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上'
end name
from emsm.tbl_elevator
left outer join emsm.tbl_user_dev
on emsm.tbl_elevator.user_dev_id = emsm.tbl_user_dev.id
group by case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上' end
) totalCount
left outer join (
select
count(*) malfunctionCount,
case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上'
end name
from emsm.tbl_warning
left outer join emsm.tbl_user_dev
on emsm.tbl_warning.user_dev_id = emsm.tbl_user_dev.id
left outer join emsm.tbl_elevator
on emsm.tbl_elevator.user_dev_id = emsm.tbl_user_dev.id
where 1 = 1
group by case when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 0 and 4 then '5年以内'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 5 and 9 then '5年至10年'
when YEAR(now()) - YEAR(TBL_ELEVATOR.service_date) between 10 and 14 then '10年至15年'
else '15年以上'
end
) dispalyCount
on totalCount.name = dispalyCount.name
group by
totalCount.name,
dispalyCount.malfunctionCount,
totalCount.yearsCount