稍复杂的查询语句，求未达标人数及与享受面积差

Question

表1 字段：档案编号（dangan_number），享受面积(xsmj)，职工姓名，配偶姓名
表2字段：档案编号（dangan_number），房屋地址，房屋面积（fwmj），房屋性质fwxz（集资房、商品房），职工姓名，配偶姓名，房屋产权人表1中每个职工档案编号唯一，也就是档案编号是唯一的，对于表2来说，由于一个职工可能有多套住房，表2中的档案编号可能是多条，房屋地址及对应的住房面积和房屋性质也就可能是一条或是多条，无房的职工表2是0条数据，这里面如果配偶有房，也就是产权人是配偶，也算是职工的房屋，也就在表2中加一条记录。我在我认为能用到的字段起了名字现在求的是：住房未达标的人数 和 与享受面积差，未达标指的是该人首先有房子（产权性质是集资房才算有房），但该房屋面积小于该人住房享受面积，与享受面积差指的是所有未达标的职工总的享受面积和与他们现有集资房面积总和的差。
享受面积(xsmj)

Answer 1

集资房每个档案的使用面积和：
select sum(shiyong_mianji) from house_info_table 
where chanquan_xingzhi='集资购房'group by dangan_number having sum(shiyong_mianji)
每个集资房职工的享受面积：
select xsmj from emp_info_table where dangan_number 
in(select distinct dangan_number from house_info_table where chanquan_xingzhi='集资购房')
对比这组数据似乎能得出结果，不过做到这我就不会了

Answer 2

试试：
1、
select count(a.dangan_number) 住房未达标的人数 from emp_info_table a 
where xsmj>(select sum(fwmj) from house_info_table where fwxz='集资购房' where a.dangan_number=dangan_number group by dangan_number )2、
select t1.sum_xsmj-t2.sum_fwmj from 
(elect sum(a.xsmj) sum_xsmj from emp_info_table a 
where xsmj>(select sum(fwmj) from house_info_table where fwxz='集资购房' where a.dangan_number=dangan_number group by dangan_number )
) t1 ,
(select sum(fwmj) sum_fwmj from house_info_table where fwxz='集资购房') t2

Answer 3

不好意思，没来得及回帖，我按你的方法把结果弄出来了，语句如下；
1.
SELECT COUNT(a.dangan_number) 
FROM EMP_INFO_TABLE a  
WHERE a.xsmj<(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
WHERE (b.chanquan_xingzhi='集资购房' OR  b.chanquan_xingzhi='房改购房' OR  b.chanquan_xingzhi='承租住房') AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )2.
SELECT t1.sum_xsmj-t2.sum_fwmj FROM  
(SELECT SUM(a.xsmj) sum_xsmj
FROM EMP_INFO_TABLE a  
WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b 
WHERE (b.chanquan_xingzhi='集资购房' OR  b.chanquan_xingzhi='房改购房' OR  b.chanquan_xingzhi='承租住房') AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )
) t1 ,
(SELECT SUM(shiyong_mianji) sum_fwmj FROM HOUSE_INFO_TABLE  WHERE
dangan_number IN (SELECT a.dangan_number
FROM EMP_INFO_TABLE a  
WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
WHERE (b.chanquan_xingzhi='集资购房' OR  b.chanquan_xingzhi='房改购房'OR  b.chanquan_xingzhi='承租住房') AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number ) ) 
) t2
但是我希望出的结果与此还有差别，就是有个按单位进行汇总，每一个结果都是一个单位，在emp_info_table中还有工作单位（gzdw）字段，另外还有一个单位字典（表名是dic_dept）
我已经把第一个结果按单位汇总写出来了,如下：
未达标职工人数按单位分类
SELECT ROWNUM,t.* FROM (
SELECT b.deptname,COUNT(a.gzdw) FROM EMP_INFO_TABLE a,DIC_DEPT b 
WHERE b.deptname=a.gzdw(+) AND a.dangan_number IN(SELECT a.dangan_number 
FROM EMP_INFO_TABLE a  
WHERE a.xsmj<(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
WHERE (b.chanquan_xingzhi='集资购房' OR  b.chanquan_xingzhi='房改购房' OR  b.chanquan_xingzhi='承租住房') AND 
a.dangan_number=b.dangan_number GROUP BY b.dangan_number ))
GROUP BY b.deptname )t
但是对于将未达标职工与享受面积差按单位分类没写出来，希望 gelyon及其他高手再给点思路。

