34,590
社区成员
发帖
与我相关
我的任务
分享
declare @t table(AID varchar(10), BID varchar(10))
insert @t select 1, 'g'
union all select 2 , 'e'
union all select 2 , '3'
union all select 4 , 'd'
union all select 4 , 'h'
union all select 4 , 'w'
union all select 3 , '4'
union all select 1, 'u'
select a.aid,a.bid,(select count(*) from @t where aid= a.aid) as 个数 from @t a order by aid
/*
aid bid 个数
---------- ---------- -----------
1 g 2
1 u 2
2 e 2
2 3 2
3 4 1
4 h 3
4 w 3
4 d 3
*/
declare @table2 table
(
ProdID int
)
declare @n int
--select * from @table
insert @table2 select distinct(ProdID) from Prod order by ProdID
set @n=1
while @n-1< (select count(distinct ProdID) from Prod)
begin
set @n= @n+1
select ProdID,ProdType from Prod where ProdID=(select top 1 ProdID from @table2)
delete @table2 where ProdID = (select top 1 ProdID from @table2)
end
declare @table2 table
(
AID int
)
declare @n int
insert @table2 select distinct(ProdID) from Prod order by ProdID
set @n=1
while @n-1< (select count(distinct ProdID) from Prod)
begin
set @n= @n+1
select ProdID,ProdType from Prod where ProdID=(select top 1 ProdID from @table2)
delete @table2 where ProdID = (select top 1 ProdID from @table2)
end