if not object_id('t1') is null drop table t1 Go Create table t1([SID] nvarchar(3),[BID] nvarchar(3)) Insert t1 select N'001',N'001' union all select N'002',N'001' union all select N'005',N'003' Go if not object_id('t2') is null drop table t2 Go Create table t2([SID] nvarchar(3),[SHULIANG] int) Insert t2 select N'001',3 union all select N'002',4 union all select N'005',8 Go select BID, stuff((select ','+[SID] from t1 where bid=t.bid for xml path('')),1,1,'')SID, sum([SHULIANG])[SHULIANG] from t1 t left join t2 tt on t.[SID] =tt.[SID] group by bid
SID BID
001 001
002 001
005 " "
-----------------------------
表二
SID SHULIANG
001 3
002 4
005 8
-----------------------------数据库中有以上两张表,《表一》中SID为 小类编号 BID 为大类编号,其中SID中编号 001和 BID中编号为 主编号
《表二》中SID与表一中SID相关联 ,SID 中编号001也为主编号请问如何数据库查询语句如何按大类编号 和小类编号统计 《表二》中字段SHULIANG
如下表:
--------------------
BID SID SHULIANG
--------------------
001
001 002 7
--------------------
005 8问题是这样的 上面发错了
drop table t1
Go
Create table t1([SID] nvarchar(3),[BID] nvarchar(3))
Insert t1
select N'001',N'001' union all
select N'002',N'001' union all
select N'005',N'003'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([SID] nvarchar(3),[SHULIANG] int)
Insert t2
select N'001',3 union all
select N'002',4 union all
select N'005',8
Go
select BID,
stuff((select ','+[SID]
from t1
where bid=t.bid
for xml path('')),1,1,'')SID,
sum([SHULIANG])[SHULIANG]
from t1 t left join t2 tt on t.[SID] =tt.[SID]
group by bid