Mysql

如何將行轉換為列並在 Mysql 中對其進行查詢?

  • July 7, 2017

我有三個表Patient ,其中包含我的患者的姓名,Controls代表可以為每種疾病繪製的 UI 控制項,以及ControlsValues表,其中包含為每個 Patient 送出的控制項的值

讓我們有一些數據 患者

|ID  | Name |
|-----------|
| 1  | Ara  |
| 2  | Sada |

控製

|ID  | Text | Type     |
|-----------|----------|
| 1  | age  | textbox  |
| 2  |alergy| checkbox |

然後是我要查詢的controlsValues表

|ID  | contrlId | value    | patientId |
|---------------|----------|-----------|
| 1  | 1        | 23       | 1         |
| 2  | 2        | true     | 1         |
| 3  | 1        | 26       | 2         |
| 4  | 2        | false    | 2         |

在這裡,當我想從具有 ( AND ) 和 ( AND ) 的ControlsValues表中返回該患者時,就會出現我的問題,在這種情況下,條件是兩行而不是兩列,這是不可能的,所以我決定將行更改為列取決於但我不知道如何,我已經搜尋了 2 天並看到了很多樣本,但沒有一個能幫助我解決我的問題controlId=1``value=23``controlId=2``value=true``controlId

您可以嘗試這種“解決方案”:

http://sqlfiddle.com/#!9/d33a95/18

它不是“數據透視表”解決方案,但也許它可以解決您的問題 - 如果我理解正確的話。

以下是使用的表格/數據:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
); 
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES 
 (1, '23', 1), 
 (2, 'true', 1),
 (1, '25', 2),
 (2, 'false', 2),
 (1, '23', 3),
 (2, 'false', 3),
 (1, '23', 4), 
 (2, 'true', 4);

和查詢:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
  OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;

存在

您無需轉置數據即可獲得答案。最直接的方法(從我的角度來看)是 using EXISTS,它非常直接地反映了您的陳述:

SELECT
    p.ID, p.Name
FROM
    patients p
WHERE
   EXISTS 
   (
       SELECT 1
       FROM controlvalues cv1
       WHERE p.ID = cv1.PatientID
             AND cv1.controlId = 1
             AND cv1.value = '23'
   )
   AND
   EXISTS
   (
       SELECT 1
       FROM controlvalues cv2
       WHERE p.ID = cv2.PatientID
             AND cv2.controlId = 2
             AND cv2.value = 'true'
   ) ;

哪個會返回:

身份證 | 姓名
-: | :---
1 | 現在

加入

作為替代方案,您可以將 s 更改EXISTSJOIN

SELECT
    p.ID, p.Name
FROM
    patients p
    JOIN controlvalues cv1 
        ON cv1.PatientID = p.ID AND cv1.controlId = 1 AND cv1.value = '23'
    JOIN controlvalues cv2 
        ON cv2.PatientID = p.ID AND cv2.controlId = 2 AND cv2.value = 'true' ;

這將返回相同的結果。該查詢更簡潔,但在我看來有點難以閱讀。

只要定義了正確的索引(或者,在我的例子中,主鍵),執行計劃(從 MariaDB 10.2 開始)是相同的。


如果您真的想旋轉您的數據,您可以這樣做:

SELECT
   p.ID, p.Name,
   max(case when controlId = 1 then value end) AS controlId1 /* age */,
   max(case when controlId = 2 then value end) AS controlId2 /* allergy */
FROM
   patients p
   JOIN controlvalues cv ON cv.PatientId = p.ID 
GROUP BY
   p.ID ;

這是透視表:

身份證 | 姓名 | 控制ID1 | 控制ID2
-: | :--- | :--------- | :---------
1 | 阿拉 | 23 | 真的 
2 | 現在 | 25 | 錯誤的

…這就是你將如何使用它:

SELECT
   pp.ID, pp.Name
FROM
   (
   SELECT
       p.ID, p.Name,
       max(case when controlId = 1 then value end) AS controlId1,
       max(case when controlId = 2 then value end) AS controlId2
   FROM
       patients p
       JOIN controlvalues cv ON cv.PatientId = p.ID 
   GROUP BY
       p.ID 
   ) AS pp
WHERE
   pp.controlId1 = '23' AND pp.controlId2 = 'true';
身份證 | 姓名
-: | :---
1 | 現在

然而,透視表的執行計劃可能更糟(特別是在大表中)。

您可以在*此處*檢查dbfiddle中的所有內容

引用自:https://dba.stackexchange.com/questions/101381