mysql - sql insert into, select, where 语句

Question

我想将 hk_room 表中的所有行复制到 hk_history 中，除了 rStatus = '-' OR rStatus='Long Stay' 和 rStatus = 'Check Out' 的行。

'-' 是 rStatus 属性的默认值。

我试过这两个查询:

INSERT INTO hk_history
(rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate)
SELECT rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate
FROM hk_room1;
WHERE rStatus <> '-';

或

INSERT INTO hk_history
(rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate)
SELECT rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate
FROM hk_room1;
WHERE rStatus = 'Long Stay' AND rStatus = 'Check Out';

but I got this error

Answer 1

在第一个查询的 where 之前有一个分号:

INSERT INTO hk_history
    (rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate)
    SELECT rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate
    FROM hk_room1;
    -------------^
    WHERE rStatus <> '-';

你需要删除它。

要实现这一点:

want to copy all row from hk_room table into hk_history except row with rStatus = '-' OR rStatus='Long Stay' and rStatus = 'Check Out'.

只需使用一条语句:

INSERT INTO hk_history
    (rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate)
    SELECT rNo,rStatus,bs,bq,hk,ds,dq,pc,twl,fm,amt,db,mw,hkR,fo_R,svR,rDate
    FROM hk_room1
    WHERE NOT (rStatus = '-' OR 
               rStatus = 'Long Stay' and rStatus = 'Check Out'
              );

您也可以将其表述为:

    WHERE rStatus <> '-' AND
          (rStatus <> 'Long Stay' OR rStatus <> 'Check Out')

但是，以前的版本似乎更清楚您的意图。