mysql - POS 应用程序 - 简化 SQL 多查询(MySQL)

Question

嘿，我正在使用 MySQL 开发 POS 应用程序。这是我的情况:

表“purchased_item”

|    id    |    name    |    check_id    |    real_price    |
|    1     |   iPhone5  |      0001      |       399        |
|    2     |   iPhone4  |      0001      |       199        |
|    3     |   iPhone5s |      0002      |       599        |
|    4     |   iPhone5c |      0003      |       399        |
|    5     |   iMac 21" |      0003      |       999        |
|    6     | iPod Touch |      0003      |        99        |
|    7     |   iPhone5  |      0004      |       399        |
|    8     |   iPhone3G |      0004      |        99        |
|    9     |   iPhone6  |      0005      |       899        |
|    10    |  iPhone3Gs |      0005      |       101        |

而我想知道有多少张支票的总数大于或等于 qual(>=) 1000，所以我现在做的是多次查询。在这个例子中，我做了 5 次并由主机程序手动求和。

后来数据增长，查询变得缓慢，因为每天都有大量的检查。所以我改为记录到另一个表。

表“检查”

|    id    |    total    |    sales    |
|   0001   |     598     |      A      |
|   0002   |     599     |      A      |
|   0003   |     1497    |      B      |
|   0004   |     498     |      B      |
|   0005   |     1000    |      A      |

但是后来又出现了一个问题:当我需要调整“purchased_item”表中的real_price时，我还需要维护“checks”表中的“total”列。听起来没什么大不了的，但我想找到更好的方法来解决它。

已解决:

SELECT * FROM purchased_item
GROUP BY check_id
HAVING sum(real_price) >= 1000

结果将是:

|    id    |    name    |    check_id    |    real_price    |
|    4     |   iPhone5c |      0003      |       399        |
|    9     |   iPhone6  |      0005      |       899        |

进一步的问题:如果我想计算支票的总价，我该怎么做？我找到了:

SELECT check_id,sum(real_price) FROM purchased_item
GROUP BY check_id
HAVING sum(real_price) >= 1000

Answer 1

这样试试

SELECT i.id, i.name, i.check_id, i.real_price
  FROM 
(
  SELECT MIN(id) id
    FROM purchased_item 
   GROUP BY check_id
  HAVING SUM(real_price) >= 1000
) q JOIN purchased_item i
    ON q.id = i.id
 ORDER BY q.id DESC

示例输出:

| ID |     NAME | CHECK_ID | REAL_PRICE ||----|----------|----------|------------||  9 |  iPhone6 |        5 |        899 ||  4 | iPhone5c |        3 |        399 |

---

...I want to count how many checks's total are over 1000

For that you can just do this

SELECT COUNT(*) total
  FROM
(
  SELECT check_id
    FROM purchased_item 
   GROUP BY check_id
  HAVING SUM(real_price) >= 1000
) q;

示例输出:

| TOTAL ||-------||     2 |

Here is SQLFiddle demo

---

To update total in checks after adjusting real_price in purchased_item

UPDATE checks c JOIN 
(
  SELECT check_id, SUM(real_price) total
    FROM purchased_item 
   WHERE check_id IN(5) -- whatever check(s)'s total you want to recalculate
   GROUP BY check_id
) p
    ON c.id = p.check_id
   SET c.total = p.total;

这是 SQLFiddle 演示