作者kalecgos0616 (.)
看板Database
标题[SQL ] 算UNION後的个数
时间Thu Oct 7 16:20:12 2010
资料库是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
1F：→ grence:"代表A店面有C, D仓库;"←叙述跟例子不符，还是我误解 囧a 10/07 20:07
2F：→ kalecgos0616:自问自答，後面那句用DISTINCT 就可以了 10/08 10:59