我正在使用子查詢和JOINs 處理復雜的 MySQL 查詢，這是我的查詢：
SELECT
id,
ancient_source_name,
ancient_source_name_alt,
ancient_source_type,
ancient_source_type_id,
IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material,
ancient_source_year,
dynasty,
ancient_period,
author,
IFNULL(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), '-') AS provenance_loc,
IFNULL(CONCAT_WS(', ',
IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
), '-') AS current_loc
FROM (
SELECT
hgn_ancient_sources.id AS id,
ancient_source_name,
IFNULL(ancient_source_name_alt, '-') AS ancient_source_name_alt,
IFNULL(ancient_source_year, '-') AS ancient_source_year,
ancient_source_type,
ancient_source_type_id,
ancient_source_material,
dynasty,
ancient_period,
provenance_countries.country AS provenance_country,
provenance_localities.locality AS provenance_locality,
provenance_places.place AS provenance_place,
current_countries.country AS current_country,
current_localities.locality AS current_locality,
current_places.place AS current_place,
IFNULL(author_name, '-') AS author
FROM hgn_ancient_sources
JOIN hgn_ancient_source_types ON hgn_ancient_sources.ancient_source_type_id = hgn_ancient_source_types.id
JOIN hgn_ancient_source_materials ON hgn_ancient_sources.ancient_source_material_id = hgn_ancient_source_materials.id
JOIN hgn_dynasties ON hgn_ancient_sources.ancient_source_dynasty_id = hgn_dynasties.id
JOIN hgn_ancient_periods ON hgn_ancient_sources.ancient_source_period_id = hgn_ancient_periods.id
LEFT JOIN junc_ancient_source_has_author ON hgn_ancient_sources.id = junc_ancient_source_has_author.ancient_source_id
LEFT JOIN hgn_authors ON junc_ancient_source_has_author.author_id = hgn_authors.id
JOIN junc_place_has_location AS provenance_place_has_location ON hgn_ancient_sources.ancient_source_provenance_place_id = provenance_place_has_location.id
JOIN junc_place_has_location AS current_place_has_location ON hgn_ancient_sources.ancient_source_current_place_id = current_place_has_location.id
JOIN junc_locality_has_country AS provenance_locality_has_country ON provenance_place_has_location.location_id = provenance_locality_has_country.id
JOIN junc_locality_has_country AS current_locality_has_country ON current_place_has_location.location_id = current_locality_has_country.id
JOIN hgn_places AS provenance_places ON provenance_place_has_location.place_id = provenance_places.id
JOIN hgn_places AS current_places ON current_place_has_location.place_id = current_places.id
JOIN hgn_localities AS provenance_localities ON provenance_locality_has_country.locality_id = provenance_localities.id
JOIN hgn_localities AS current_localities ON current_locality_has_country.locality_id = current_localities.id
JOIN hgn_countries AS provenance_countries ON provenance_locality_has_country.country_id = provenance_countries.id
JOIN hgn_countries AS current_countries ON current_locality_has_country.country_id = current_countries.id
) AS subquery
除了這部分代碼外，一切正常：
IFNULL(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), '-') AS provenance_loc,
IFNULL(CONCAT_WS(', ',
IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
), '-') AS current_loc
我收到了良好的效果時，有1/3或2/3列用Unknown或Not Applicable價值，但是當3/3列（例如current_country，current_locality，current_place）有這兩個值中的一個，我得到空單元格作為一個結果，而不是-作為應該是可以預料的。
我在代碼中嘗試了不同的更改，但沒有任何效果。我應該提到，例如，查詢的這一部分：
IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material
作業完美，所以我的假設是CONCAT_WS()功能存在一些問題，但我找不到什么。
uj5u.com熱心網友回復：
CONCAT_WS()如果非空分隔符之后的所有引數都是 ，則回傳空字串NULL。
請參閱演示。
我沒有在MySql 的檔案中找到明確說明這一點（就像在SQL Server 的檔案中明確說明的那樣）。
您可以做的是還使用NULLIF()檢查結果是否為空字串并NULL在使用之前將其更改為IFNULL()：
IFNULL(NULLIF(CONCAT_WS(', ',
IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), ''), '-') AS provenance_loc
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/402234.html