MySQL许多‘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

您可以使用显式连接来稍微简化一下：

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是错误的，因为它总是错误的。如果需要获取包含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)