sql查询语句如下:
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0')AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60');请问如何添加查询条件,使到当`client_sn` = '102FSDE0'时,`goods_sn`字段中含有'008BJ000'值的记录不被筛选出来。用if语句可行吗? sql如何定?请指教!
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0')AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60');请问如何添加查询条件,使到当`client_sn` = '102FSDE0'时,`goods_sn`字段中含有'008BJ000'值的记录不被筛选出来。用if语句可行吗? sql如何定?请指教!
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0') AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60') AND (`client_sn`,`goods_sn`) NOT IN (SELECT `client_sn`,`goods_sn` FROM `sale` WHERE `client_sn`='102FSDE0' AND `goods_sn`='008BJOOO')
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0') AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60') AND (`client_sn`,`goods_sn`) NOT IN (SELECT `client_sn`,`goods_sn` FROM `sale` WHERE `client_sn`='102FSDE0' AND `goods_sn`='008BJOOO')
(因为条件goods_sn存的是肯定项,而不是否定项)
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0')AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60')
后面的条件用 and 或 and not 都不对
`client_sn` = '102FSDE0' and `goods_sn` in ('203HMJ01', '303SXZ06', '404PTT60')
SELECT * FROM sale WHERE (client_sn ='102FSDE0' AND goods_sn IN ('203HMJ01', '303SXZ06', '404PTT60')) or (client_sn ='102FSFY0' AND goods_sn IN ('203HMJ01', '303SXZ06', '008BJ000','404PTT60'));
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0')AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60');请问如何添加查询条件,使到当`client_sn` = '102FSDE0'时,`goods_sn`字段中含有'008BJ000'值的记录不被筛选出来。所以我回答
... and not (`client_sn`='102FSDE0' and `goods_sn`='008BJ000')
不过是省略了你原始的 sql 指令,因为你说是“添加”
完整的写法是
SELECT * FROM `sale` WHERE `client_sn` IN ('102FSDE0', '102FSFY0')AND `goods_sn` IN ('203HMJ01', '303SXZ06', '008BJ000', '404PTT60') and not (`client_sn`='102FSDE0' and `goods_sn`='008BJ000')
;
当`client_sn`='102FSDE0'时,goods_sn包含('203HMJ01', '303SXZ06', '404PTT60')才被返回
... where `client_sn` = '102FSDE0' and `goods_sn` in ('203HMJ01', '303SXZ06', '404PTT60')
吗?