这样的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的位置不重要，因为它们记录数相同，请问这样的语句如何写呢

...全文

73 13 打赏收藏转发到动态举报

写回复

用AI写文章

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

zjcxc 元老 2003-06-22

打赏
举报

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

zjcxc 元老 2003-06-22

打赏
举报

你的意思是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