0

## 找出兩個數據表中不存在的那些數據

`假設目前 user_id = 1`

ｕｃ數據表

``````prod_id / user_id
102 / 1
103 / 1
``````

ｃｓ數據表

``````prod_id / user_id
102 / 1
``````

``````SELECT
uc.prod_id
FROM `user_cart` as uc
JOIN `user_cart_stock` as cs ON cs.prod_id = uc.prod_id
WHERE uc.user_id = 1
AND cs.prod_id IS NULL
``````

something iT邦新手 5 級 ‧ 2018-10-28 17:10:06 檢舉

with cte_u_c as
(
select distinct
prod_id
,user_id
,1 as u_c
,0 as c_s
from uc
),

cte_c_s as
(
select distinct
prod_id
,user_id
,0 as u_c
,1 as c_s
from cs
),

cte_union as
(
select
*
from cte_u_c

union all

select
*
from cte_c_s
)

select
prod_id
,user_id
,sum(u_c) as u_c
,sum(c_s) as c_s
from
cte_union
group by
prod_id
,user_id

### 6 個回答

6
rogeryao
iT邦高手 1 級 ‧ 2018-10-28 16:51:14

Full Outer Join 讓我起雞母皮XDD

4

iT邦大師 1 級 ‧ 2018-10-28 18:20:55

0

iT邦新手 5 級 ‧ 2018-10-29 09:22:38

`````` select prod_id , user_id from user_cart
EXCEPT
select prod_id , user_id from user_cart_stock
``````

SELECT PROD_ID
FROM UC
WHERE USER_ID = 1
MINUS
SELECT PROD_ID
FROM CU
WHERE USER_ID = 1

``````SELECT DISTINCT prod_id , user_id
FROM user_cart as uc
WHERE (prod_id, user_id) NOT IN
(SELECT prod_id, user_id FROM user_cart_stock)
``````
0
watain666
iT邦新手 5 級 ‧ 2018-10-29 11:35:44

0
s20050253004
iT邦新手 5 級 ‧ 2018-10-29 14:40:16

SELECT PROD_ID
FROM UC
WHERE USER_ID = 1
MINUS
SELECT PROD_ID
FROM CU
WHERE USER_ID = 1

SELECT UC.PROD_ID
FROM UC, CU
WHERE CS.PROD_ID(+) = UC.PROD_ID AND UC.USER_ID = 1 AND CS.PROD_ID IS NULL;

0
ty201457
iT邦新手 5 級 ‧ 2018-10-29 19:12:45

uc.prod_id / uc.user_id / cs.prod_id / cs.user_id
102 / 1 / 102 / 1
103 / 1 / null /null

uc.prod_id / uc.user_id / cs.prod_id / cs.user_id
102 / 1 / 102 / 1

SELECT * FROM uc JOIN cs ON cs.prod_id = uc.prod_id

cs.prod_id = uc.prod_id
uc.prod_id = 103

cs.prod_id = null
so null

SELECT *
FROM uc
left JOIN cs ON cs.prod_id = uc.prod_id
WHERE uc.user_id = 1
AND cs.prod_id IS NULL