php mysql 库存

发布于 2024-12-08 20:33:27 字数 1239 浏览 2 评论 0原文

我正在为名为 Magic: the Gathering 的集换式卡牌游戏创建一个应用程序，并且我做了一个查询来检查所有用户提交的牌组，并为您提供库存中卡牌的百分比。甲板上的卡片。但我的问题是，它对数据库上的所有牌组都执行此操作。我想做的只是归还我已经拥有 50% 牌的牌组。

这是查询：

SELECT
SUM(t.qty_inv) / t.deck_cards completed,
t.deck_id
FROM (
  SELECT
  CASE WHEN m.qty_inv IS NULL THEN 0 WHEN m.qty_inv > dc.card_qty THEN dc.card_qty ELSE m.qty_inv END qty_inv,
  dc.deck_id,
  d.deck_cards
  FROM mtgb_test.decks d
  INNER JOIN mtgb_test.decks_cards dc ON (d.deck_id = dc.deck_id)
  LEFT OUTER JOIN (
    SELECT
    COUNT(*) qty_inv, item_print_id print_id
    FROM mtgb_test.inventories_items
    WHERE item_user_id = 1
    GROUP BY item_print_id
  ) m ON (m.print_id = dc.card_print_id)
) t
GROUP BY deck_id
ORDER BY completed DESC;

这个查询的问题是我不能像这样在 where 子句中使用派生的 completed 字段：

WHERE completed > 0.5

我不知道变量是否可以解决这个问题，我尝试了但它被搞砸了，因为我对用户定义的变量很陌生。

编辑：一些好人在下面回答说我需要 HAVING 语法，这是正确且明显的答案。然后我可能会选择后记问题的最佳答案。

另一件事，如果我的数据库中有 200,000 副牌，并且我的库存中有 2,000 张牌，我可以接受这个查询循环所有这些牌并找出我已经拥有一半牌的牌组。但我的问题是左外连接。我不知道 MySQL 内部是如何工作的，但也许知道的人可以指出来。当循环遍历所有 200,000 个牌组时，它会为每个牌组进行左连接，还是会足够聪明地缓存它，以便它只查询我的库存物品一次？

预先感谢，

拉蒙

原文

I'm creating an app for a trading card game called Magic: the Gathering and I have made a query that checks all user-submitted decks and gives you the percentage of cards you have in your inventory over the cards in the deck. But my problem is, it does this for all the decks on the database. What I want to do is only return the decks which I already have 50% of the cards for.

Here is the query:

SELECT
SUM(t.qty_inv) / t.deck_cards completed,
t.deck_id
FROM (
  SELECT
  CASE WHEN m.qty_inv IS NULL THEN 0 WHEN m.qty_inv > dc.card_qty THEN dc.card_qty ELSE m.qty_inv END qty_inv,
  dc.deck_id,
  d.deck_cards
  FROM mtgb_test.decks d
  INNER JOIN mtgb_test.decks_cards dc ON (d.deck_id = dc.deck_id)
  LEFT OUTER JOIN (
    SELECT
    COUNT(*) qty_inv, item_print_id print_id
    FROM mtgb_test.inventories_items
    WHERE item_user_id = 1
    GROUP BY item_print_id
  ) m ON (m.print_id = dc.card_print_id)
) t
GROUP BY deck_id
ORDER BY completed DESC;

The problem with this query is that I can't use the derived completed field in the where clause like so:

WHERE completed > 0.5

I don't know if variables can solve this problem, I tried a bit but it got mished mashed as I'm very new to user-defined variables.

Edit: Some good people answered below that I needed to have the HAVING syntax, and that is the correct and obvious answer. I'd just probably choose the best answer to the postscript question then.

Another thing, if I had 200,000 decks in my database and I had 2,000 cards in my inventory, I'm fine with this query looping through all those and finding which decks I already had half of the cards for. But my problem is with the LEFT OUTER JOIN. I don't know about how MySQL really works inside but maybe someone who does can point it out. When looping through all the 200,000 decks, would it do the left join for every deck there is or will it be smart enough to cache this so that it only queries my inventory items once?

Thanks in advance,

Ramon

分享到QQ

分享到微博