Mysql
如何將行轉換為列並在 Mysql 中對其進行查詢?
我有三個表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 更改
EXISTS
為JOIN
: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中的所有內容