我需要协助加入 [英] I need assistance on making a join
问题描述
大家好,
我正在努力加入Querry,如果可以的话,请为我提供帮助.
现在,这是我的问题;
考虑下表.
stock_movement具有字段
Movement_id(PK),
date_,
类别,
foreign_id,
数量);
该表记录了所有从商店搬出的库存;
类别,确定库存去向,
例如如果为0,则返回柜台,如果为1,则返回车间,如果为2,则返回另一个分支,以此类推
所以每个类别我还有另外三个表;
0)表计数器(counter_id,counter_name,counter_description);
1)表格工作坊(workshop_id,workshop_name,supervisor);
2)表分支(branch_id,branch_name,branch_manager);
现在,我需要每天报告库存变动情况;
因此我的查询需要选择data = selected_date的位置,并且每个记录都有一个叙述性列来显示记录的去向(counter_name或workshop_name或branch_name),具体取决于记录的类别.
我认为解决方案应该像这样;
HI everyone,
I am struggling to make a join querry, kindly assist me if you can.
Now, this is my problem;
Consider the following table;
stock_movement has fields
movement_id (PK),
date_,
category,
foreign_id,
quantity);
This table records all stock moving out of a store;
category, determines where the stock is going,
e.g. if 0, to the counter, if 1 to workshop, if 2 to another branch etc
so i have three other tables for each category;
0) table counters (counter_id, counter_name, counter_description);
1) table workshops (workshop_id, workshop_name, supervisor);
2) table branches (branch_id, branch_name, branch_manager);
Now, i need to make a daily report of stock moving out;
so my query need to select where data = selected_date, and for each record have a narrative column showing where it went (counter_name or workshop_name, or branch_name) depending on the category of the record.
i think the solution should like to something like this;
select sm.date_, sm.quantity, if(category = 0) then (select counter_name from counters where counter_id = sm.foreign_id) else if (category = 1) then
(select workshop_name from workshops where workshop_id = sm.foreign_id) else
(select branch_name from branches where branch_id = sm.foreign_id) as target_name from stock_movement
但它不起作用,您能帮我使它起作用吗,
有没有一种方法可以将if放在连接部分上以使其起作用.
在此先感谢
but it does not work, can you help me make it work,
is there a way we can put the ifs on the join part to make it work.
Thanks in advance
推荐答案
您可以尝试一下
YOU CAN TRY THIS
SELECT sm.date_, sm.quantity, alltarget.target_name FROM
stock_movement as sm JOIN
(
SELECT 1 as category, counter_id as id,counter_name as target_name FROM counters
UNION ALL
SELECT 2 as category, workshop_id as id,workshop_name as target_name FROM workshops
UNION ALL
SELECT 3 as category, branch_id as id, branch_name as target_name FROM branches)
AS alltargets
ON sm.foreign_id=alltargets.id and sm.category=alltargets.category
如果您想要更快的代码,可以尝试将并集选择放入一个临时表中,在该表上建立一个索引并与之联接,但是那样工作会很费劲.
PS:我没有发表声明,所以您可能会发现一些拼写错误的内容.
If you want a much faster code you can try put the union select into a temporary table make an index on it an join with that, but it will work fain just like that.
PS: I didn''t run the statment so you might find some misspelled stuff.
这篇关于我需要协助加入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!