sql—简化具有多个左连接的mysql查询

n6lpvg4x 于 2021-06-18 发布在 Mysql

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

我有一个表，其中包含引用其他多语言表的标记。我写了这样一个查询，它完成了任务，但并不优雅，而且非常冗长：

SELECT DISTINCT
    T1.ID as CountryID,
    T1.Type AS CountryType,
    T2.Text AS CountryText,
    T3.Text AS CountryTitle,
    T4.Text AS Heading1,
    T5.Text AS Heading2,
    T6.Text AS Heading3,
    T7.Text AS Heading4,
    T8.Text AS Heading5,
    T9.Text AS Heading6,
    T10.Text AS Heading7,
    T11.Text AS Heading8,
    T12.Text AS Heading9,
    T13.Text AS Heading10,
    T14.Text AS Heading11,
    T15.Text AS Heading12,
    T16.Text AS Heading13,
    T17.Text AS Heading14,
    T18.Text AS Heading15,
    T19.Text AS Heading16,
    T20.Text AS Heading17,
    T21.Text AS Heading18,
    T22.Text AS Heading19,
    T23.Text AS Heading20
FROM 
    Countrys AS T1
    LEFT JOIN EN_TEXT AS T2 ON T1.DESCRIPTION=T2.TAG
    LEFT JOIN EN_TEXT AS T3 ON CONCAT(T1.COUNTRYTAG,'_TITLE')=T3.TAG
    LEFT JOIN EN_TEXT AS T4 ON CONCAT(T1.COUNTRYTAG,'_HEADING_1')=T4.TAG
    LEFT JOIN EN_TEXT AS T5 ON CONCAT(T1.COUNTRYTAG,'_HEADING_2')=T5.TAG
    LEFT JOIN EN_TEXT AS T6 ON CONCAT(T1.COUNTRYTAG,'_HEADING_3')=T6.TAG
    LEFT JOIN EN_TEXT AS T7 ON CONCAT(T1.COUNTRYTAG,'_HEADING_4')=T7.TAG
    LEFT JOIN EN_TEXT AS T8 ON CONCAT(T1.COUNTRYTAG,'_HEADING_5')=T8.TAG
    LEFT JOIN EN_TEXT AS T9 ON CONCAT(T1.COUNTRYTAG,'_HEADING_6')=T9.TAG
    LEFT JOIN EN_TEXT AS T10 ON CONCAT(T1.COUNTRYTAG,'_HEADING_7')=T10.TAG
    LEFT JOIN EN_TEXT AS T11 ON CONCAT(T1.COUNTRYTAG,'_HEADING_8')=T11.TAG
    LEFT JOIN EN_TEXT AS T12 ON CONCAT(T1.COUNTRYTAG,'_HEADING_9')=T12.TAG
    LEFT JOIN EN_TEXT AS T13 ON CONCAT(T1.COUNTRYTAG,'_HEADING_10')=T13.TAG
    LEFT JOIN EN_TEXT AS T14 ON CONCAT(T1.COUNTRYTAG,'_HEADING_11')=T14.TAG
    LEFT JOIN EN_TEXT AS T15 ON CONCAT(T1.COUNTRYTAG,'_HEADING_12')=T15.TAG
    LEFT JOIN EN_TEXT AS T16 ON CONCAT(T1.COUNTRYTAG,'_HEADING_13')=T16.TAG
    LEFT JOIN EN_TEXT AS T17 ON CONCAT(T1.COUNTRYTAG,'_HEADING_14')=T17.TAG
    LEFT JOIN EN_TEXT AS T18 ON CONCAT(T1.COUNTRYTAG,'_HEADING_15')=T18.TAG
    LEFT JOIN EN_TEXT AS T19 ON CONCAT(T1.COUNTRYTAG,'_HEADING_16')=T19.TAG
    LEFT JOIN EN_TEXT AS T20 ON CONCAT(T1.COUNTRYTAG,'_HEADING_17')=T20.TAG
    LEFT JOIN EN_TEXT AS T21 ON CONCAT(T1.COUNTRYTAG,'_HEADING_18')=T21.TAG
    LEFT JOIN EN_TEXT AS T22 ON CONCAT(T1.COUNTRYTAG,'_HEADING_19')=T22.TAG
    LEFT JOIN EN_TEXT AS T23 ON CONCAT(T1.COUNTRYTAG,'_HEADING_20')=T23.TAG
