如下两个表:
表1
provinceid name
100 北京
200 上海
... ....
表2
province_termipid content
200 test
100 sdfsdf
100 3212
100 dsd
我想实现的是这样的效果:
id icount
北京 该省记录数
上海 该省记录数
表1
provinceid name
100 北京
200 上海
... ....
表2
province_termipid content
200 test
100 sdfsdf
100 3212
100 dsd
我想实现的是这样的效果:
id icount
北京 该省记录数
上海 该省记录数
解决方案 »
- 用逗号隔开的数据怎么模糊查询
- 这个触发器怎么写?
- 100分求月份统计数据,要求与每年的月份对比数据,同时类型分开
- 数据文件和日志文件大小不变化怎么回事?
- sqlserver7.0 unInstallShield 问题
- 在还原数据库后,提示数据库将保留于装载状态,不能用,怎么办?
- 卸载SQL sever 2005了,然后无法删除目录下的 Microsoft SQL Server的文件夹 显示“无法删除文件:无法读源文件或磁盘”
- 大家谈谈自增字段的存在意义!
- 请问。。这两句sql语句那句效率为高一些?[高手来啊]
- [求助]请教涉及到行列转换的动态区间分段统计实现方法
- sql2005如何使用Microsoft Visual SourceSafe
- 求一个简单SQL查询语句和一个有点复杂的存储过程
FROM province A
LEFT JOIN (SELECT PID,COUNT(1) ICOUNT FROM province_termi GROUP BY PID) B
ON A.ID = B.PID
INSERT INTO @A SELECT 100,'北京'
UNION ALL
SELECT 200,'上海'DECLARE @B TABLE(PID INT, CONTENT VARCHAR(20))
INSERT INTO @B SELECT 200,'test'
UNION ALL
SELECT 100,'sdfsdf'
UNION ALL
SELECT 100,'3212'
UNION ALL
SELECT 100,'dsd'SELECT NAME,B.ICOUNT
FROM @A A
LEFT JOIN (SELECT PID,COUNT(1) ICOUNT FROM @B GROUP BY PID) B
ON A.ID = B.PID
-------------------------
北京 3
上海 1
FROM province A
LEFT JOIN (SELECT PID,COUNT(1) ICOUNT FROM province_termi GROUP BY PID) B
ON A.ID = B.PID
from 表1 a left join (select pid,count(1) as icount from 表2 group by pid) b
on a.id=b.pid
(
select a.id as id1,a.name as id ,count(*) as icount from province a
inner join province_termi b
on a.id=b.pid
group by a.id,a.name
) aa
你可以测试哈!我本地语法成功!~嘎嘎!
INSERT INTO @A SELECT 100,'北京'
UNION ALL
SELECT 200,'上海'DECLARE @B TABLE(PID INT, CONTENT VARCHAR(20))
INSERT INTO @B SELECT 200,'test'
UNION ALL
SELECT 100,'sdfsdf'
UNION ALL
SELECT 100,'3212'
UNION ALL
SELECT 100,'dsd' select a.id,a.name,count(*) as 总数 from @a a ,@b b where a.id=b.pid group by a.id,a.name--100 北京 3
--200 上海 1
SELECT A.ID,A.Name,B.ICOUNT
FROM province A
LEFT JOIN (SELECT PID,COUNT(PID) ICOUNT FROM province_termi GROUP BY PID) B
ON A.ID = B.PID你看一下.我这没有环境,只要SELECT PID,COUNT(PID) ICOUNT FROM province_termi GROUP BY PID对,这条语句就对.我忘了GROUP BY这么写对不对了.
不好意思.