多行子查询mysql

3htmauhk 于 2021-06-17 发布在 Mysql

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

我需要此查询的帮助我不知道如何执行子查询，但我正在尝试执行以下操作： Date , HNO , IDNAM , IDFNAM , Pre weight , post weight , Calculation pre weight - post weight 我遇到的问题是如何将数据存储在表中的weight列中，请参见下面的示例数据from database table，在weight同一列中有pre的一行，然后是post的另一行

28/04/2017,D123456,BLOGGS,Joe,HOSP HD,79.8,POST
28/04/2017,D123456,BLOGGS,Joe,HOSP HD,80.4,PRE
26/04/2017,D123456,BLOGGS,Joe,HOSP HD,79.2,POST
26/04/2017,D123456,BLOGGS,Joe,HOSP HD,80.2,PRE
21/04/2017,D123456,BLOGGS,Joe,HOSP HD,78.6,POST
21/04/2017,D123456,BLOGGS,Joe,HOSP HD,82.8,PRE

我想达到下面这个结果

28/04/2017|D123456|BLOGGS|Joe|HOSP HD|80.4 Pre|79.8 Post|calculation using alias
26/04/2017|D123456|BLOGGS|Joe|HOSP HD|80.2 Pre|79.2 Post|calculation using alias
21/04/2017|D123456|BLOGGS|Joe|HOSP HD|82.8 Pre|78.6 Post|calculation

SELECT DATE_FORMAT(D1.HRDATE, '%d/%m/%Y') AS Date, IDHNO, IDNAM, IDFNAM,IDSTS,
(SELECT HRWGT FROM p_dialysis D2 WHERE D2.oid = D1.oid AND HRPrePost LIKE 'pre' AND HRWGT is not null) AS Pre,
(SELECT HRWGT FROM p_dialysis D3 WHERE D3.oid = D1.oid AND HRPrePost LIKE 'post' AND HRWGT is not null) AS Post,
(SELECT (SELECT Pre) - (SELECT Post)) AS Total FROM p_dialysis D1
    LEFT JOIN p_person ON D1.fk_oid = p_person.oid WHERE D1.HRDATE BETWEEN STR_TO_DATE('01/04/2017', '%d/%m/%Y') AND STR_TO_DATE('30/04/2017', '%d/%m/%Y') AND IDHNO LIKE 'D000000%' AND D1.HRWGT > 0

上面的代码产生这个结果

28/04/2017    D123456 BLOGGS  Joe HOSP HD NULL    79.8    
28/04/2017    D123456 BLOGGS  Joe HOSP HD 80.4    NULL    
26/04/2017    D123456 BLOGGS  Joe HOSP HD NULL    79.2    
26/04/2017    D123456 BLOGGS  Joe HOSP HD 80.2    NULL        
21/04/2017    D123456 BLOGGS  Joe HOSP HD NULL    78.6    
21/04/2017    D123456 BLOGGS  Joe HOSP HD 82.8    NULL

mysql

来源：https://stackoverflow.com/questions/53766360/multiple-row-subquery-mysql

1条答案

按热度按时间

ocebsuys1#

我对总表模式有点困惑，但要得到结果，可以将表本身作为子查询连接起来以获得结果

SELECT DATE_FORMAT(pre.HRDATE, '%d/%m/%Y') AS Date, pre.IDHNO, pre.IDNAM, pre.IDFNAM, pre.IDSTS,  CONCAT(pre.HRPrePost, ' ' ,pre.HRWGT), CONCAT(pst.HRPrePost,' ' ,pst.HRWGT)
FROM your_table pre
    INNER JOIN(
    SELECT DATE_FORMAT(HRDATE, '%d/%m/%Y') AS Date, IDHNO, IDNAM, IDFNAM, IDSTS, HRPrePost, HRWGT
    FROM your_table 
        WHERE HRPrePost = 'POST'
) pst
ON pst.`Date` = DATE_FORMAT(pre.HRDATE, '%d/%m/%Y') AND pre.IDHNO = pst.IDHNO

WHERE pre.HRPrePost = 'PRE'

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

我来回答

多行子查询mysql

1条答案

相关问题

热门标签

最新问答