当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL query - make conditions conditional?

问题描述:

I have two tables (these are just the simplified versions):

`Locations`:

id city country area

-- ---- ------- ----

71 C X E

72 C B E

73 C F G

74 L X E

75 M N O

76 M N E

`Findings`:

ID city country area resultVM

-- ---- ------- ---- --------

18 empty

19 C lam

20 X lam8

21 E pdg4

Now, I want to write an insert that fills up a third table, according to the data in these two. It should contain all the ids from the Location table once (Summed.locations_id is a ref to Locations.id obviously), and a matched resultVM from the Findings table. Result should be:

`Summed`:

locations_id resultVM

------------ --------

71 lam

72 lam

73 lam

74 lam8

75 empty

76 pdg4

The criteria are: first try matching the city to find a resultVM in the Findings table, if there is no result then match the country, then the area, and finally if there are no results at all then the empty line where no city/country/area is filled.

Currently I am doing this with 4 inserts/updates on duplicate keys, but since the tables are very large that takes a lot of time. Does anyone know if this can be done in a single update/insert/anything (in MySQL)?

Thanks!

网友答案:

You can do left join.

 INSERT INTO Summed (locations_id,resultVM)
    SELECT Locations.`id` AS locations_id,(CASE WHEN Findings_city.`city` IS NOT NULL THEN Findings_city.`resultVM`
                           WHEN Findings_country.`country` IS NOT NULL THEN Findings_country.`resultVM`
                           WHEN Findings_area.`area` IS NOT NULL THEN Findings_area.`resultVM`
                            ELSE 'empty'END) AS resultVM
    FROM
    Locations 
    LEFT JOIN Findings Findings_city ON Locations.`city` = Findings_city.`city`
    LEFT JOIN Findings Findings_country ON Locations.`country` = Findings_country.`country`
    LEFT JOIN Findings Findings_area ON Locations.`area` = Findings_area.`area`
    GROUP BY Locations.`id`

Here is SQL Fiddle Demo Hope this helps..

网友答案:

Try:

INSERT INTO `Summed`
SELECT
  `l`.`id`,
  COALESCE(
    `fcity`.`resultVM`,
    `fcountry`.`resultVM`,
    `farea`.`resultVM`,
    `fempty`.`resultVM`)
FROM
  `Locations` `l`
    LEFT JOIN `Findings` `fcity` ON `l`.`city` = `fcity`.`city`
    LEFT JOIN `Findings` `fcountry` ON `l`.`country` = `fcountry`.`country`
    LEFT JOIN `Findings` `farea` ON `l`.`area` = `farea`.`area`
    LEFT JOIN `Findings` `fempty` ON `fempty`.`city` = '' AND
                         `fempty`.`country` = '' AND
                         `fempty`.`area` = '';

SQL Fiddle demo

分享给朋友:
您可能感兴趣的文章:
随机阅读: