code duplication in sql case statements
Hi I'm trying to output something like the following but am finding that there is a lot of code duplication going on.
| australian_has_itch | kiwi_has_itch |
| yes | no |
| no 开发者_JAVA百科 | n/a |
| n/a | no |
...
My query looks like this with two case statements that do the same thing but flip the country (my real query has 5 of these case statements):
SELECT
CASE
WHEN
NOT EXISTS (
SELECT person_id
FROM people_with_skin
WHERE people_with_skin.person_id = people.person_id
AND people.country = "Australia"
)
THEN 'N/A'
WHEN
EXISTS (
SELECT person_id
FROM itch_none_to_report
WHERE people.country = "Australia"
AND person_id = people.person_id
)
THEN 'None to report'
WHEN
EXISTS (
SELECT person_id
FROM itchy_people
WHERE people.country = "Australia"
AND person_id = people.person_id
)
THEN 'Yes'
ELSE 'No'
END australian_has_itch,
CASE
WHEN
NOT EXISTS (
SELECT person_id
FROM people_with_skin
WHERE people_with_skin.person_id = people.person_id
AND people.country = "NZ"
)
THEN 'N/A'
WHEN
EXISTS (
SELECT person_id
FROM itch_none_to_report
WHERE people.country = "NZ"
AND person_id = people.person_id
)
THEN 'None to report'
WHEN
EXISTS (
SELECT person_id
FROM itchy_people
WHERE people.country = "NZ"
AND person_id = people.person_id
)
THEN 'Yes'
ELSE 'No'
END kiwi_has_itch,
FROM people
Is there a way for me to condense this somehow and not have so much code duplication?
Thanks!
Use:
SELECT CASE
WHEN x.personid IS NOT NULL AND x.country = 'Australia' THEN 'N/A'
WHEN y.personid IS NOT NULL AND y.country = 'Australia' THEN 'None to report'
ELSE 'No'
END AS australian_has_itch,
CASE
WHEN x.personid IS NOT NULL AND x.country = 'NZ' THEN 'N/A'
WHEN y.personid IS NOT NULL AND y.country = 'NZ' THEN 'None to report'
ELSE 'No'
END AS australian_has_itch
FROM PEOPLE p
LEFT JOIN (SELECT DISTINCT
pws.person_id,
p.country
FROM people_with_skin pws
JOIN PEOPLE p ON p.person_id = pws.person_id) x ON x.person_id = p.person_id
LEFT JOIN (SELECT DISTINCT
intr.person_id,
p.country
FROM itch_none_to_report intr
JOIN PEOPLE p ON p.personid = intr.personid) y ON y.person_id = p.person_id
精彩评论