Answer 4

上面还不是我想要的结果，结果是这样：
序号   单位  未达标人数 未达标与享受面积差
1     A单位   14          334
2     B单位    0           0
3     C单位    1           13————————————————————
并且以上结果并没有统计出未达标人数为0的单位，如B单位，如果将
WHERE b.deptname=a.gzdw(+) AND a.dangan_number IN( 
改为WHERE b.deptname=a.gzdw(+) AND a.dangan_number(+) IN(
提示错误：ORA-01799: 列不可以外部连接到子查询  ,请问各位高手该怎么实现我想要的结果。

Answer 5

--排了下版，不知道满足你的了不
1、
SELECT ROWNUM,t.* FROM (
   SELECT b.deptname,Nvl(COUNT(a.gzdw),0) FROM EMP_INFO_TABLE a,DIC_DEPT b  
   WHERE b.deptname=a.gzdw(+) AND a.dangan_number IN(
             SELECT a.dangan_number  FROM EMP_INFO_TABLE a   
             WHERE a.xsmj<(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
                           WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房' OR b.chanquan_xingzhi='承租住房') 
                           AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )
             )
  GROUP BY b.deptname )t2、
SELECT b.deptname,Nvl(t3.chaji,0) FROM DIC_DEPT b ,(
     SELECT t1.gzdw,(t1.sum_xsmj-t2.sum_fwmj) chaji FROM   
             (SELECT a.gzdw,SUM(a.xsmj) sum_xsmj FROM EMP_INFO_TABLE a   
              WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b  
                            WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房' OR b.chanquan_xingzhi='承租住房') 
                            AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number ) 
              GROUP BY a.gzdw
              ) t1 ,
              (SELECT gzdw,SUM(shiyong_mianji) sum_fwmj FROM HOUSE_INFO_TABLE 
               WHERE dangan_number IN (SELECT a.dangan_number FROM EMP_INFO_TABLE a   
                                       WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
                                                     WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房'OR b.chanquan_xingzhi='承租住房') 
                                                     AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )
                                       ) 
               GROUP BY gzdw ) t2
     WHERE t1.gzdw=t2.gzdw
) t3
WHERE  b.deptname=t3.gzdw(+)

Answer 6

可能有点错，HOUSE_INFO_TABLE 表中没有工作单位gzdw字段是不是？那么我这个表依据什么来区分不同单位的呢？是dangan_number跟EMP_INFO_TABLE中的dangan_number再关联起来判断归属单位的是不？
这样还要嵌套一层，想想。下班了我，晚上回去再给你看看你先改下

Answer 7

HOUSE_INFO_TABLE 表中没有工作单位gzdw，EMP_INFO_TABLE表中有工作单位gzdw，我也觉得只能用档案编号关联，顺便说下，档案编号是由该职工所属单位的单位编号+随机生成的5位数组成，单位编号在单位字典（dic_dept）中和单位一一对应。

Answer 8

