[SQL ] 算UNION後的個數
資料庫是MySQL 5.0.77
我現在有兩個資料表:
table1:
Id 店面
Tom A
Tom B
代表Tom有A, B店面
table2:
店面 倉庫
A C
A D
A E
B C
B D
B E
代表A店面有C, D倉庫; B店面有E倉庫
現在透過UNION已經能能顯示出下面這樣的結果:
Id 地點
Tom A
Tom B
Tom C
Tom D
Tom E
代表Tom擁有以下地點
我用這句sql:
SELECT a.ActorId, a.LocationId
FROM `Jurisdiction` AS a
WHERE ActorId =Tom
UNION SELECT a.ActorId, b.WarehouseId AS LocationId
FROM `Jurisdiction` AS a, `LocationRelationship` AS b
WHERE a.LocationId = b.ShopId
AND a.ActorId =Tom
我現在想算這個select的個數
使用這句sql:
SELECT ((SELECT COUNT(*)
FROM `Jurisdiction` AS a
WHERE ActorId =Tom)
+ (SELECT COUNT(*)
FROM `Jurisdiction` AS a, `LocationRelationship` AS b
WHERE a.LocationId = b.ShopId
AND a.ActorId =Tom))AS total
算出total是8與結果不合
後來發現前面那句sql的total是2後面那句sql的total是6
將後面那句sql多select幾個欄位後 select出來是
ActorId ShopId WarehouseId
Tom A C
Tom A D
Tom A E
Tom B C
Tom B D
Tom B E
要怎麼樣select出這樣，重複的WarehouseId就不顯示呢？
ActorId ShopId LocationId
Tom A C
Tom A D
Tom A E
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 59.125.41.241
→
10/07 20:07, , 1F
10/07 20:07, 1F
→
10/08 10:59, , 2F
10/08 10:59, 2F
Database 近期熱門文章
PTT數位生活區 即時熱門文章
