3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
这样的SQL语句如何写？
我有两个表，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的位置不重要，因为它们记录数相同，请问这样的语句如何写呢
--------------------------------------------------------------
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的位置不重要，因为它们记录数相同，请问这样的语句如何写呢
...全文
请发表友善的回复…
发表回复
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
(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
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
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)
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
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的位置不重要，因为它们记录数相同，请问这样的语句如何写呢
04 5
00 3
02 1
01 1
上面的数据，02和01的位置不重要，因为它们记录数相同，请问这样的语句如何写呢