输入来自不同 mysql 表的数据

本文介绍了输入来自不同 mysql 表的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我当前的查询是:

INSERT INTO `ipdKayaChikitsaF`(`IPDNo`、`OPDNo`、`RegNo`、`PatientId`、`PDep`、`PWard`、`PBed`、`pSymptom`、`PDisease`、`PName`、`PMob`、`uid`、`PSex`、`Page`、`PMStatus`、`PFname`、`PAddr`、`PDist`、`PState`、`OPDDate`、`dischargeStatus`、`admitOn`、`dischargeOn`、`CreatedOn`、`CreatedBy`)SELECT '1',OPDNo,RegNo,PatientId,PDep,'Kaya Chikitsa (Female)','1',pSymptom,'na',pname,PMob,uid,PSex,Page,PMStatus,PFname,PAddr,PDist,PState,OPDDate,'0',OPDDate,'0000-00-00',OPDDate,'$user'来自`opd`其中 OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`ORDER BY RAND() LIMIT $ipdKayaChikitsaF";

以上查询工作正常:

现在请检查上面查询中提到的选择查询.在这里，我从表中选择了一些值，其中一些是硬编码的.现在我想从一个不同的表中填充 PBed 值，其中应该有一个条件.

我的床头柜有 3 列像

病房|床号 |地位Kaya Chikitsa (女) 1 0Kaya Chikitsa (女) 2 0Kaya Chikitsa (女) 3 1Kaya Chikitsa (男) 4 0Kaya Chikitsa (男) 5 1Kaya Chikitsa (男) 6 0

我想用床单中的床号填充PBed，它应该有Ward='Kaya Chikitsa (Female)' 和Status=0 的条件；

我的结果应该是这样的

表 ipdKayaChikitsaF

请检查上面的表格她我希望根据上述条件从床单中填充 PBed 列.

解决方案

在 select 查询中使用 join like.注意表 opd 必须有一个 pward 的连接字段才能使这个查询工作，我假设床表名称是 bed_table

INSERT INTO `ipdKayaChikitsaF`(`IPDNo`、`OPDNo`、`RegNo`、`PatientId`、`PDep`、`PWard`、`PBed`、`pSymptom`、`PDisease`、`PName`、`PMob`、`uid`、`PSex`、`Page`、`PMStatus`、`PFname`、`PAddr`、`PDist`、`PState`、`OPDDate`、`dischargeStatus`、`admitOn`、`dischargeOn`、`CreatedOn`、`CreatedBy`)SELECT '1',o.OPDNo,o.RegNo,o.PatientId,o.PDep,'Kaya Chikitsa (Female)','1',o.pSymptom,'na',o.pname,o.PMob,o.uid,o.PSex,o.Page,o.PMStatus,o.PFname,o.PAddr,o.PDist,o.PState,o.OPDDate,'0',b.Ward,b.`Bed No`,o.OPDDate,'$user'从 `opd` as o 在 b.Ward = o.pward 上加入 bed_table b其中 OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`ORDER BY RAND() LIMIT $ipdKayaChikitsaF"b.Ward = 'Kaya Chikitsa (Female)' and b.status = '0';

由于您在 opd 表中没有关系字段，您可以使用简单的笛卡尔积来获取状态为 0 的病房 Kaya Chikitsa(女性)的确切值.此外，在上述查询中之前忘记替换静态病房和床位选择查询中的值，即 'Kaya Chikitsa (Female)','1' withb.Ward,b.床号.因此，您只获得了1"的值.结果中没有在床上.

 INSERT INTO `ipdKayaChikitsaF`(`IPDNo`、`OPDNo`、`RegNo`、`PatientId`、`PDep`、`PWard`、`PBed`、`pSymptom`、`PDisease`、`PName`、`PMob`、`uid`、`PSex`、`Page`、`PMStatus`、`PFname`、`PAddr`、`PDist`、`PState`、`OPDDate`、`dischargeStatus`、`admitOn`、`dischargeOn`、`CreatedOn`、`CreatedBy`)SELECT '1',o.OPDNo,o.RegNo,o.PatientId,o.PDep,b.Ward,b.`Bed No`,o.pSymptom,'na',o.pname,o.PMob,o.uid,o.PSex,o.Page,o.PMStatus,o.PFname,o.PAddr,o.PDist,o.PState,o.OPDDate,'0',o.OPDDate,'0000-00-00',o.OPDDate,'$user'从 `opd` 作为 o，(选择 Ward，`bed no` 形式 bed_table where Status = 0 and Ward = 'Kaya Chikitsa (Female)' )作为 b其中 OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`ORDER BY RAND() LIMIT $ipdKayaChikitsaF"b.Ward = 'Kaya Chikitsa (Female)' and b.status = '0';

My current query is:

INSERT INTO `ipdKayaChikitsaF`(`IPDNo`, `OPDNo`, `RegNo`, `PatientId`, `PDep`, `PWard`, `PBed`, `pSymptom`, `PDisease`, `PName`, `PMob`, `uid`, `PSex`, `PAge`, `PMStatus`, `PFname`, `PAddr`, `PDist`, `PState`, `OPDDate`, `dischargeStatus`, `admitOn`, `dischargeOn`, `CreatedOn`, `CreatedBy`) 
SELECT '1',OPDNo,RegNo,PatientId,PDep,'Kaya Chikitsa (Female)','1',pSymptom,'na',pname,PMob,uid,PSex, PAge,PMStatus,PFname,PAddr,PDist,PState,OPDDate,'0',OPDDate,'0000-00-00',OPDDate,'$user' 
from `opd` 
where OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`  
ORDER BY RAND() LIMIT $ipdKayaChikitsaF";

