多表查询去除重复记录
首先关于sql 多表查询去除重复记录我们就可以想到用group by 或distinct 再着想到inner left 等，
下面来看看个实例
看一个distinct 实例
现在将完整语句放出:
select *, count(distinct name) from table group by name
结果:
id name count(distinct name)
1 a 1
2 b 1
3 c 1
最后一项是多余的，不用管就行了，目的达到。。。。。
group by 必须放在 order by 和 limit之前，不然会报错
db_a:
id age
1 20
2 30
3 40
4 50
db_b:
topid poto
2 axxxxxxxxxx
2 bxxxxxxxxxx
2 cxxxxxxxxxxx
3 dxxxxxxxxxxx
SELECT * FROM db_a AS A LEFT JOIN db_b AS B ON B.topid=A.id;
现在查询出来有6条数据, 怎么解决.
SELECT * FROM db_a AS A RIGHT JOIN db_b AS B ON B.topid=A.id;
//四条数据。是你要的吗
id age topicid poto
2 bbbbbb 2 axxxxx
2 bbbbbb 2 bxxxxxx
2 bbbbbb 2 cxxxxx
3 cccccc 3 dxxxxxx
SELECT * FROM db_a AS A, db_b AS B WHERE B.topid = A.id
select distinct(列名) from 表
找出这个表中，这个列里，不重复的值出来
distinct(列名)
SELECT * FROM db_a AS A INNER JOIN db_b AS B ON A.id = B.topid;
SELECT * FROM db_a AS A left JOIN db_b AS B ON A.id = B.topid goup by a.id;
另外更多方法
方法一：用union
select a.menuId, menuAliasNumber, menuName1, menuName2 ,
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=1) as 'reg',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=2) as 'large',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=3) as 'small'
from workmenuItems a right join workmenuCatUse b on a.menuId=b.menuId
right join workmenuPrice c on c.menuId=b.menuId
union
select a.menuId, menuAliasNumber, menuName1, menuName2 ,
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=1) as 'reg',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=2) as 'large',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=3) as 'small'
from workmenuItems a right join workmenuCatUse b on a.menuId=b.menuId
right join workmenuPrice c on c.menuId=b.menuId
方法二：用distinct
select distinct(a.menuId), menuAliasNumber, menuName1, menuName2 ,
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=1) as 'reg',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=2) as 'large',
(select price from workmenuPrice where workmenuPrice.menuId=a.menuId and menuPriceTypeId=3) as 'small'
from workmenuItems a right join workmenuCatUse b on a.menuId=b.menuId
right join workmenuPrice c on c.menuId=b.menuId