WHERE
    T1.Type='Country_ENGLAND';

有没有一种方法可以简化这个查询，使我不必拥有所有的左连接？不同的国家会有不同数量的标题，所以我必须得到所有可能的标题，以确保我得到他们所有。
像这样的事情-我知道这是行不通的，我只是举一个例子来说明我在努力做什么。

SELECT DISTINCT
T1.ID as CountryID,
T1.Type AS CountryType,
T2.Text AS CountryText,
T3.Text AS CountryTitle,
T4.Text AS Headings?,

FROM 
Countrys AS T1
LEFT JOIN EN_TEXT AS T2 ON T1.DESCRIPTION=T2.TAG
LEFT JOIN EN_TEXT AS T3 ON CONCAT(T1.COUNTRYTAG,'_TITLE')=T3.TAG
LEFT JOIN EN_TEXT AS T4 ON CONCAT(T1.COUNTRYTAG,'_HEADING_%')=T4.TAG AND T4.TAG IS NOT NULL,
WHERE
T1.Type='Country_ENGLAND';

sql mysql

来源：https://stackoverflow.com/questions/53261880/simplifying-a-mysql-query-with-many-left-joins

1条答案

按热度按时间

k4ymrczo1#

您可以使用条件聚合来透视标记

SELECT DISTINCT
    C.ID as CountryID,
    C.Type AS CountryType,
    MAX(CASE WHEN C.DESCRIPTION = T.TAG THEN T.Text END) AS CountryText,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_TITLE') THEN T.Text END) AS CountryTitle,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_1') THEN T.Text END) AS Heading1,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_2') THEN T.Text END) AS Heading2,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_3') THEN T.Text END) AS Heading3,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_4') THEN T.Text END) AS Heading4,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_5') THEN T.Text END) AS Heading5,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_6') THEN T.Text END) AS Heading6,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_7') THEN T.Text END) AS Heading7,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_8') THEN T.Text END) AS Heading8,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_9') THEN T.Text END) AS Heading9,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_10') THEN T.Text END) AS Heading10,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_11') THEN T.Text END) AS Heading11,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_12') THEN T.Text END) AS Heading12,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_13') THEN T.Text END) AS Heading13,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_14') THEN T.Text END) AS Heading14,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_15') THEN T.Text END) AS Heading15,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_16') THEN T.Text END) AS Heading16,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_17') THEN T.Text END) AS Heading17,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_18') THEN T.Text END) AS Heading18,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_19') THEN T.Text END) AS Heading19,
    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_20') THEN T.Text END) AS Heading20
FROM Countrys AS C
LEFT JOIN EN_TEXT AS T 
  ON T.TAG LIKE CONCAT(C.COUNTRYTAG,'%')
  -- AND (T.TAG LIKE '%TITLE' OR T.TAG LIKE '%HEADING%')
WHERE C.Type = 'Country_ENGLAND'
GROUP BY C.ID, C.Type;

嗯，还是有点冗长。
但它只需要1个连接，所以应该更快。
在案例的标准中，你可以用 LIKE .
因为 ON 子句已确保countrytag。
f、 e。

... MAX(CASE WHEN T.TAG LIKE '%HEADING_1' THEN T.Text END) AS Heading1, ...

但你得冒着事先计算好的风险 LIKE 下划线 _ 是任意1个字符的占位符。

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

我来回答

sql—简化具有多个左连接的mysql查询

1条答案

相关问题

热门标签

最新问答