求个SQL

Question

数据MRT_C表
   WFHT          P_CODE      MASTREJOB
HCS1106-0038 PCA00100800   1105082
HCS1106-0038 PCA00101000   1105082
0000022         ZP120101401   090820POT_C表
   PORNO          CODE          WO
HCS1106-0038 PCA00100800  1105082
HCS1106-0038 PCA00101000  1105082
DJ0811-0034     MZM27002000  081102
要得到HCS1106-0038 PCA00100800   1105082
HCS1106-0038 PCA00101000   1105082
0000022         ZP120101401   090820
DJ0811-0034     MZM27002000  081102

Answer 1

select * from MRT_C表 left join  POT_C表 on MRT_C表.P_CODE=POT_C表.CODE

Answer 2

select col1=isnull(a.WFHT,b.PORNO),
       col2=isnull(a.P_CODE,b.CODE),
       col3=isnull(a.MASTREJOB,b.WO)
from MRT_C a full join POT_C b on a.WFHT=b.PORNO

Answer 3

col1                                                                                                 col2                                                         col3
---------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------------------------------------
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00101000                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00101000                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00101000                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082
HCS1106-0038                                                                                         PCA00101000                                                  1105082
HCS1106-0038                                                                                         PCA00100800                                                  1105082(12 行受影响)

Answer 4

select * from MRT_C
union 
select * from POT_C

Answer 5

select col1=isnull(a.WFHT,b.PORNO),
       col2=isnull(a.P_CODE,b.CODE),
       col3=isnull(a.MASTERJOB,b.WO)
from MRT_C a full join POT_C b on a.WFHT=b.PORNO WHERE b.PORNO='HCS1106-0038'

Answer 6

用5樓方法，把兩個結果集union連接

Answer 7

select * from MRT_C
union 
select * from POT_C
这个不错！

Answer 8

MRT_C表
   WFHT          P_CODE      MASTREJOB
HCS1106-0038    PCA00100800      1105082
HCS1106-0038    PCA00101000      1105082
0000022         ZP120101401   090820POT_C表
   PORNO          CODE          WO
HCS1106-0038    PCA00100800     1105082
HCS1106-0038    PCA00101000     1105082
DJ0811-0034     MZM27002000  081102
当条件  WFHT=PORNO AND P_CODE=CODE AND MASTREJOB=WO
就显示
HCS1106-0038    PCA00100800     1105082
HCS1106-0038    PCA00101000     1105082
不等于就显示
0000022         ZP120101401   090820
DJ0811-0034     MZM27002000  081102

Answer 9

如果两个表的数据条件等于的情况下就显示一条，不等于呢就显示两个表的数据。感觉逻辑好像有点不对，但是上面说就要这样显示。不知道SQL能不能实现?

Answer 10

select * from MRT_C
union 
select * from POT_C where wo='081102'

Answer 11

加上distinct ？
这个不是去重复数据的嘛？

Answer 12

用union（不是union all）应该是比较好的。 full join会比较麻烦，因为会产生是null的列，所以需要进行判断，才能得到所需的结果