多表查詢去除重復記錄

多表查詢去除重復記錄

首先關於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