SQL如何Count Distinct过的数据

来源：互联网发布：穆雅斓淘宝店铺号编辑：程序博客网时间：2024/05/20 00:39

distinct:

<pre name="code" class="sql">SELECT distinct t1.*,t2.industryId  FROM positions t1 ,planteddetails t2, landplanted t3 where t3.landId = t1.id and t2.plantedId = t3.idand (t1.landType like '%粮%' or t1.gatherId like '%粮%') and t2.industryId ='1'

直接count会报错

SELECT count((distinct t1.*,t2.industryId  FROM positions t1 ,planteddetails t2, landplanted t3) where t3.landId = t1.id and t2.plantedId = t3.idand (t1.landType like '%粮%' or t1.gatherId like '%粮%') and t2.industryId ='1'

所以我们可以换个思路

select count(*) from (SELECT distinct t1.*,t2.industryId  FROM positions t1 ,planteddetails t2, landplanted t3 where t3.landId = t1.id and t2.plantedId = t3.idand (t1.landType like '%粮%' or t1.gatherId like '%粮%') and t2.industryId ='1') as total

直接把distinct的结果作为一张表count

这里as total是必须的临时表需要名字不然会报错

0 0