将两个MySQL查询合并为一个
将两个MySQL查询合并为一个
提问于 2013-06-04 14:27:15
我有两个不同的查询，如下所示-
SELECT s.serialno, s.productid, s.description, s.in_quantity, s.uom,
s.shiptype,s.receiveddate, s.project_id, IF(EXISTS(SELECT so.serialno
FROM stockout so WHERE so.serialno = s.serialno),1,0) AS movementHistory
FROM stockin s WHERE s.productid = 'UKL40114/11HP'上面查询的示例结果，限制为0,5
mysql> SELECT s.serialno, s.productid, s.description, s.in_quantity, s.uom,
-> s.shiptype,s.receiveddate, s.project_id, IF(EXISTS(SELECT so.serialno
-> FROM stockout so WHERE so.serialno = s.serialno),1,0) AS movementHist
ory
-> FROM stockin s WHERE s.productid = 'UKL40114/11HP' LIMIT 0, 5;
+------------+---------------+--------------------------+-------------+------+--
--------+---------------------+------------+-----------------+
| serialno | productid | description | in_quantity | uom | s
hiptype | receiveddate | project_id | movementHistory |
+------------+---------------+--------------------------+-------------+------+--
--------+---------------------+------------+-----------------+
| A23001YKG5 | UKL40114/11HP | RADIO UNIT/RAU2 13/11 HP | 1 | 4 |
3 | 2008-08-27 00:00:00 | 1 | 1 |
| A230024FUV | UKL40114/11HP | RADIO UNIT/RAU2 13/11 HP | 1 | 4 |
2 | 2008-11-17 00:00:00 | 1 | 1 |
| A230024NHT | UKL40114/11HP | RADIO UNIT/RAU2 13/11 HP | 1 | 4 |
2 | 2009-03-17 00:00:00 | 1 | 1 |
| A230024KQT | UKL40114/11HP | RADIO UNIT/RAU2 13/11 HP | 1 | 4 |
2 | 2009-03-17 00:00:00 | 1 | 1 |
| A230024UR4 | UKL40114/11HP | RADIO UNIT/RAU2 13/11 HP | 1 | 4 |
2 | 2009-03-17 00:00:00 | 1 | 1 |
+------------+---------------+--------------------------+-------------+------+--
--------+---------------------+------------+-----------------+
5 rows in set (0.01 sec)和
SELECT so.out_quantity, so.serialno, so.siteid, so.employee_id,
so.deliverytime, so.receivername
FROM stockout so WHERE so.serialno = 'A23001RK3N'上述查询的示例结果-
mysql> SELECT so.out_quantity, so.serialno, so.siteid, so.employee_id, so.delive
rytime, so.receivername FROM stockout so WHERE so.seri
alno = 'TU8D322820';
+--------------+------------+-----------+-------------+---------------------+---
-----------+
| out_quantity | serialno | siteid | employee_id | deliverytime | re
ceivername |
+--------------+------------+-----------+-------------+---------------------+---
-----------+
| 1 | TU8D322820 | 001BDS011 | 38 | 2008-06-26 10:25:00 | 0
|
+--------------+------------+-----------+-------------+---------------------+---
-----------+
1 row in set (0.00 sec)如果为movementHistory == 1，我将在PHP页面上执行第二个查询。但是，我想将此查询与第一个查询内联。我的想法是这样的：
SELECT s.serialno, s.productid, s.description, s.in_quantity, s.uom, s.shiptype,
s.receiveddate, s.project_id, IF(EXISTS(SELECT so.serialno FROM stockout so WHERE
so.serialno = s.serialno),1,0) AS movementHistory
CASE movementHistory
WHEN '1' THEN (SELECT so.out_quantity, so.serialno, so.siteid, so.employee_id,
so.deliverytime, so.receivername
FROM stockout so WHERE so.serialno = 'TU8D322820')
WHEN '0' THEN 0
END AS Policy
FROM stockin s WHERE s.productid = 'UKL40114/11HP'我该如何实现？
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-06-04 15:23:39
如果我理解正确的话，你可以试试这个：
SELECT
s.serialno,
s.productid,
s.description,
s.in_quantity,
s.uom,
s.shiptype,
s.receiveddate,
s.project_id,
IF(EXISTS(
SELECT
so.serialno
FROM
stockout so
WHERE
so.serialno = s.serialno
),
1,
0
) AS movementHistory,
so.out_quantity,
so.serialno,
so.siteid,
so.employee_id,
so.deliverytime,
so.receivername
FROM
stockin s
LEFT JOIN stockout so
ON so.serialno = 'A23001RK3N'
AND IF(EXISTS(
SELECT
so1.serialno
FROM
stockout so1
WHERE
so1.serialno = s.serialno
),
1,
0
) = 1
WHERE
s.productid = 'UKL40114/11HP'Stack Overflow用户
发布于 2013-06-04 15:23:27
尝试使用LEFT JOIN JOINING这两个表。检查此查询：
SELECT SI.serialno
, SI.productid
, SI.description
, SI.in_quantity
, SI.uom
, SI.shiptype
, SI.receiveddate
, SI.project_id
, IFNULL(SO.out_quantity, -1) AS out_quantity
, IFNULL(SO.siteid, '') AS siteid
, IFNULL(SO.employee_id, -1) AS employee_id
, IFNULL(SO.deliverytime, '') AS deliverytime
, IFNULL(SO.receivername, -1) AS receivername
FROM stockin SI
LEFT JOIN stockout SO ON SO.serialno = SI.serialno
WHERE SI.productid = 'UKL40114/11HP'
LIMIT 0, 5;如果stockout表返回的值为NULL，您可以更改这些值。检查IFNULL函数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16911139
复制相关文章
相似问题
腾讯云开发者
