在一点pickle中,我有一个复杂的查询,它返回2个结果,这是很好的,但是我想修改查询,以便它合并2并返回1个结果。我想知道这是否可能。我举了一个例子:
SELECT s.f_Name s.l_Name, t.tour_Name, o.order_Placed, s.staff_ID, t.tour_ID, d.date
FROM Staff_Day AS d, Tour as t, Staff AS s, Customers AS c
JOIN Orders AS o ON c.customer_ID = o.order_No
WHERE c.date >= '2014-03-18 00:00:00' AND c.date <= '2014-03-19 23:59:59'
AND d.tour_ID = c.trip_Taken
AND t.tour_ID = d.tour_ID
AND d.date = c.date
AND d.staff_ID = s.staff_ID
ORDER BY d.staff_ID ASC
这将返回两个结果:
我要将其合并为1个结果,以便它具有以下列:
f_Name1, l_Name1, f_Name2, l_Name2, tour_Name,
order_Placed1, order_Placed2, staff_ID1, staff_ID2,
tour_ID1, tour_ID2, staff_Day_Date1, staff_Dat_Date2
最佳答案
是的,在MySQL中可以这样做。但是。。。
你不想这样做。
这最好用PHP的过程逻辑来处理。获取这两行,然后将它们组装到所需的输出中。
作为在MySQL中的一个演示:
SELECT t.*
FROM ( SELECT @prev_fee AS fee_r1
, @prev_fee := s.fee AS fee_r2
, @prev_fi AS fi_r1
, @prev_fi := s.fi AS fi_r2
, @prev_fo AS fo_r1
, @prev_fo := s.fo AS fo_r2
FROM (
SELECT 'a' AS fee, '1' AS fi, 'apple' AS fo
UNION ALL
SELECT 'c','3','cat'
UNION ALL
SELECT 'e','5','egg'
UNION ALL
SELECT 'b','2','bad'
ORDER BY 1
) s
CROSS
JOIN ( SELECT @prev_fee := NULL
, @prev_fi := NULL
, @prev_fo := NULL
) i
LIMIT 2
) t
LIMIT 1,1
原始查询将替换别名为
s
的内联视图查询。您需要为查询返回的每一列定义用户变量,并使用这些变量“保存”前一行的值。在别名为
t
的内联视图中,在将用户变量设置为当前行的值之前,需要在“选择”列表中显示前一行中保存的值。如果需要对列重新排序,则需要在最外层的查询中进行排序。