后面你提出的要根据单位区分，你应该早点提出来啊，最后整的麻烦的很
这个是最后修改的，应该可以了，第2个问题，不清楚是否house_info_table中表结构，
最好给出你的表结构：
家里没法排版，你看看可以了不，不行明天再说。1、
SELECT ROWNUM,t.* FROM (
   SELECT d.deptname,Nvl(c.cnt,0) FROM DIC_DEPT d,    
            (SELECT a.gzdw,coumt(1) cnt FROM EMP_INFO_TABLE a   
             WHERE a.xsmj<(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
                           WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房' OR b.chanquan_xingzhi='承租住房') 
                           AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number 
                           )
             GROUP BY a.gzdw
             ) c
   WHERE d.deptname=c.gzdw(+)
)t
2、假设你house_info_table中表中有工作单位字段gzdwSELECT b.deptname,Nvl(t3.chaji,0) FROM DIC_DEPT b ,(
     SELECT t1.gzdw,(t1.sum_xsmj-t2.sum_fwmj) chaji FROM   
             (SELECT a.gzdw,SUM(a.xsmj) sum_xsmj FROM EMP_INFO_TABLE a   
              WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b  
                            WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房' OR b.chanquan_xingzhi='承租住房') 
                            AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number ) 
              GROUP BY a.gzdw
              ) t1 ,
              (SELECT h.gzdw,Sum(h.shiyong_mianji)sum_fwmj FROM HOUSE_INFO_TABLE h
               WHERE EXISTS(SELECT 1 FROM EMP_INFO_TABLE a   
                            WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
                                          WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房'OR b.chanquan_xingzhi='承租住房') 
                                          AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )
                            AND h.dangan_number =a.dangan_number ) 
               GROUP BY h.gzdw
               ) t2
     WHERE t1.gzdw =t2.gzdw
) t3
WHERE  b.deptname=t3.gzdw(+)

Answer 9

哦 那你把我12 楼中 t2 这个子结果这样改：
(SELECT substr(h.dangan_number,1,length(h.dangan_number)-5) gzdw,Sum(h.shiyong_mianji)sum_fwmj FROM HOUSE_INFO_TABLE h
  WHERE EXISTS(SELECT 1 FROM EMP_INFO_TABLE a   
  WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
  WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房'OR b.chanquan_xingzhi='承租住房')  
  AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number )
  AND h.dangan_number =a.dangan_number )  
  GROUP BY substr(h.dangan_number,1,length(h.dangan_number)-5)
  ) t2

Answer 10

辛苦gelyon,我明白你的意思，可是住房信息表HOUSE_INFO_TABLE没有工作单位，即使有职工的工作单位没有用，因为配偶的房子也算在职工的统计范围内，一个档案编号可能既有产权人为职工的住房也有产权人为配偶的住房。

Answer 11

根据档案编号这样进行取值substr(h.dangan_number,1,length(h.dangan_number)-5)
不行吗？
为什么？

Answer 12

不好意思，你在13楼和15楼的回复我当时没看见，你可真是高手，我已经按照你的思路把问题都解决了
语句如下：
SELECT ROWNUM,t.* FROM (
    SELECT DISTINCT(b.deptname),NVL(t3.chaji,0) FROM DIC_DEPT b ,
        (
          SELECT t1.sub_dangan_number ,(t1.sum_xsmj-t2.sum_fwmj) chaji FROM   
            (
             SELECT SUBSTR(a.dangan_number,1,LENGTH(a.dangan_number)-5) sub_dangan_number,SUM(a.xsmj) sum_xsmj FROM EMP_INFO_TABLE a   
              WHERE a.xsmj>
                  (SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b   
                   WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房' OR b.chanquan_xingzhi='承租住房')  
                   AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number
                   )  
             GROUP BY SUBSTR(a.dangan_number,1,LENGTH(a.dangan_number)-5)
             ) t1 ,
        (
            SELECT SUBSTR(h.dangan_number,1,LENGTH(h.dangan_number)-5) gzdw,SUM(h.shiyong_mianji)sum_fwmj FROM HOUSE_INFO_TABLE h
               WHERE EXISTS(SELECT 1 FROM EMP_INFO_TABLE a   
                             WHERE a.xsmj>(SELECT SUM(b.shiyong_mianji) FROM HOUSE_INFO_TABLE b
                               WHERE (b.chanquan_xingzhi='集资购房' OR b.chanquan_xingzhi='房改购房'OR b.chanquan_xingzhi='承租住房')   
                                   AND a.dangan_number=b.dangan_number GROUP BY b.dangan_number 
                             )
                          AND h.dangan_number =a.dangan_number )   
            GROUP BY SUBSTR(h.dangan_number,1,LENGTH(h.dangan_number)-5)
          ) t2
          WHERE t1.sub_dangan_number =t2.gzdw
      ) 
    t3
   WHERE b.deptcode=t3.sub_dangan_number(+)
 )t ————————————
再次表示感谢！