导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

这样的SQL语句如何写?

jonsonzxw 2003-06-22 01:36:13
我有两个表,category 和articles
--------------------------------------------------------------
category表:
字段名 类型 长度
catid varchar 2
....

--------------------------------------------------------------
articles表:
字段名 类型 长度
artid varchar 12
...

示例数据:
000000000000
040000000000
020000000000
000000000001
040000000001
040000000002
010000000000
040000000003
040000000004
000000000002

articles表artid的前2位就是categorys表的catid

现在的问题是我想得到这样的结果:
04 5
00 3
02 1
00 1

上面的数据,02和00的位置不重要,因为它们记录数相同,请问这样的语句如何写呢

...全文
6 点赞 收藏 13
写回复
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
nboys 2003-06-22
select left(artid,2),count(*) from articles group by left(artid,2) having count(*)>0 order by count(*) desc
回复
RobertYang 2003-06-22
select left(artid,2),count(*) from articles group by left(artid,2) having count(*) > 0
回复
pengdali 2003-06-22
select left(artid,2),count(*) from articles group by left(artid,2) order by count(*) desc
回复
select a.* from
(select left(artid ,2) as artid,count(*) number
from articles
group by left(artid ,2)
) a
where number>0
order by number desc
回复
pengdali 2003-06-22
select left(artid,2),count(*) from articles group by left(artid,2)
回复
jonsonzxw 2003-06-22
可以了,

04 5
00 2
02 1
01 0
03 0
05 0
06 0
07 0
08 0
09 0
99 0

能不能再问一下,将记录数为0的过滤掉,如何做呢???
如上面,只显示
04 5
00 2
02 1
回复
你的意思是articles表的artid首两位统计:
select a.* from
(select left(artid ,2) as artid,count(*) number
from articles
group by left(artid ,2)
) a
order by number desc
回复
jonsonzxw 2003-06-22
谢谢,我试试先
回复
caiyunxia 2003-06-22
select left(artid ,2),count(*)
from articles
group by left(artid ,2)
回复
bapi 2003-06-22
select t1.catid, count(*)
from category t1
left join articles t2 on (t1.catid = SubString(t2.artid,1,2))
group by t1.catid
回复
pengdali 2003-06-22
select a.catid,isnull(b.num,0) num from category a left join (select left(artid,2) artid,count(*) num from articles group by left(artid,2)) b on a.catid=b.artid order by b.num desc
回复
pengdali 2003-06-22
select a.catid,isnull(b.num,0) num from category a left join (select left(artid,2) artid,count(*) num from articles group by left(artid,2)) b on a.catid=b.artid order by b.num
回复
jonsonzxw 2003-06-22
sorry ,最后写错了一点
04 5
00 3
02 1
01 1

上面的数据,02和01的位置不重要,因为它们记录数相同,请问这样的语句如何写呢
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告