sql语句求解 (急)
note_id noteCategory_id
1 1
2 2
3 1
4 1
5 2
表二：
noteCategory_id noteCategoryName
1 bank
2 government
3 report
求sql语句或存储过程：
得到：
noteCategory_id noteCategoryName num
1 bank 3
2 government 2
3 report 0
num为noteCategory 所具有的note_id的数量，从表一中得到 的。
谢了哦
[解决办法]
SELECT noteCategory_id ,noteCategoryName,
num = (select count(*) from 表1 where noteCategory_id = a.noteCategory_id)
FROM 表2 AS a
[解决办法]
declare @ta table(note_id int, noteCategory_id int)
insert @ta
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2
declare @tb table(noteCategory_id int, noteCategoryName varchar(10))
insert @tb
select 1, 'bank ' union all
select 2, 'government ' union all
select 3, 'report '
SELECT noteCategory_id ,noteCategoryName,
num = (select count(*) from @ta where noteCategory_id = a.noteCategory_id)
FROM @tb AS a
/*结果
noteCategory_id noteCategoryName num
--------------- ---------------- -----------
1 bank 3
2 government 2
3 report 0
*/
[解决办法]
select
notecategory_id,
notecategoryname,
(select count(*) from 表1 where notecategory_id = a.notecategory_id) as num
from 表2 as a
