SQL如何Count Distinct过的数据

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.id
and (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.id
and (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.id
and (t1.landType like '%粮%' or t1.gatherId like '%粮%') and t2.industryId ='1') as total

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

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