我有 3 張桌子：
帳戶
| ID | 名稱 |
|---|---|
| 1 | 谷歌 |
| 2 | 蘋果 |
自定義欄位
| ID | 名稱 |
|---|---|
| 1 | 電話 |
| 2 | 電子郵件 |
custom_field_submission
| ID | custom_field_id | entity_id | 價值 |
|---|---|---|---|
| 1 | 1 | 1 | 555-444-333 |
| 2 | 1 | 2 | 111-111-111 |
| 3 | 2 | 1 | [email protected] |
| 4 | 2 | 2 | 蘋果@apple.com |
查詢后的預期結果
| ID | 名稱 | 電話 | 電子郵件 |
|---|---|---|---|
| 1 | 谷歌 | 555-444-333 | [email protected] |
| 2 | 蘋果 | 111-111-111 | 蘋果@apple.com |
我有一個這樣的查詢：
SELECT
a.id,
a.name,
phone.value as phone,
email.value as email
FROM account a
LEFT JOIN (
SELECT DISTINCT custom_field_submission.value, custom_field_submission.entity_id
FROM custom_field_submission
WHERE custom_field_submission.custom_field_id = 1) AS phone
ON phone.entity_id = a.id
LEFT JOIN (
SELECT DISTINCT custom_field_submission.value, custom_field_submission.entity_id
FROM custom_field_submission
WHERE custom_field_submission.custom_field_id = 2) AS email
ON email.entity_id = a.id
實際上，我有 20 個自定義欄位用于 10 000 個帳戶。我在哪里運行查詢它很慢（3-4 秒）
您有管理優化此功能的想法嗎？
謝謝。
uj5u.com熱心網友回復：
您在這里需要的是一個資料透視查詢：
SELECT
a.id,
a.name,
MAX(CASE WHEN cf.name = 'Phone' THEN cfs.value END) AS Phone,
MAX(CASE WHEN cf.name = 'Email' THEN cfs.value END) AS Email
FROM account a
LEFT JOIN custom_field_submission cfs
ON cfs.entity_id = a.id
LEFT JOIN custom_field cf
ON cf.id = cfs.custom_field_id
GROUP BY
a.id,
a.name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/380023.html
下一篇：SQLSTATE[HY000][2002]沒有那個檔案或目錄（SQL：select*frominformation_schema.tableswheretable_schema=homeandtabl