SELECT DEVICE,PORT FROM [TABLENAME] WHERE DEVICE NOT IN ( SELECT DEVICE FROM [TABLENAME] WHERE STATUS=1 )
假设table_device(device,port) table_port(port,state) select a.device,b.port from table_device a right join (select port from table_port where state<>'1' ) b on a.port=b.port 这样可以了吧?
SELECT distinct DEVICE,PORT FROM [TABLENAME] WHERE isnull(STATUS,'')<>'1'
FROM [TABLENAME]
WHERE
DEVICE NOT IN
(
SELECT DEVICE FROM [TABLENAME] WHERE STATUS=1
)
table_port(port,state)
select a.device,b.port from table_device a
right join (select port from table_port where state<>'1' ) b
on a.port=b.port
这样可以了吧?
FROM [TABLENAME]
WHERE isnull(STATUS,'')<>'1'