这是我的mysql查询:
SELECT
(SELECT SUM(jb_qty) FROM tbl_transactions WHERE jb_Type=1 AND jb_s_Id=$s_Id) AS purqty,
(SELECT SUM(jb_qty) FROM tbl_transactions WHERE jb_Type=2 AND jb_s_Id=$s_Id) AS salqty
FROM tbl_transactions LIMIT 1
以获取库存状态(jb_type
1
为采购,2
为销售)。
我也希望从中获得当前的库存状态(如purqty-salqty
),并需要更好的查询来获得这些结果。
这里有几点可以很容易地改进。首先,您可以消除子查询,只使用带有case
表达式的单个查询,因此相同的行将只处理一次。其次,由于两个查询的jb_s_Id
条件相同,您可以再次将其移到where
子句中,以减少查询需要处理的行数:
SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
FROM tbl_transactions
WHERE jb_s_Id = $s_Id
编辑:
为了回答评论中的问题,如果你想从purqty中减去salqty,你可以在两个表达式之间使用-
运算符:
SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
SUM(CASE jb_type WHEN 1 THEN jb_qty END) -
SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS diff
FROM tbl_transactions
WHERE jb_s_Id = $s_Id
不过,一种更优雅的方法是使用子查询,这样就不必重复两次相同的表达式:
SELECT purqty, salqty, purqty - salqty
FROM (SELECT SUM(CASE jb_type WHEN 1 THEN jb_qty END) AS purqty,
SUM(CASE jb_type WHEN 2 THEN jb_qty END) AS salqty
FROM tbl_transactions
WHERE jb_s_Id = $s_Id) t