我整天都在想这个问题,但是没有成功,我都快疯了。
我有3个表叫做:选择,拍卖,游戏
我需要实现的是在"选择"表中插入3个新记录,每个游戏都写在"拍卖"表中(它们在两个表中都标有ID号),但一些必要的数据也在"游戏"表中,这与"拍卖"表(与BetfairMark)相连。避免重复输入,如果有的话。
'Auctions'表有如下列:id(从表前写入,并与'selections'表连接)BetfairMark(必须与'game'中的'BetfairMarketID'相同,以便正确连接'auctions'表和'game'表),title(写入table from before)
的选择的表列:
id(该表中每产生一条新记录自动增加的数目);auctionid(这是必须在这里创建的号码,该查询与拍卖表中的'id'相同)order(3条记录中的每一条必须以查询订单号1,2,3生成)赔率(需要从游戏表中获得每条记录的正确赔率)
的游戏的表有一列:BetfairMarketID(它需要与拍卖表中的'BetfairMark'相同的数字,以确定从该表中获取数据的正确行)ABack(需要在'order' 1下的选择新记录中的赔率)BBack(需要在'order' 2下的选择新记录中添加的赔率)缺点(需要在'order' 3下的选择新记录中添加的赔率)
总结需要实现这一点:-在"选择"表中插入3条新记录,其中每条记录需要标记为顺序1,2和3。必须显示有适当的'auctionid'号码,根据拍卖表的关系。-如果有重复的记录条目(这意味着一些记录将有相同的'auctionid'编号和相同的'order'编号记录将不会被插入)。-在这3条新记录中必须输入"赔率"数字,这些数字来自"游戏"表中的"ABack","BBack","退税"列(根据BetfairMarketID/BetfairMark相同的数字,与"拍卖"表相关)。
我尝试了这个,希望是正确的轨道,但需要大量的修改:
INSERT INTO selections (selections.auctionid, selections.order, selections.odds)
VALUES
((SELECT id FROM auctions), '1', (select ABack FROM game, auctions WHERE game.BetfairMarketID = auctions.BetfairMark)),
((SELECT id FROM auctions), '2', (select BBack FROM game, auctions WHERE game.BetfairMarketID = BetfairMark)),
((SELECT id FROM auctions), '3', (select DrawBack FROM game, auctions WHERE game.BetfairMarketID = BetfairMark))
[Err] 1242 -子查询返回多于1行
解决!为每一行创建了3个独立的查询,现在可以工作了!
因此,添加UNIQUE INDEX到(auctionid, order)对有这个可操作的代码:
INSERT IGNORE INTO
selections
(
selections.auctionid,
selections.order,
selections.title,
startamount
)
SELECT
auctions.id,
1,
PlayerA,
0.01
FROM
auctions, game
WHERE
auctions.BetfairMark = game.BetfairMarketID
;
INSERT IGNORE INTO
elections
(
selections.auctionid,
selections.order,
selections.title,
startamount
)
SELECT
auctions.id,
2,
PlayerB,
0.01
FROM
auctions, game
WHERE
auctions.BetfairMark = game.BetfairMarketID
;
INSERT IGNORE INTO
selections
(
selections.auctionid,
selections.order,
selections.title,
startamount
)
SELECT
auctions.id,
3,
'third text',
0.01
FROM
auctions, game
WHERE
auctions.BetfairMark = game.BetfairMarketID
;
因为您有一个PHP标记,所以我假设可以使用一些PHP代码来处理它。
使用PHP运行以下查询
SELECT id, BetfairMarketID FROM game
然后,遍历每一行,并这样做:
INSERT INTO selections (selections.auctionid, selections.order, selections.odds)
VALUES
((SELECT id FROM auctions WHERE BetfairMark = $BetfairMarketID), '1', (select ABack FROM game WHERE id = $id)),
((SELECT id FROM auctions WHERE BetfairMark = $BetfairMarketID), '2', (select BBack FROM game WHERE id = $id)),
((SELECT id FROM auctions WHERE BetfairMark = $BetfairMarketID), '3', (select DrawBack FROM game WHERE id = $id))
如果我明白你想做什么,这应该是可行的。如果我说错了,请留下评论!