更新:这已解决,我犯了一个语法错误。
是否可以在左连接中联接和筛选两列? 例如:
tbl_people
id food side value
a pizza fries 10
b pizza shake 2
c burger fries 3
tbl_sides
food side
pizza fries
burger fries
然后使用 SQL:
SELECT
id, food, side, value
FROM
tbl_people AS people
LEFT JOIN
tbl_sides AS sides ON sides.food = people.food
AND sides.side = people.side
我可以添加一个标志,以便我可以确定食物对是否联接或是否为 NULL? 我不想内部连接,因为我需要计算每人的食物/配菜总数,以及每人匹配的食物/配菜对。 我试过了:
SELECT
id, food, side, value,
CASE WHEN
side.side IS NOT NULL
AND side.food IS NOT NULL
THEN 1
ELSE 0
END AS match_flag
FROM
tbl_people AS people
LEFT JOIN
tbl_sides AS sides ON sides.food = people.food
AND sides.side = people.side
但它不起作用。 基本上,我只需要在未应用连接时进行标记,但我遇到了麻烦。
我想你想要的是这个:
SELECT
id, food, side, value,
CASE WHEN
side.side = people.side
THEN 1
ELSE 0
END AS match_flag
FROM
tbl_people AS people
LEFT JOIN
tbl_sides AS sides ON people.food = sides.food
使用 MySQL,表达式将返回布尔值 true/false 1/0,在查找布尔输出时用作速记CASE
语句。
这将用于标记与 1 不匹配的情况:
SELECT
people.id, people.food, people.side, people.value
,sides.food IS NULL AS match_flag
FROM
tbl_people AS people
LEFT JOIN tbl_sides AS sides
ON sides.food = people.food
AND sides.side = people.side
演示:SQL 小提琴
或者这样做将不匹配标记为 0:
SELECT
people.id, people.food, people.side, people.value
,COALESCE(sides.food = people.food,0) AS match_flag
FROM
tbl_people AS people
LEFT JOIN tbl_sides AS sides
ON sides.food = people.food
AND sides.side = people.side
演示:SQL 小提琴
尝试在连接之前标记空值
SELECT
people.id, people.food, people.side, people.value, nvl(sides.side, 'NOT JOINED')
FROM
tbl_people AS people
LEFT JOIN
(select food, nvl(side, 'SOME NULL') as side from tbl_sides) AS sides
ON sides.food = people.food
AND sides.side = people.side
当表达式为 NULL 时,它返回 1,否则返回 0。
http://sqlfiddle.com/#!2/cadbdd/15/0
SELECT *, ISNULL(sides.food) AS match_flag
FROM
tbl_people AS people
LEFT JOIN
tbl_sides AS sides ON sides.food = people.food
AND sides.side = people.side