如何在一个mysql表中显示两个查询

njthzxwz 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(320)

表1:rm

create table rm(rcod int(5) primary key, qty int(5));

表2:cs

create table cs(cscod int(5)primary key, qty int(5));

表3：最终结果

create table final(icod int(5) primary key, qty int(5));

表4:cmbine

create table cmbine(icod int(5),rcod int(5),rmu int(5),cscod int(5),csu int(5));

现在插入值：

insert into rm values(1,10);
insert into rm values(2,20);

insert into cs values(1,20);
insert into cs values(2,10);

    insert into final values(1,50);

insert into cmbine values(1,1,1,2,5);
insert into cmbine values(1,1,20,1,2);

现在，我需要来自以下两个查询的结果，我要合并列并使其成为一个。查询1：

select rcod, 
    qty 
from rm 
where rcod in (select rcod 
               from cmbine 
               where icod = 1);

问题2：

select cscod,
    qty 
from cs 
where cscod in (select cscod 
                from cmbine 
                where icod = 1);

我本以为这能帮上忙，但我犯了个错误。

select cmbine.icod,
        cmbine.rcod,
        rm.qty,
        (rm.qty*cmbine.rmu),
        cmbine.cscod,
        cs.qty,
        cmbine.csq,
        (cs.qty * cmbine.csq) 
    from rm,
        cs,
        cmbine 
    where rcod in (select rcod 
                   from cmbine 
                   where icod=1) 
        and cscod in (select cscod 
                      from cmbine 
                      where icod=1)
;

我希望现在的信息足够了。
谢谢！
我该怎么做？

mysql

来源：https://stackoverflow.com/questions/61260660/how-to-display-two-queries-in-one-mysql-table

1条答案

按热度按时间

0sgqnhkj1#

@ridamjain使用 UNION ，如下所示：

select icod, 
    qty 
from cs 
where icod in (select distinct cscod 
               from cmbine 
               where icod=100)

UNION

select icod, 
    qty 
from rm 
where rcod in (select distinct rscod 
               from cmbine 
               where icod=100)

我认为你也不需要有子查询。下面是如何在没有子查询的情况下执行此操作：

select cs.icod, 
    cs.qty 
from cs
JOIN cmbine AS cm ON cm.cscod = cs.icod
    AND cm.icod = 100
GROUP BY cs.icod

UNION

select rm.icod, 
    rm.qty 
from rm
JOIN cmbine AS cm ON cm.rscod = rm.rcod
    AND cm.icod = 100
GROUP BY rm.icod

如果您需要判断哪个结果集来自哪个查询，下面是如何做到这一点的：

select 'First Query' AS result_source,
    cs.icod, 
    cs.qty 
from cs
JOIN cmbine AS cm ON cm.cscod = cs.icod
    AND cm.icod = 100
GROUP BY cs.icod

UNION

select 'Second Query' AS result_source,
    rm.icod, 
    rm.qty 
from rm
JOIN cmbine AS cm ON cm.rscod = rm.rcod
    AND cm.icod = 100
GROUP BY rm.icod

编辑：由于澄清被添加到原始问题中，这里是一个经过清理的查询版本，它应该会给出您所要求的结果。有一点需要注意：在 cmbine 您将最后一列列列为 csu 但在查询中它被引用为 csq . 我使用sql脚本并在sqlfiddle中模拟查询。
以下是查询的最终清理版本：

SELECT `cmbine`.`icod`,
    `cmbine`.`rcod`,
    `rm`.`qty`,
    (`rm`.`qty` * `cmbine`.`rmu`),
    `cmbine`.`cscod`,
    `cs`.`qty`,
    `cmbine`.`csq`,
    (`cs`.`qty` * `cmbine`.`csq`) 
FROM `cmbine`
JOIN `cs` ON `cs`.`cscod` = `cmbine`.`cscod`
JOIN `rm` ON `rm`.`rcod` = `cmbine`.`rcod`
WHERE `cmbine`.`icod` = 1;

如果这仍然不能得到你想要的，只要给我一些澄清，你想要什么，我会尽我所能帮助你。
希望这有帮助！

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

我来回答

如何在一个mysql表中显示两个查询

1条答案

相关问题

热门标签

最新问答