WHERE的困难.IN查询

我目前正在开发一个在线游戏的拍卖网站。然而，在创建统计信息时，我遇到了查询问题。

问题是，我需要拥有我参与的所有拍卖，而我已经输掉了。但到目前为止，我只看到能够得到所有丢失的拍卖（不仅仅是我参加的那些）。

假设我有以下表格：

表格拍卖的表格结构

CREATE TABLE IF NOT EXISTS `Auction` (
  `id` varchar(50) NOT NULL,
  `preferedAuctionId` varchar(50) NOT NULL,
  `winningTicketId` int(11) NOT NULL DEFAULT '-1',
  `createdOn` datetime NOT NULL,
  `startedOn` datetime NOT NULL,
  `finishedOn` datetime NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--表`AuctionBids`的表结构

CREATE TABLE IF NOT EXISTS `AuctionBids` (
  `bidId` varchar(50) NOT NULL,
  `accountId` varchar(50) NOT NULL,
  `auctionId` varchar(50) NOT NULL,
  `ticketId` int(50) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`bidId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

以下是我尝试过的一些查询

SELECT * 
FROM Auction au, AuctionBids ab 
WHERE au.id = ab.auctionId AND au.winningTicketId = ab.ticketId AND 
      ab.accountId NOT LIKE '". $_accountId ."' AND 
      au.finishedOn NOT LIKE '0000-00-00 00:00:00' AND au.active = '0'

和

SELECT * 
FROM Auction 
WHERE winningTicketId NOT IN 
(    SELECT ticketId 
     FROM AuctionBids 
     WHERE accountId = '". $_accountId ."') 
   AND finishedOn NOT LIKE '0000-00-00 00:00:00' AND active = '0'

如果您缺少任何关键信息，请询问我，我会为您提供。

你的真诚，Larssy1

在本地主机上测试我做了一个测试转储：

http://pastebin.com/d412ESVa

预期结果：

账户ID为"｛0D3FB1B2-1C0C-06EA-979A-15F5B5231E7C｝"的人赢得了他参与的所有拍卖，这意味着他返回的行数应为0。

账号为"｛65CC1576-C3C3-5AC1-8098-B8CF1347F481｝"的人输掉了他参与的所有拍卖，这意味着他返回的行数应该是1。

也许是这样？

SELECT distinct a.* FROM Auction a JOIN AuctionBids ab ON a.id = ab.auctionId 
WHERE a.winningTicketId not in 
(select ticketId from AuctionBids WHERE accountId = '". $_accountId ."'
and a.id = auctionId) 
AND a.id in
(select auctionId from AuctionBids WHERE accountId = '". $_accountId ."')
AND a.active = '0'
AND finishedOn NOT LIKE '0000-00-00 00:00:00';

已编辑。在SQLFiddle 中试用

我想您正在寻找一个联接表。我假设winningTicket字段包含中奖彩票id。

SELECT * 
FROM Auction JOIN AuctionBids ON Auction.id=AuctionBids.auctionId
WHERE Auction.active=0 AND 
      AuctionBids.accountId=yourAccountID AND 
      Auction.winningTicket<>AuctionBids.ticketId

也许这会帮助你或让你走上正确的道路？

===更新===

如果你这样做：

SELECT 
    Auction.id AS auctionID,
    Auction.winningTicketId,
    SUM( IF(Auction.winningTicketId=AuctionBids.ticketId, IF(AuctionBids.accountId=yourID,1,0 ),0) ) AS youWON,
    SUM( IF(AuctionBids.accountId=yourID,1,0) ) AS nYouBID
FROM Auction JOIN AuctionBids ON Auction.id=AuctionBids.auctionId
WHERE
    Auction.active=0
GROUP BY Auction.id
ORDER BY youWON ASC, nYouBID DESC, auctionID ASC

将您的ID替换为"｛0D3FB1B2-1C0C-06EA-979A-15F5B5231E7C｝"SQLFiddle:http://SQLFiddle.com/#！2015年9月8日星期二

如果你是赢家，如果你真的出价，这将为每个非活动拍卖提供一行两个bool。然后你必须分析第一行，直到你打出youWON==1。

如果你想把它作为你的帐户id，为什么你用ab.accountId不喜欢'".$_accountId."'？

 I think you should use ab.accountId LIKE '". $_accountId ."'

类似这样的东西，你可以检索"赢"、"输"、"参与"（未测试，但我想方法是正确的）

SELECT 'win', A.*, B,*
FROM Auction A INNER JOIN AuctionBids B
  ON A.id = B.auctionId
WHERE B.accountId = $me
  AND B.active = 0
  AND A.winningTicketId = B.bidID
UNION
SELECT 'lose', A.*, B,*
FROM Auction A INNER JOIN AuctionBids B
  ON A.id = B.auctionId
WHERE B.accountId = $me
  AND B.active = 0
  AND A.winningTicketId !=  B.bidID
UNION
SELECT 'ing', A.*, B,*
FROM Auction A INNER JOIN AuctionBids B
  ON A.id = B.auctionId
WHERE B.accountId = $me
  AND B.active = 1

Difficulties with WHERE .. IN query

--表AuctionBids的表结构

--表`AuctionBids`的表结构