MySQL 许多“和”；声明

Question

我有这样的语句，这是错误的方法，但是我如何让该语句发挥作用（该语句的结果需要包含其中的所有 AND ）

SELECT a.username, a.first_name, a.last_name
     , b.tx_time, b.account_id
     , a.id 
     , b.table_id, b.tx_type, b.amount 
FROM punter a
   , account_transaction b 
WHERE b.tx_time >= '2011-07-01' 
  AND b.tx_time < '2011-09-30' 
  AND b.account_id = a.id 
  AND b.tx_type = 4 
  AND b.tx_type = 14

Answer 1

AND b.tx_type = 4 AND b.tx_type = 14 将为您提供空结果集。一列同时只能有一个值。

SELECT a.username, a.first_name, a.last_name, 
       b.tx_time, b.account_id, a.id,
       b.table_id, b.tx_type, b.amount 
FROM punter a 
INNER JOIN account_transaction b ON b.account_id = a.id
WHERE b.tx_time BETWEEN '2011-07-01' AND '2011-09-30' AND b.tx_type IN (4,14) 

Answer 2

您可以使用显式 JOIN 来简化一点：

SELECT a.username, a.first_name, a.last_name, 
       b.tx_time, b.account_id, a.id,
       b.table_id, b.tx_type, b.amount 
FROM punter a INNER JOIN account_transaction b 
    ON b.account_id = a.id
WHERE b.tx_time BETWEEN '2011-07-01' AND '2011-09-30' 
  AND b.tx_type = 4 
  AND b.tx_type = 14

请注意，我认为使用 b.tx_type = 4 AND b.tx_type = 14 是错误的，因为它始终为 false。如果您需要获取具有 tx_type = 4 或 tx_type = 14 的记录，您可以更改最后的 SQL 行：

AND b.tx_type IN (4,14)

Answer 3

我只是猜你的意思是 b.tx_type = 4 OR b.tx_type = 14

SELECT a.username, a.first_name, a.last_name, b.tx_time, 
b.account_id, a.id, b.table_id, b.tx_type, b.amount 
FROM punter a, account_transaction b 
WHERE b.tx_time >= '2011-07-01' AND b.tx_time < '2011-09-30' 
AND b.account_id = a.id 
-- AND b.tx_type = 4 AND b.tx_type = 14
AND b.tx_type in (4, 14)

Answer 4

这部分：

AND b.tx_type = 4 
AND b.tx_type = 14

确保您在结果中获得零行。

---

如果您希望下注者同时进行4和14类型的交易，则需要JOIN两次进入赌桌b：

SELECT a.username, a.first_name, a.last_name
     , b1.tx_time AS tx_time_1
     , b2.tx_time AS tx_time_2
     , a.id 
     , b1.table_id AS table_id_1, b1.tx_type AS tx_type_1, b1.amount AS amount_1 
     , b2.table_id AS table_id_2, b2.tx_type AS tx_type_2, b2.amount AS amount_2 
FROM punter a
  JOIN account_transaction b1
    ON b1.account_id = a.id 
  JOIN account_transaction b2
    ON b2.account_id = a.id 

WHERE b1.tx_time >= '2011-07-01' 
  AND b1.tx_time < '2011-09-30' 
  AND b1.tx_type = 4

  AND b2.tx_time >= '2011-07-01' 
  AND b2.tx_time < '2011-09-30' 
  AND b2.tx_type = 14