我有以下表格:
products
-包含库存中的产品suppliers
-包含供应商详细信息product_category
-包含产品类别的名称product_suppliers
-包含产品和供应商之间的关系
这里重要的一个可能是product_suppliers
,所以这里是列:
id //(auto-increment)
prod_id //(id of product)
sup_id //(id of supplier)
preferred //(1 if it's the preferred supplier for that product - 0 if not)
cost_per_outer //(this suppliers price for this item)
qty_in_outer //(the number in a box)
在存储产品详细信息的product
表中,还具有用于stock_level
和reorder_level
的字段。当前者少于后者时,应将该产品包括在重新订购的列表中。
我正在尝试为列表构建查询,但运行不太正常。
我有4个项目符合重新订购的条件,影响了两个供应商。每个供应商应该有两个项目,但目前它只检索第一个供应商的结果。
我使用的是MySQL和PHP。
我运行了以下查询,以便深入了解数据库中的实际数据:
mysql> SELECT
-> p.prod_id,
-> AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht') AS sku,
-> AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht') AS prod_name,
-> AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') AS prod_type,
-> AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') AS stock_level,
-> AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht') AS reorder_level,
-> AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht') AS category_name,
-> ps.sup_id,
-> ps.preferred
-> FROM
-> products p
-> INNER JOIN
-> product_category c
-> ON
-> p.category_id = c.category_id
-> INNER JOIN
-> product_supplier ps
-> ON
-> p.prod_id = ps.prod_id
-> INNER JOIN
-> suppliers s
-> ON
-> ps.sup_id = s.supplier_id
-> ORDER BY
-> ps.sup_id;
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
| prod_id | sku | prod_name | prod_type | stock_level | reorder_level | category_name | sup_id | preferred |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
| 7 | 7 | Term Block | 1 | 3 | 5 | Electrical | 2 | 1 |
| 5 | 5 | Electrical Tape | 1 | 12 | 20 | Electrical | 2 | 1 |
| 6 | 6 | BlowGas | 1 | 6 | 15 | Plumbing | 12 | 1 |
| 1 | 1 | PTFE Tape | 1 | 9 | 10 | Plumbing | 12 | 1 |
+---------+------+-----------------+-----------+-------------+---------------+---------------+--------+-----------+
以下是对列表的查询,结果只显示了我所期望的四个项目中的两个:
mysql> SELECT
-> p.prod_id,
-> AES_DECRYPT(p.alt_id, 'MW4KQLg1Irfo3Xz7Nxht') AS sku,
-> AES_DECRYPT(p.prod_name, 'MW4KQLg1Irfo3Xz7Nxht') AS prod_name,
-> AES_DECRYPT(p.prod_desc, 'MW4KQLg1Irfo3Xz7Nxht') AS prod_desc,
-> AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') AS stock_level,
-> AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht') AS reorder_level,
-> AES_DECRYPT(p.reorder_qty, 'MW4KQLg1Irfo3Xz7Nxht') AS reorder_qty,
-> p.vat_exempt,
-> AES_DECRYPT(p.lastorderdate, 'MW4KQLg1Irfo3Xz7Nxht') AS lastorderdate,
-> AES_DECRYPT(p.lastorderqty, 'MW4KQLg1Irfo3Xz7Nxht') AS lastorderqty,
-> AES_DECRYPT(c.category_name, 'MW4KQLg1Irfo3Xz7Nxht') AS category_name,
-> ps.sup_id
-> FROM
-> products p
-> INNER JOIN
-> product_category c
-> ON
-> p.category_id = c.category_id
-> INNER JOIN
-> product_supplier ps
-> ON
-> p.prod_id = ps.prod_id
-> INNER JOIN
-> suppliers s
-> ON
-> ps.sup_id = s.supplier_id
-> WHERE
-> AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') = 1
-> AND
-> AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht')
-> AND
-> ps.preferred = 1
-> ORDER BY
-> ps.sup_id;
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
| prod_id | sku | prod_name | prod_desc | stock_level | reorder_level | reorder_qty | vat_exempt | lastorderdate | lastorderqty | category_name | sup_id |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
| 7 | 7 | Term Block | Nylon connector block. | 3 | 5 | 20 | 0 | NULL | NULL | Electrical | 2 |
| 5 | 5 | Electrical Tape | Black | 12 | 20 | 100 | 0 | NULL | NULL | Electrical | 2 |
+---------+------+-----------------+------------------------+-------------+---------------+-------------+------------+---------------+--------------+---------------+--------+
问题是,您的结果来自AES_DECRYPT((,属于string
类型,这就是为什么比较3 - 5
和12 - 20
的库存级别和重新排序级别值时,它会显示,而比较6 -15
和9 - 20
时,它不会显示,因为如果您将6和15作为字符串6,则会超过15。9和20的情况相同。
希望你明白你的问题。。。
在比较之前将AES_DECRYPT((的结果转换为数字。。
将您的where条款更改为此
WHERE
(AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht') + 0) = 1 AND
(AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0) <= (AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht') + 0)
添加+0
会将结果强制转换为数字
希望这能有所帮助。
WHERE
-> AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht',varchar) = 1
-> AND
-> AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar) <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar)
或:
WHERE
-> AES_DECRYPT(p.prod_type, 'MW4KQLg1Irfo3Xz7Nxht',int) = 1
-> AND
-> AES_DECRYPT(p.stock_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar) <= AES_DECRYPT(p.reorder_level, 'MW4KQLg1Irfo3Xz7Nxht',varchar)
发件人:http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01015.1510/html/iqsecure/Functions_s_4132405.htm
如果在使用AES_ENCRYPT函数,可以使用AES_DECRYPT查看相同的数据函数,方法是将VARCHAR作为数据类型传递。如果你没有通过数据类型设置为AES_DECRYPT,则返回VARBINARY数据类型。