使用union query合并mysql的varchar行

uxh89sit 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(309)

我有两张table，分别是department和function。
部门

------------------------
dept_id   |  dept_name
------------------------
  1       |  department1
  2       |  department2
------------------------

功能

--------------------------------------------------------------
function_id  |  function_name  |  dept_id  | is_main_function
--------------------------------------------------------------
    1        |  function1      |    1      |       1
    2        |  function2      |    1      |       0
    3        |  function3      |    1      |       0
--------------------------------------------------------------

这是我的问题

select dept_name,function_name as main_function,null as sub_function from department d,functon f where d.dept_id = f.dept_id and is_main_function = 1
 union 
 select dept_name,null as main_function,function_name as sub_function from department d,functon f where d.dept_id = f.dept_id and is_main_function = 0

此查询的输出为

------------------------------------------------
dept_name    |  main_function  |  sub_function 
------------------------------------------------
department1  |    function1    |    null
department1  |     null        |   function2      
department1  |     null        |   function2      
------------------------------------------------

但我想要的是

------------------------------------------------
dept_name    |  main_function  |  sub_function 
------------------------------------------------
department1  |    function1    |   function2      
department1  |    function1    |   function2      
------------------------------------------------

有可能得到这个输出吗？

mysql union

来源：https://stackoverflow.com/questions/51979628/combine-varchar-rows-of-mysql-using-union-query

2条答案

按热度按时间

kxxlusnw1#

您可以使用以下解决方案 INNER JOIN 而不是 UNION :

SELECT d.dept_name, f1.function_name AS main_function, f2.function_name AS sub_function
FROM department d 
    INNER JOIN `function` f1 ON d.dept_id = f1.dept_id AND f1.is_main_function = 1 
    INNER JOIN `function` f2 ON d.dept_id = f2.dept_id AND f2.is_main_function = 0

demo:https://www.db-fiddle.com/f/owqzs9c1bngf4zwcswtirf/1

赞(0）回复(0）举报 2021-06-20

8ftvxx2r2#

试试这个，它用 JOIN 要实现您的目标：

declare @department table (dept_id int, dept_name varchar(20));
insert into @department values
 (1 , 'department1'),
 (2 , 'department2');

declare @function table (function_id int, function_name varchar(20), dept_id int, is_main_function int);
insert into @function values
 (1 , 'function1' , 1 , 1),
 (2 , 'function2' , 1 , 0),
 (3 , 'function3' , 1 , 0);

 select d.*, main.function_name, sub.function_name
 from @department d
 join @function main on d.dept_id = main.dept_id and main.is_main_function = 1
 join @function sub on d.dept_id = sub.dept_id and sub.is_main_function = 0

赞(0）回复(0）举报 2021-06-20

我来回答

使用union query合并mysql的varchar行

2条答案

相关问题

热门标签

最新问答