Select multiple values via CASE statement?

I have a Vehicle table with an Owner_ID field in it. This Owner_ID will correspond to either the ID field in a Customer table or the ID field in a Business table. I am looking to return different values depending on the table that the ID belongs to.

I have this statement:

SELECT v.Make, v.Model
FROM Vehicle v
LEFT JOIN Customer c ON c.ID = v.Owner_ID
LEFT JOIN Business b ON b.ID = v.Owner_ID

So say I want to return the customer surname if the Owner_ID = Customer.ID, and the business name if Owner_ID = Business_ID. I know I can use the CASE statement as such:

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN c.Surname
        WHEN b.ID IS NOT NULL THEN b.Name

But is there a way to get multiple values from one case statement? Something like: (I know this is completely wrong by the way.)

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN
            SELECT c.Surname, c.Date_Of_Birth
        WHEN b.ID IS NOT NULL THEN
            SELECT b.Name, b.Founded

Or will I have to do:

SELECT v.Make, v.Model,
    CASE WHEN c.ID IS NOT NULL THEN c.Surname
        WHEN b.ID IS NOT NULL THEN b.Name
    END AS "Name",
    CASE WHEN c.ID IS NOT NULL THEN c.Date_Of_Birth
        WHEN b.ID IS NOT NULL THEN b.Founded
    END AS "DOB/Founded"

---

No there is no way to get a branch of a case expression to span multiple columns.

In your example case you could do

SELECT v.Make,
       v.Model,
       MAX(COALESCE(c.Surname, b.name)) as name
FROM   Vehicle v
       LEFT JOIN Customer c
         ON c.ID = v.Owner_ID
       LEFT JOIN Business b
         ON b.ID = v.Owner_ID
GROUP  BY v.Owner_ID 

---

I think it needs to be clear what columns the query should return and this cannot change depending on the query. So the conditions have to be broken up per column.

What about IF ? Might be easier to read in this case.

SELECT v.Make, v.Model,
    IF(c.ID IS NOT NULL, c.Surname,       IF(b.ID IS NOT NULL, b.Name,    NULL)) as `Name`
    IF(c.ID IS NOT NULL, c.Date_Of_Birth, IF(b.ID IS NOT NULL, b.Founded, NULL)) as `DOB_Founded`

---

不确定，但你可以试试这个

SELECT v.Make, v.Model
FROM Vehicle v
LEFT JOIN Customer c ON c.ID = v.Owner_ID
LEFT JOIN Business b ON b.ID = v.Owner_ID 
WHERE c.Surname IS NOT NULL 
OR b.name IS NOT NULL

链接地址: http://www.djcxy.com/p/51408.html

上一篇: 如何指定是否拥有编组字符串的所有权？

下一篇: 通过CASE语句选择多个值？