Above query is working properly:

Now kindly check select query mentioned in above query. Here I am selecting some values from table and some of them are hard codded. Now I want PBed value to be filled from a different table in which a condition should be there.

My bed table has 3 columns like

  Ward                 |   Bed No | Status
Kaya Chikitsa (Female)       1        0
Kaya Chikitsa (Female)       2        0
Kaya Chikitsa (Female)       3        1
Kaya Chikitsa (Male)         4        0
Kaya Chikitsa (Male)         5        1
Kaya Chikitsa (Male)         6        0

I want to fill PBed with Bed No. from bed table and it should have condition of Ward='Kaya Chikitsa (Female)' and Status=0;

My result should be like this

Table ipdKayaChikitsaF

`IPDNo` | `OPDNo` | `RegNo` | `PatientId`  |   `PDep    |     `PWard`          | `PBed`  and so on...

1          267       1          12          Kaya Chikitsa  Kaya Chikitsa (Female)   1    and so on...
2          352       8          15          Kaya Chikitsa  Kaya Chikitsa (Female)   2    and so on...
and so on...

Kindly check above table her I want PBed column to be filled from bed table as conditions mentioned above.

解决方案

Use join in select query like. Note table opd must have a join field of pward for this query to work and I have assumed that the bed table name is bed_table

INSERT INTO `ipdKayaChikitsaF`(`IPDNo`, `OPDNo`, `RegNo`, `PatientId`, `PDep`, `PWard`, `PBed`, `pSymptom`, `PDisease`, `PName`, `PMob`, `uid`, `PSex`, `PAge`, `PMStatus`, `PFname`, `PAddr`, `PDist`, `PState`, `OPDDate`, `dischargeStatus`, `admitOn`, `dischargeOn`, `CreatedOn`, `CreatedBy`) 
SELECT '1',o.OPDNo,o.RegNo,o.PatientId,o.PDep,'Kaya Chikitsa (Female)','1',o.pSymptom,'na',o.pname,o.PMob,o.uid,o.PSex,o. PAge,o.PMStatus,o.PFname,o.PAddr,o.PDist,o.PState,o.OPDDate,'0',b.Ward, b.`Bed No`,o.OPDDate,'$user' 
from `opd` as o join bed_table b on b.Ward = o.pward
where OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`  
ORDER BY RAND() LIMIT $ipdKayaChikitsaF" and b.Ward = 'Kaya Chikitsa (Female)' and b.status = '0';

As you have no relation field in opd table you can use simple cartesian product to get that exact value of ward Kaya Chikitsa (Female) with status 0. Also, In the above query previously forgot to replace the static ward and bed no value in select query i.e 'Kaya Chikitsa (Female)','1' withb.Ward,b.Bed No. Therefore you were getting only value of "1" in bed no in the results.

    INSERT INTO `ipdKayaChikitsaF`(`IPDNo`, `OPDNo`, `RegNo`, `PatientId`, `PDep`, `PWard`, `PBed`, `pSymptom`, `PDisease`, `PName`, `PMob`, `uid`, `PSex`, `PAge`, `PMStatus`, `PFname`, `PAddr`, `PDist`, `PState`, `OPDDate`, `dischargeStatus`, `admitOn`, `dischargeOn`, `CreatedOn`, `CreatedBy`) 
    SELECT '1',o.OPDNo,o.RegNo,o.PatientId,o.PDep,b.Ward,b.`Bed No`,o.pSymptom,'na',o.pname,o.PMob,o.uid,o.PSex,o. PAge,o.PMStatus,o.PFname,o.PAddr,o.PDist,o.PState,o.OPDDate,'0',o.OPDDate,'0000-00-00',o.OPDDate,'$user' 
    from `opd` as o, (select Ward, `bed no` form bed_table where Status = 0 and Ward = 'Kaya Chikitsa (Female)' ) as b
    where OPDDate=STR_TO_DATE('$curDate','%Y-%m-%d 00:00:00') AND PDep='Kaya Chikitsa' AND PSex=0 GROUP BY `OPDNo`  
    ORDER BY RAND() LIMIT $ipdKayaChikitsaF" and b.Ward = 'Kaya Chikitsa (Female)' and b.status = '0';

这篇关于输入来自不同 mysql 表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！