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

最新推荐文章于 2025-07-30 12:10:13 发布

sonicatnoc

最新推荐文章于 2025-07-30 12:10:13 发布

阅读量651

点赞数

CC 4.0 BY-SA版权

文章标签： sql null

本文链接：https://blog.youkuaiyun.com/sonicatnoc/article/details/4282344

本文提供两个SQL查询示例，分别用于检索无位置信息和有位置信息的设备详情，包括设备型号、制造商等属性。适用于数据库管理和维护场景。

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

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')