多表查询去除重复记录

多表查询去除重复记录

首先关于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