ORA-00979: not a GROUP BY expression

最新推荐文章于 2025-04-08 16:23:02 发布

qq_28351271

最新推荐文章于 2025-04-08 16:23:02 发布

阅读量358

点赞数

CC 4.0 BY-SA版权

文章标签： ORA-00979 not a GROU

本文链接：https://blog.youkuaiyun.com/qq_28351271/article/details/50247559

本文介绍了一种SQL查询方法，用于获取公租房项目的详细信息，包括项目名称、位置、建筑规模、分配情况及住户类型等。通过内连接和左连接操作，实现了对项目进度、房屋分配状态以及不同收入群体入住情况的综合分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
proj.name,
proj.area,
proj.position,
proj.num AS houseCount,
proj.buildingArea,
proj.planStartDate,
CASE WHEN proj.rate = '100%' THEN TO_CHAR(proj.modifyTime, 'YYYY-MM-DD HH24:MI:SS') ELSE proj.planEndDate END AS builtTime,
CASE WHEN proj.rate = '100%' THEN '已竣工' ELSE '未竣工' END AS built,
'公租房' AS type,
count(h.ID) - count(t.houseID) AS unallotted,
min(t.addTime) allotTime,
count(t.houseID) AS allotted,
sum(t.JTRS) AS amount,
sum(CASE WHEN t.type = 1 THEN 1 ELSE 0 END) AS lowIncome,
sum(CASE WHEN t.type = 2 THEN 1 ELSE 0 END) AS newToWork,
sum(CASE WHEN t.type = 3 THEN 1 ELSE 0 END) AS otherCity,
sum(CASE WHEN t.type = 4 THEN 1 ELSE 0 END) AS unitApply
FROM projectplan proj
INNER JOIN houses h ON h.projectId = proj.id
LEFT JOIN (SELECT a.type, map.addTime, map.houseID, f.JTRS
FROM tenanthousemapping map, tenant t, accessapply a, familyinfo f
WHERE t.ID = map.tenantID AND f.UserId = t.ID AND t.ApplyID = a.id AND map.status = 1) t
ON t.houseID = h.ID
GROUP BY proj.id;