我有一个问题。
现在我有三张桌子(宠物,地址,联系人):
USER TABLE COLUMNS:
PET_ID_PK
PETNAME
ADDRESS_ID_FK
CONTACT_ID_FK
GENDER
OWNER_ID
BOUGHT_DATE
ADDRESS TABLE COLUMNS:
ADDRESS_ID_PK
ADDRESS_STRING
CONTACT TABLE COLUMNS:
CONTACT_ID_PK
CONTACT_NUMBER
我想把这些表连接到一个有列的表中:
PET_ID_PK
PETNAME
ADDRESS_STRING
CONTACT_NUMBER
GENDER
我希望得到属于特定OWNER_ID的宠物,并按性别顺序显示行(先是女性,然后是男性)。在这种情况下,我可以使用ORDER BY case。
但在每个用户表条目中,ADDRESS_ID_FK或CONTACT_ID_FK都将是NULL值。因此,参考Unioning Two Tables with Different Number Of Columns中提供的一个解决方案,该解决方案可以抵消NULL值的列,我提出了以下查询:
SELECT DISTINCT ON (pets.pet_id_pk) * FROM
((SELECT pet_id_pk, petname, address_string, NULL AS contact_id_fk, gender
FROM user JOIN address ON address.address_id_pk=address_id_fk)
UNION
(SELECT pet_id_pk, petname, NULL AS address_string, contact_id_fk, gender
FROM user JOIN address ON contact.contact_id_pk=contact_id_fk) AS pets
WHERE OWNER_ID=$1 ORDER BY (CASE WHEN gender=female ELSE 2 END), bought_date DESC
但是,此查询无法相应地工作。
希望有人能帮我。非常感谢。
请尝试以下操作:
SELECT u.pet_id_pk, u.petname, a.address_string, c.contact_number, u.gender
FROM user u
LEFT JOIN address a ON u.address_id_fk=a.address_id_pk
LEFT JOIN contact c ON u.contact_id_fk=c.contact_id_pk
WHERE OWNER_ID=$1 ORDER BY u.gender,bought_date DESC
您可以使用LEFT JOIN。LEFT JOIN将显示表,即使没有匹配结果。
对于ORDER BY,我并不认为有必要使用CASE,因为只使用ORDER BY时,它将显示FEMALE后跟MALE。您使用ORDER BY CASE有什么具体的原因吗?