我的解决方案: 建立如下存储过程供程序中调用CREATE PROCEDURE [dbo].[MYDEMO] AS CREATE TABLE #TempTable ( name varchar(100), ac bigint, bc bigint )declare @cname nvarchar(100)DECLARE Return_Cursor CURSOR FOR SELECT name FROM A OPEN Return_Cursor FETCH NEXT FROM Return_Cursor INTO @cname
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO #TempTable(name) VALUES(@cname) update #TempTable set ac=(select count(B1) from B where B.B1=@cname), bc=(select count(B2) from B where B.B2=@cname) where name=@cname
FETCH NEXT FROM Return_Cursor INTO @cname END CLOSE Return_Cursor DEALLOCATE Return_Cursorselect * from #TempTable GO
表A
name
----------
白色
黑色
红色
----------
表B
B1 B2
--------
白色 黑色
白色 黑色
红色 白色
-----------DataGrid 是
name B1 B2
----------------
白色 2 1
黑色 0 2
红色 1 0
--------------
就是这样的
建立如下存储过程供程序中调用CREATE PROCEDURE [dbo].[MYDEMO]
AS
CREATE TABLE #TempTable
(
name varchar(100),
ac bigint,
bc bigint
)declare @cname nvarchar(100)DECLARE Return_Cursor CURSOR
FOR SELECT name FROM A
OPEN Return_Cursor
FETCH NEXT FROM Return_Cursor INTO @cname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTable(name)
VALUES(@cname)
update #TempTable
set
ac=(select count(B1) from B where B.B1=@cname),
bc=(select count(B2) from B where B.B2=@cname)
where name=@cname
FETCH NEXT FROM Return_Cursor INTO @cname
END CLOSE Return_Cursor
DEALLOCATE Return_Cursorselect * from #TempTable
GO