我有一张表 用的联合主键 字段分别是 uid edition_uid
我现在要根据 edition_uid的2个值如 ‘xxx’,‘yyy’
实现一个全连接的效果即
xx yy
1 1
2 null
null 3
这样的效果 语句如何写
附上我写的错误语句
SELECT
fi1.content ccc,
fi2.content yyy
FROM
format_item fi1
Full JOIN //不支持?
format_item fi2
ON
fi1.uid = fi2.uid
WHERE
fi1.edition_uid='xxx'
AND
fi2.edition_uid='yyy'
ORDER BY
fi1.create_time,fi2.create_time
我现在要根据 edition_uid的2个值如 ‘xxx’,‘yyy’
实现一个全连接的效果即
xx yy
1 1
2 null
null 3
这样的效果 语句如何写
附上我写的错误语句
SELECT
fi1.content ccc,
fi2.content yyy
FROM
format_item fi1
Full JOIN //不支持?
format_item fi2
ON
fi1.uid = fi2.uid
WHERE
fi1.edition_uid='xxx'
AND
fi2.edition_uid='yyy'
ORDER BY
fi1.create_time,fi2.create_time
用LEFT JOIN+RIGHT JOIN来解决
fi1.content ccc,
fi2.content yyy
FROM
format_item fi1
LEFT JOIN
format_item fi2
ON
fi1.uid = fi2.uid
WHERE
fi1.edition_uid='xxx'
AND
fi2.edition_uid='yyy'
UNIONSELECT
fi1.content ccc,
fi2.content yyy
FROM
format_item fi1
RIGHT JOIN
format_item fi2
ON
fi1.uid = fi2.uid
WHERE
fi1.edition_uid='xxx'
AND
fi2.edition_uid='yyy'
ORDER BY
fi1.create_time,fi2.create_time
是不是把where 后面的条件连同连接条件作为一个现在这种情况查出来只是xxx 和yyy 中uid相同的
也就是效果只有xx yy1 1
这种
03d06209695c4b37ab3fee8ce3aa5cbd 收拾收拾 收拾收拾
1a82cf4b08f74bc0a5e0a3af894fe049 得到地地道道地地道道的 得到地地道道地地道道的
8854102286b243d9b205602a831ed2b1 新建节次 新建节次这里只有fi2 的数据fi1 还有2条没出来
uid edition_uid doc_uid project_id preview_content content parent_node_uid parent_node_edition ord item_type is_test is_affirm is_section change_type change_uid priority tag is_deleted creator create_time approver approve_time module test_purpose precondition
1a82cf4b08f74bc0a5e0a3af894fe049 1bd8dbe6696d4c698ea0be3929102081 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 得到地地道道地地道道的 得到地地道道地地道道的 8854102286b243d9b205602a831ed2b1 1bd8dbe6696d4c698ea0be3929102081 1000 101 0 0 0 1 \N 1 1 0 363 2010-03-04 11:57:32 \N \N \N \N \N
6c3cb2507d204fbb8a6e99895555c748 1bd8dbe6696d4c698ea0be3929102081 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 是大方过 是大方过 8854102286b243d9b205602a831ed2b1 1bd8dbe6696d4c698ea0be3929102081 2000 101 0 0 0 2 \N 1 3 0 363 2010-03-04 11:58:28 \N \N \N \N \N
03d06209695c4b37ab3fee8ce3aa5cbd 1bd8dbe6696d4c698ea0be3929102081 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 收拾收拾 收拾收拾 8854102286b243d9b205602a831ed2b1 1bd8dbe6696d4c698ea0be3929102081 2000 101 0 0 0 4 \N 1 2 1 363 2010-03-04 11:57:56 \N \N \N \N \N
8854102286b243d9b205602a831ed2b1 1bd8dbe6696d4c698ea0be3929102081 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 新建节次 新建节次 \N \N 1000 101 0 0 1 2 \N 1 \N 0 363 2010-03-04 11:57:32 \N \N \N \N \N
f2uid edition_uid doc_uid project_id preview_content content parent_node_uid parent_node_edition ord item_type is_test is_affirm is_section change_type change_uid priority tag is_deleted creator create_time approver approve_time module test_purpose precondition
03d06209695c4b37ab3fee8ce3aa5cbd 13204882274211dfbc3623e515b75689 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 收拾收拾 收拾收拾 8854102286b243d9b205602a831ed2b1 13204882274211dfbc3623e515b75689 2000 101 0 0 0 2 \N 1 2 0 363 2010-03-04 11:57:56 \N \N \N \N \N
1a82cf4b08f74bc0a5e0a3af894fe049 13204882274211dfbc3623e515b75689 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 得到地地道道地地道道的 得到地地道道地地道道的 8854102286b243d9b205602a831ed2b1 13204882274211dfbc3623e515b75689 1000 101 0 0 0 1 \N 1 1 0 363 2010-03-04 11:57:32 \N \N \N \N \N
8854102286b243d9b205602a831ed2b1 13204882274211dfbc3623e515b75689 b5e5608c7a1b4a65b1ed59c9b98ae7bc 2340 新建节次 新建节次 \N 13204882274211dfbc3623e515b75689 1000 101 0 0 1 2 \N 1 \N 0 363 2010-03-04 11:57:32 \N \N \N \N \N
f1.content f2.content
1 1
2 2
3 3
4 null
null 5是这样一种结果