好长时间没用sql了很多东西都忘掉了,昨天用到了,好不容易才想找到,人比较笨,今天记下来省得再忘记!
type表:用来存放文章的类别:
typeid | typename |
1 | 体育新闻 |
2 | 军事新闻 |
3 | 国际新闻 |
article表:用来存放文章:
title | typeid | content | hits |
aaa | 1 | AAAA | 12 |
bbb | 2 | BBBB | 11 |
ccc | 1 | CCCC | 23 |
ddd | 1 | DDDD | 34 |
eee | 2 | EEEE | 43 |
fff | 2 | FFFF | 22 |
ggg | 1 | GGGG | 25 |
1:合并两个表,统计每种类型文章的总数:
select type.typeid,type.typename,(select count(*) from article where article.type=type.typeid) as article_number from type order by article_number desc
2:合并两个表,忽略记录为空的内容(如没有typeid为3的文章):
select distinct type.typeid, type.typename, article.title,article.hits from type full outer join article on article.typeid = type.typeid
distinct关键字为去除重复的字段