select c.class_id,c.class_name,c.class_table,tt.数量
from [table] c
join
(
select col,sum(数量) 数量 from class_table group by col
) tt on c.class_table=tt.col
from [table] c
join
(
select col,sum(数量) 数量 from class_table group by col
) tt on c.class_table=tt.col
select c.class_id,c.class_name,c.class_table,tt.数量
from [table] c
join
(
select col,sum(数量) 数量 from class_table group by col --col为class_table里面的大米,酱油所在的列
) tt on c.class_table=tt.col
select
b.class_id ,
b.class_name ,
b.class_table ,
select count(*) from class_table a where a.class_table = b.class_table as 数量
from [table] b
group by b.class_table这样?
)tt.数量
from [table] c
group by class_name,class_id
select
b.class_id ,
b.class_name ,
b.class_table ,
select count(*) from class_table a where a.class_table = b.class_table as 数量
from [table] b
group by b.class_table,b.class_id,b.class_name
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+'select '''+name+''' as name,count(1) as cnt from ['+name+']' from sysobjects where xtype='u' and name like '%table_%'
insert into # exec(@sql)select
a.*,
b.cnt as 数量
from
[table] a
left join
# b
on
a.class_table=b.name
INSERT [table_大米]
SELECT 1CREATE TABLE TB([class_id] INT, [class_name] NVARCHAR(2), [class_table] NVARCHAR(8))
INSERT TB
SELECT 1, N'大米', N'table_大米' UNION ALL
SELECT 2, N'酱油', N'table_酱油' UNION ALL
SELECT 3, N'醋', N'table_醋'SELECT *,rowcnt=isnull((select rowcnt from sysindexes where id=object_id([class_table])),0) FROM TB DROP TABLE TB,[table_大米]
/*
class_id class_name class_table rowcnt
----------- ---------- ----------- --------------------
1 大米 table_大米 1
2 酱油 table_酱油 0
3 醋 table_醋 0
*/
select @sql=isnull(@sql+' union all ','')+'select '''+name+''' as name,count(1) as cnt from ['+name+']' from sysobjects where xtype='u' and name like '%table_%'
insert into # exec(@sql)select
a.*,
b.cnt as 数量
from
[table] a
left join
# b
on
a.class_table=b.name
http://topic.csdn.net/u/20090428/12/c13a0ffa-d89f-476a-bdb9-af5abcc7b668.html
insert into tba select 1,'大米','table_大米'
insert into tba select 2,'酱油','table_酱油'
insert into tba select 3,'醋','table_醋'
insert into tba select 4,'大米','table_大米'
insert into tba select 5,'醋','table_醋'
go
select class_id=identity(int,1,1),class_name,class_table,count(*) as cnt into tbb from tba group by class_name,class_table
select * from tbb
go
drop table tba,tbb
/*
class_id class_name class_table cnt
----------- ---------- -------------------- -----------
1 醋 table_醋 2
2 大米 table_大米 2
3 酱油 table_酱油 1
*/
这个sysindexes至少还得加个 indid in(0,1) 条件,否则有可能会返回多条记录
select class_id=identity(int,1,1),class_name,class_table,count(*) as 数量 into tbb from tba group by class_name,class_table
insert into #tba select 1,'大米','table_大米'
insert into #tba select 2,'酱油','table_酱油'
insert into #tba select 3,'醋','table_醋'
insert into #tba select 4,'大米','table_大米'
insert into #tba select 5,'醋','table_醋'select id=identity(int,1,1),class_name,class_table,count(class_table) cnt into # from #tba group by class_name,class_tableselect * from #id class_name class_table cnt
----------- ---------- -------------------- -----------
1 醋 table_醋 2
2 大米 table_大米 2
3 酱油 table_酱油 1(3 行受影响)
如果不是的
请说清楚
大家都不懂
你语言在组织下
SELECT a.class_name , a.class_table,b.num as 数量 FROM table a
left join (
select 'table_大米' as table_name count(1) as num from table_大米
union all
select 'table_酱油' as table_name count(1) as num from table_酱油
union all
select 'table_醋 ' as table_name count(1) as num from table_醋 )b on a.class_table=b.table_name
declare @s varchar(8000)
set @s=''
select @s='select * ,数量=(select count(*) from '+class_table+') union '
from tbexec(@s)
试试吧