多表查詢去除重復記錄
首先關於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
