表 tbl_logUserID OtherID
aaa 1
bbb 2
bbb 2
aaa 1
bbb 2
ccc 3
ddd 1我想统计出来,每个OtherID,下,用户的个数如OtherID为1,用户只有aaa, ddd,但在数据库里有3条aaa的记录,最终希望结果为OtherID count
1 2 (aaa, ddd)
2 1 (bbb)
3 1 (ccc)
aaa 1
bbb 2
bbb 2
aaa 1
bbb 2
ccc 3
ddd 1我想统计出来,每个OtherID,下,用户的个数如OtherID为1,用户只有aaa, ddd,但在数据库里有3条aaa的记录,最终希望结果为OtherID count
1 2 (aaa, ddd)
2 1 (bbb)
3 1 (ccc)
UserID varchar(10),
OtherID int
)insert into tb select 'aaa',1
union all select 'bbb',2
union all select 'bbb',2
union all select 'aaa',1
union all select 'bbb',2
union all select 'ccc',3
union all select 'ddd',1go
create function dbo.get(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=+@str+'('+UserID+')' from tb where OtherID=@id group by UserID
return @str
end
goselect OtherID,count=count(distinct UserID),dbo.get(OtherID) from tb group by OtherIDdrop function get
drop table tb
2 1 (bbb)
3 1 (ccc)
) AS A GROUP BY A.OtherID
) AS A GROUP BY A.OtherID
CREATE TABLE tbl_log
(
UserID VARCHAR(20),
OtherID INT
)
INSERT INTO tbl_log
SELECT 'aaa',1 UNION ALL
SELECT 'bbb',2 UNION ALL
SELECT 'bbb',2 UNION ALL
SELECT 'aaa',1 UNION ALL
SELECT 'bbb',2 UNION ALL
SELECT 'ccc',3 UNION ALL
SELECT 'ddd',1 --2005
SELECT *
FROM
(
SELECT DISTINCT OtherID FROM tbl_log
) A
OUTER APPLY
( SELECT [UserID] = STUFF(REPLACE(REPLACE(
(
SELECT UserID FROM
(SELECT UserID,OtherID FROM tbl_log GROUP BY UserID,OtherID) B
WHERE OtherID = A.OtherID
FOR XML AUTO
), '<B UserID="',','), '"/>', ''),1,1,'')
) NDROP TABLE tbl_logOtherID UserID
----------- -----------------
1 aaa,ddd
2 bbb
3 ccc
UserID varchar(10),
OtherID int
)insert into tb select 'aaa',1
union all select 'bbb',2
union all select 'bbb',2
union all select 'aaa',1
union all select 'bbb',2
union all select 'ccc',3
union all select 'ddd',1go
create function dbo.get(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=+@str+','+UserID from tb where OtherID=@id group by UserID
return @str
end
goselect OtherID,count=count(distinct UserID),'('+stuff(dbo.get(OtherID),1,1,'')+')' as result from tb group by OtherIDdrop function get
drop table tb/*
OtherID count result
----------- ----------- -----------
1 2 (aaa,ddd)
2 1 (bbb)
3 1 (ccc)
*/
group by otherid
declare @T table(UserID varchar(3),OtherID int)
insert @T
select 'aaa',1 union all
select 'bbb',2 union all
select 'bbb',2 union all
select 'aaa',1 union all
select 'bbb',2 union all
select 'ccc',3 union all
select 'ddd',1-- 整那么复杂?COUNT(DISTINCT UserID)select OtherID,[count]=COUNT(DISTINCT UserID) from @T group by OtherID/*
OtherID count
----------- -----------
1 2
2 1
3 1
*/
create function ll(@otherid int)
returns varchar(50)
as
begin
declare @sql varchar(50)
select @sql=isnull(@sql+',','')+UserID from (select distinct * from tablename)a where otherid=@otherid
return @sql
endselect otherid,count(distinct userid) userid,dbo.ll(otherid)[ ]
from tablename group by otherid
count(distinct userID)
FROM tbl_log
GROUP BY otherid