留两个SQL省得下次用重写麻烦

来源：互联网发布：磁带备份软件编辑：程序博客网时间：2024/06/18 06:55

1）无位置

select DEV_NO,OPERATION_STATE.NAME,DEVICE_TYPE_L3.TNAME,MSER,MANUFACTURER.CNAME,DEVICE_SERIES.NAME,DEVICE_MODEL.NAME,DEVICE.SPEC from DEVICE,OPERATION_STATE,DEVICE_MODEL,DEVICE_SERIES,MANUFACTURER,DEVICE_TYPE_L3 where DEVICE.CAB_ID is NULL and DEVICE_MODEL.L3ID=DEVICE_TYPE_L3.ID and DEVICE.MODEL=DEVICE_MODEL.ID and DEVICE_MODEL.SID=DEVICE_SERIES.ID and DEVICE_SERIES.MID=MANUFACTURER.ID and DEVICE.OSCODE=OPERATION_STATE.CODE and DEV_NO in ('9902825','9900123','0000498','0104766','0104763','0102635','0102636','0202741','9800797','9800798','9903526','0004105','0002285','0007457','0007459','0007460','0007462','0007463','0204857','0204858','0204859','0204860','0204861','0204862','0204863','0204864','9701840','9701843','9701844','9701639','9801638','9903521','9801122-3','9801122-2','9801122-5','9701681-5','9701681-8','9701681-6','9701681-4','9801122-7','9801122-8')

2）有位置

select DEV_NO,OPERATION_STATE.NAME,CAMPUS.NAME,BUILDING.NAME,ROOM.NAME,DEVICE_TYPE_L3.TNAME,MSER,MANUFACTURER.CNAME,DEVICE_SERIES.NAME,DEVICE_MODEL.NAME,DEVICE.SPEC,DEVICE."REMARK",DEVICE.ZJZ from DEVICE,OPERATION_STATE,DEVICE_MODEL,DEVICE_SERIES,MANUFACTURER,DEVICE_TYPE_L3,CABINET,CAMPUS,BUILDING,ROOM where DEVICE.CAB_ID = CABINET.ID and CABINET.RID = ROOM.ID and ROOM.BID = BUILDING.ID and BUILDING.CID = CAMPUS.ID and DEVICE_MODEL.L3ID=DEVICE_TYPE_L3.ID and DEVICE.MODEL=DEVICE_MODEL.ID and DEVICE_MODEL.SID=DEVICE_SERIES.ID and DEVICE_SERIES.MID=MANUFACTURER.ID and DEVICE.OSCODE=OPERATION_STATE.CODE and DEV_NO in ('9902825','9900123','0000498','0104766','0104763','0102635','0102636','0202741','9800797','9800798','9903526','0004105','0002285','0007457','0007459','0007460','0007462','0007463','0204857','0204858','0204859','0204860','0204861','0204862','0204863','0204864','9701840','9701843','9701844','9701639','9801638','9903521','9801122-3','9801122-2','9801122-5','9701681-5','9701681-8','9701681-6','9701681-4','9801122-7','9801122-8')