在mysql中如何这样打印:只在一个查询中打印

实现这一点的方法是创建两个表,一个是palaries<6000,另一个是palaries>=6000,然后 JOIN 他们。现在由于这些表中没有自然排序,我们必须为每个表创建一个人工行号,然后 加入 他们就是基于这个。因为我们不知道哪个表可能有更多的行,所以我们必须做一个 FULL OUTER JOIN . 不幸的是MySQL不支持这一点,所以我们必须用 LEFT JOIN UNION 用一个 RIGHT JOIN

SELECT e1.salary AS salary_less_then_6000, e2.salary AS salary_greater_then_6000 
FROM (SELECT salary, @rownum1:=@rownum1+1 AS rownum
      FROM employees
      JOIN (SELECT @rownum1 := 0) r
      WHERE salary < 6000
      ORDER BY salary
      ) e1
LEFT JOIN (SELECT salary, @rownum2:=@rownum2+1 AS rownum
      FROM employees
      JOIN (SELECT @rownum2 := 0) r
      WHERE salary >= 6000
      ORDER BY salary
      ) e2
ON e1.rownum = e2.rownum
UNION
SELECT e1.salary AS salary_less_then_6000, e2.salary AS salary_greater_then_6000 
FROM (SELECT salary, @rownum3:=@rownum3+1 AS rownum
      FROM employees
      JOIN (SELECT @rownum3 := 0) r
      WHERE salary < 6000
      ORDER BY salary
      ) e1
RIGHT JOIN (SELECT salary, @rownum4:=@rownum4+1 AS rownum
      FROM employees
      JOIN (SELECT @rownum4 := 0) r
      WHERE salary >= 6000
      ORDER BY salary
      ) e2
ON e1.rownum = e2.rownum

输出(来自我的演示)

salary_less_then_6000   salary_greater_then_6000
3000                    7000
5000                    8000
(null)                  9000

SQLFiddle Demo