SELECT c.id, c.name, c.title, c.parent_title
FROM (
SELECT a.id, a.name, a.title, a.parent_title, b.id AS temp
FROM menu_item_node_v AS a LEFT JOIN sam_role_menu_v2 AS b WHERE a.id=b.id AND a.parent_name<>'no' AND b.role_id='bzr'
) AS c
WHERE c.temp is null提示的错误:消息 156,级别 15,状态 1,第 4 行
关键字 'WHERE' 附近有语法错误。就是要提取a表中id值不在b表中出现的记录,是怎么错的呢?
FROM (
SELECT a.id, a.name, a.title, a.parent_title, b.id AS temp
FROM menu_item_node_v AS a LEFT JOIN sam_role_menu_v2 AS b WHERE a.id=b.id AND a.parent_name<>'no' AND b.role_id='bzr'
) AS c
WHERE c.temp is null提示的错误:消息 156,级别 15,状态 1,第 4 行
关键字 'WHERE' 附近有语法错误。就是要提取a表中id值不在b表中出现的记录,是怎么错的呢?
FROM menu_item_node_v a
WHERE a.parent_name<>'no'
AND not exists(select 1 from sam_role_menu_v2 b where b.role_id='bzr' and b.id=a.id)
FROM (
SELECT a.id, a.name, a.title, a.parent_title, b.id AS temp
FROM menu_item_node_v AS a LEFT JOIN sam_role_menu_v2 AS b on a.id=b.id AND a.parent_name <> 'no' AND b.role_id='bzr'
) AS c
WHERE c.temp is null外连接的where换成on就可以了,试试吧
FROM (
SELECT a.id, a.name, a.title, a.parent_title, b.id AS temp
FROM menu_item_node_v AS a LEFT JOIN sam_role_menu_v2 AS b WHERE a.id=b.id AND a.parent_name<>'no' AND b.role_id='bzr'
) AS c
WHERE c.temp is null改成试试
SELECT id,name,title,parent_title
FROM (
SELECT a.id, a.name, a.title, a.parent_title, b.id AS temp
FROM menu_item_node_v AS a LEFT JOIN sam_role_menu_v2 AS b
on a.id=b.id where a.parent_name<>'no' AND b.role_id='bzr'
)c
WHERE temp is null