大家来动动脑子，我被搅糊涂了。很着急。 (简单描述)
select distinct y.xh ,y.leibie,y.tag,y.fhid,y.fh,y.sbbh from
(select a.xh,a.fhid,a.fh,b.fhid as fhidb,b.shbbh as sbbh,isnull(b.fy,0) as hj,isnull(b.syds,0) as syds ,isnull(b.byds,0) as byds ,b.bz,isnull(b.psf,0) as psf ,leibie=case when b.fhid is null then null else '水表' end,'W' as tag
from hishtfwtb a,hisshbtb b where a.fhid*=b.fhid and a.rq='200007' and b.rq='200007'union
select a.xh,a.fhid,a.fh,b.fhid as fhidb ,b.dbbh as sbbh,isnull(b.feiyong,0) as hj,isnull(b.syds,0) as syds ,isnull(b.byds,0) as byds ,b.bz,0 as psf ,leibie=case when b.fhid is null then null else '电表' end ,'E' as tag
from hishtfwtb a,hisdbtb b where a.fhid*=b.fhid and a.rq='200007' and b.rq='200007' union
select a.xh,a.fhid,a.fh,b.fhid as fhidb ,b.mqbbh as sbbh,isnull(b.feiyong,0) as hj,isnull(b.syds,0) as syds,isnull(b.byds,0) as byds ,b.bz,0 as psf ,leibie= case when b.fhid is null then null else '煤气表' end ,'M' as tag
from hishtfwtb a,hismqtb b where a.fhid*=b.fhid and a.rq='200007' and b.rq='200007' union
select a.xh,a.fhid,a.fh,b.fhid as fhidb ,b.dhhm as sbbh,isnull(b.hj,0)as hj ,0 as syds,0 as byds,b.bz ,0 as psf,leibie=case when b.fhid is null then null else '电话' end ,'T' as tag
from hishtfwtb a,hisdhtb b where a.fhid*=b.fhid and a.rq='200007' and b.rq='200007') y
order by xh,fhid,tag
如果是这样的结果：
xh leibie tag fhid fh sbbh
----------- ------ ---- ----------- -------------------------------------------------- --------------------
1 NULL E 1 八卦岭二楼234 NULL
1 NULL M 1 八卦岭二楼234 NULL
1 NULL T 1 八卦岭二楼234 NULL
1 NULL W 1 八卦岭二楼234 NULL
1 NULL E 2 八卦岭二楼225 NULL
1 NULL M 2 八卦岭二楼225 NULL
1 NULL T 2 八卦岭二楼225 NULL
1 NULL W 2 八卦岭二楼225 NULL
1 NULL E 3 八卦岭二楼232 NULL
1 NULL M 3 八卦岭二楼232 NULL
1 NULL T 3 八卦岭二楼232 NULL
1 NULL W 3 八卦岭二楼232 NULL
我想得到的结果是
1 NULL E 1 八卦岭二楼234 NULL
1 NULL E 2 八卦岭二楼225 NULL
1 NULL E 3 八卦岭二楼232 NULL
(只要得到房号就可以，序号和tag无没有关系)
如果结果这样：
2 NULL E 4 八卦岭七楼726 NULL
2 NULL M 4 八卦岭七楼726 NULL
2 电话 T 4 八卦岭七楼726 2405085
2 NULL W 4 八卦岭七楼726 NULL
我想得到的结果是：
2 电话 T 4 八卦岭七楼726 2405085
问题点数：0、回复次数：4Top
1 楼orablue（orablue）回复于 2003-12-02 09:56:55 得分 0
我想加一个字段，然后在前台ADOQUERY里面过滤掉，但是就是想不出怎么加这个字段？Top
2 楼orablue（orablue）回复于 2003-12-02 10:55:30 得分 0
1 NULL 1 八卦岭二楼234 NULL
1 NULL 2 八卦岭二楼225 NULL
1 NULL 3 八卦岭二楼232 NULL
2 NULL 4 八卦岭七楼726 NULL
2 电话 4 八卦岭七楼726 2405085
想上面的结果我只要达到这样就可以了：
1 NULL 1 八卦岭二楼234 NULL
1 NULL 2 八卦岭二楼225 NULL
1 NULL 3 八卦岭二楼232 NULL
2 电话 4 八卦岭七楼726 2405085
只要有设备的去掉重复的房号。
Top
3 楼azsoft（Try my best）回复于 2003-12-02 11:00:51 得分 0
not isnull(leibie,'')Top
4 楼orablue（orablue）回复于 2003-12-02 11:07:17 得分 0
azsoft:
不明白你的意思啊Top
