SELECT /*+PARALLEL(A,B,5)*/
TO_CHAR('201106'), TO_CHAR('2101'), A.SERVICE_ID, A.NEW_FLAG
FROM (SELECT /*+PARALLEL(T,5)*/
T.SERVICE_ID, T.NEW_FLAG
FROM EDW_M.EDW_M_CUST_SERV_FLAG_GSM_M@LNDW T
WHERE T.ACCT_MONTH = TO_CHAR(TO_CHAR('201106'))
AND T.AREA_NO = TO_CHAR('2101')
AND T.SERVICE_TYPE = '112511'
AND T.IF_3G = '1'
AND T.SUB_SERVICE_TYPE = '70'
AND T.ACCT_FLAG = '1'
AND T.SERVICE_ID IN
('40851463', '43058493', '50519397', '54206966', '54390125',
'54499906', '54500527', '54598967') --查询结果为8条
/*GROUP BY T.SERVICE_ID, T.NEW_FLAG */ --注释
) A,
(SELECT W.SERV_ID
FROM EDW_F.EDW_F_3G_HAPPY_USER_M@LNDW W
WHERE W.ACCT_MONTH = TO_CHAR('201106')
AND W.AREA_NO = TO_CHAR('2101')
AND W.NET_FLAG = '0' --3G
AND W.SERV_ID IN ('40851463', '43058493', '50519397', '54206966',
'54390125', '54499906', '54500527', '54598967')) B --查询结果为空
WHERE A.SERVICE_ID = B.SERV_ID(+)
AND B.SERV_ID IS NULL
--------------------------------------------------------------------------------------------------------SELECT /*+PARALLEL(A,B,5)*/
TO_CHAR('201106'), TO_CHAR('2101'), A.SERVICE_ID, A.NEW_FLAG
FROM (SELECT /*+PARALLEL(T,5)*/
T.SERVICE_ID, T.NEW_FLAG
FROM EDW_M.EDW_M_CUST_SERV_FLAG_GSM_M@LNDW T
WHERE T.ACCT_MONTH = TO_CHAR(TO_CHAR('201106'))
AND T.AREA_NO = TO_CHAR('2101')
AND T.SERVICE_TYPE = '112511'
AND T.IF_3G = '1'
AND T.SUB_SERVICE_TYPE = '70'
AND T.ACCT_FLAG = '1'
AND T.SERVICE_ID IN
('40851463', '43058493', '50519397', '54206966', '54390125',
'54499906', '54500527', '54598967') --查询结果为8条
GROUP BY T.SERVICE_ID, T.NEW_FLAG --打开注释
) A,
(SELECT W.SERV_ID
FROM EDW_F.EDW_F_3G_HAPPY_USER_M@LNDW W
WHERE W.ACCT_MONTH = TO_CHAR('201106')
AND W.AREA_NO = TO_CHAR('2101')
AND W.NET_FLAG = '0' --3G
AND W.SERV_ID IN ('40851463', '43058493', '50519397', '54206966',
'54390125', '54499906', '54500527', '54598967')) B --查询结果为空
WHERE A.SERVICE_ID = B.SERV_ID(+)
AND B.SERV_ID IS NULL********************************************************************************
正常的结果
201106 2101 54598967 0
201106 2101 43058493 0
201106 2101 54390125 0
201106 2101 50519397 1
201106 2101 54499906 0
201106 2101 54500527 0
201106 2101 54206966 0
201106 2101 40851463 0问题一
打开注释和注上注释 查询的结果应该都是一样的
现在发现的问题是 打开注释查询结果为空,注上注释查询出8条结果问题二
换了一个库登陆进行查询
结果发现打开注释与注上注释均查询出8条记录。
麻烦高手帮忙解释一下是怎么回事,是不是库的配置有问题。非常感谢!
TO_CHAR('201106'), TO_CHAR('2101'), A.SERVICE_ID, A.NEW_FLAG
FROM (SELECT /*+PARALLEL(T,5)*/
T.SERVICE_ID, T.NEW_FLAG
FROM EDW_M.EDW_M_CUST_SERV_FLAG_GSM_M@LNDW T
WHERE T.ACCT_MONTH = TO_CHAR(TO_CHAR('201106'))
AND T.AREA_NO = TO_CHAR('2101')
AND T.SERVICE_TYPE = '112511'
AND T.IF_3G = '1'
AND T.SUB_SERVICE_TYPE = '70'
AND T.ACCT_FLAG = '1'
AND T.SERVICE_ID IN
('40851463', '43058493', '50519397', '54206966', '54390125',
'54499906', '54500527', '54598967') --查询结果为8条
/*GROUP BY T.SERVICE_ID, T.NEW_FLAG */ --注释
) A,
(SELECT W.SERV_ID
FROM EDW_F.EDW_F_3G_HAPPY_USER_M@LNDW W
WHERE W.ACCT_MONTH = TO_CHAR('201106')
AND W.AREA_NO = TO_CHAR('2101')
AND W.NET_FLAG = '0' --3G
AND W.SERV_ID IN ('40851463', '43058493', '50519397', '54206966',
'54390125', '54499906', '54500527', '54598967')) B --查询结果为空
WHERE A.SERVICE_ID = B.SERV_ID(+)
AND B.SERV_ID IS NULL
--------------------------------------------------------------------------------------------------------SELECT /*+PARALLEL(A,B,5)*/
TO_CHAR('201106'), TO_CHAR('2101'), A.SERVICE_ID, A.NEW_FLAG
FROM (SELECT /*+PARALLEL(T,5)*/
T.SERVICE_ID, T.NEW_FLAG
FROM EDW_M.EDW_M_CUST_SERV_FLAG_GSM_M@LNDW T
WHERE T.ACCT_MONTH = TO_CHAR(TO_CHAR('201106'))
AND T.AREA_NO = TO_CHAR('2101')
AND T.SERVICE_TYPE = '112511'
AND T.IF_3G = '1'
AND T.SUB_SERVICE_TYPE = '70'
AND T.ACCT_FLAG = '1'
AND T.SERVICE_ID IN
('40851463', '43058493', '50519397', '54206966', '54390125',
'54499906', '54500527', '54598967') --查询结果为8条
GROUP BY T.SERVICE_ID, T.NEW_FLAG --打开注释
) A,
(SELECT W.SERV_ID
FROM EDW_F.EDW_F_3G_HAPPY_USER_M@LNDW W
WHERE W.ACCT_MONTH = TO_CHAR('201106')
AND W.AREA_NO = TO_CHAR('2101')
AND W.NET_FLAG = '0' --3G
AND W.SERV_ID IN ('40851463', '43058493', '50519397', '54206966',
'54390125', '54499906', '54500527', '54598967')) B --查询结果为空
WHERE A.SERVICE_ID = B.SERV_ID(+)
AND B.SERV_ID IS NULL********************************************************************************
正常的结果
201106 2101 54598967 0
201106 2101 43058493 0
201106 2101 54390125 0
201106 2101 50519397 1
201106 2101 54499906 0
201106 2101 54500527 0
201106 2101 54206966 0
201106 2101 40851463 0问题一
打开注释和注上注释 查询的结果应该都是一样的
现在发现的问题是 打开注释查询结果为空,注上注释查询出8条结果问题二
换了一个库登陆进行查询
结果发现打开注释与注上注释均查询出8条记录。
麻烦高手帮忙解释一下是怎么回事,是不是库的配置有问题。非常感谢!
T.SERVICE_ID, T.NEW_FLAG
FROM EDW_M.EDW_M_CUST_SERV_FLAG_GSM_M@LNDW T
WHERE T.ACCT_MONTH = TO_CHAR(TO_CHAR('201106'))
AND T.AREA_NO = TO_CHAR('2101')
AND T.SERVICE_TYPE = '112511'
AND T.IF_3G = '1'
AND T.SUB_SERVICE_TYPE = '70'
AND T.ACCT_FLAG = '1'
AND T.SERVICE_ID IN
('40851463', '43058493', '50519397', '54206966', '54390125',
'54499906', '54500527', '54598967') --查询结果为8条
GROUP BY T.SERVICE_ID, T.NEW_FLAG --打开注释和不打开注释结果是不是一样??
登陆的库是@lndw 访问的也是@lndw的表 group by 与不是 group by 查询的结果一样
我登陆的@lndwres库 我访问的表带链路@lndw group by 与不是 group by 查询的结果不一样
带链路的,其实就是同一张表
结果却不一样 很是困惑