求教两表分组汇总及排序

Question

表1
id name 
1   tom
2   mike
3   john表2
id     xm           lb        time
1      tom          跳舞        2009-01-09
2      tom          唱歌        2009-02-21
3      tom          打球        2009-01-21
4      mike         唱歌        2009-03-11
5      mike         打球        2009-05-02
6      john         跳舞        2009-04-29
7      (包场)tom    唱歌        2009-01-12
8      mike         唱歌        2009-03-21
9      mike         打球        2009-04-22
10     (包场)john    跳舞        2009-05-01[b]想根据时间段，来汇总，如果lb中没有发生过，则汇总显示为0,其中条目7汇总到tom的唱歌中,10也要汇到john的跳舞中去
取得这样的结果：[/b]tom  跳舞          1
     唱歌          2    "这里边就包含了包场"
     打球          1
mike 跳舞          0
     唱歌          2
     打球          2
john 跳舞          2    "这里边就包含了包场"
     唱歌          0
     打球          0

Answer 1

declare @t1 table(id int,name varchar(10))
insert into @t1 select 1,'tom'
insert into @t1 select 2,'mike'
insert into @t1 select 3,'john' declare @t2 table(id int,xm varchar(16),lb varchar(10),time datetime)
insert into @t2 select 1 ,rtrim('tom       '),'跳舞','2009-01-09'
insert into @t2 select 2 ,rtrim('tom       '),'唱歌','2009-02-21'
insert into @t2 select 3 ,rtrim('tom       '),'打球','2009-01-21'
insert into @t2 select 4 ,rtrim('mike      '),'唱歌','2009-03-11'
insert into @t2 select 5 ,rtrim('mike      '),'打球','2009-05-02'
insert into @t2 select 6 ,rtrim('john      '),'跳舞','2009-04-29'
insert into @t2 select 7 ,rtrim('(包场)tom '),'唱歌','2009-01-12'
insert into @t2 select 8 ,rtrim('mike      '),'唱歌','2009-03-21'
insert into @t2 select 9 ,rtrim('mike      '),'打球','2009-04-22'
insert into @t2 select 10,rtrim('(包场)john'),'跳舞','2009-05-01'select
    a.name,b.lb,isnull(count(c.id),0)
from
    @t1 a cross join (select distinct lb from @t2) b
left join
    @t2 c
on
    a.name=replace(c.xm,'(包场)','') and b.lb=c.lb
group by
    a.name,b.lb
order by
    a.name,b.lb/*
name       lb                     
---------- ---------- ----------- 
john       唱歌         0
john       打球         0
john       跳舞         2
mike       唱歌         2
mike       打球         2
mike       跳舞         0
tom        唱歌         2
tom        打球         1
tom        跳舞         1
*/

Answer 2

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (id INT,name VARCHAR(4))
INSERT INTO @tb1
SELECT 1,'tom' UNION ALL
SELECT 2,'mike' UNION ALL
SELECT 3,'john'
 
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (id INT,xm VARCHAR(10),lb VARCHAR(4),time DATETIME)
INSERT INTO @tb2
SELECT 1,'tom','跳舞','2009-01-09' UNION ALL
SELECT 2,'tom','唱歌','2009-02-21' UNION ALL
SELECT 3,'tom','打球','2009-01-21' UNION ALL
SELECT 4,'mike','唱歌','2009-03-11' UNION ALL
SELECT 5,'mike','打球','2009-05-02' UNION ALL
SELECT 6,'john','跳舞','2009-04-29' UNION ALL
SELECT 7,'(包场)tom','唱歌','2009-01-12' UNION ALL
SELECT 8,'mike','唱歌','2009-03-21' UNION ALL
SELECT 9,'mike','打球','2009-04-22' UNION ALL
SELECT 10,'(包场)john','跳舞','2009-05-01'--SQL查询如下:SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY name ORDER BY name)=1 
          THEN name ELSE '' END AS name,
    lb,cnt
FROM (
    SELECT 
        A.name,B.lb,COUNT(C.xm) AS cnt
    FROM @tb1 AS A
        CROSS JOIN (SELECT DISTINCT lb FROM @tb2) AS B
        LEFT JOIN @tb2 AS C
    ON CHARINDEX(A.name,C.xm)>0
       AND B.lb=C.lb
    GROUP BY A.name,B.lb
) AS A/*
name lb   cnt
---- ---- -----------
john 唱歌   0
     打球   0
     跳舞   2
mike 唱歌   2
     打球   2
     跳舞   0
tom  唱歌   2
     打球   1
     跳舞   1
(9 行受影响)
*/

Answer 3

---测试数据---
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(4))
insert [ta]
select 1,'tom' union all
select 2,'mike' union all
select 3,'john'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[xm] varchar(10),[lb] varchar(4),[time] datetime)
insert [tb]
select 1,'tom','跳舞','2009-01-09' union all
select 2,'tom','唱歌','2009-02-21' union all
select 3,'tom','打球','2009-01-21' union all
select 4,'mike','唱歌','2009-03-11' union all
select 5,'mike','打球','2009-05-02' union all
select 6,'john','跳舞','2009-04-29' union all
select 7,'(包场)tom','唱歌','2009-01-12' union all
select 8,'mike','唱歌','2009-03-21' union all
select 9,'mike','打球','2009-04-22' union all
select 10,'(包场)john','跳舞','2009-05-01'
 
---查询---
select 
  a.name,
  a.lb,
  isnull(b.cnt,0) cnt  
from
  (select a.id,a.name,b.lb from ta a,(select distinct lb from tb) b) a
left join
  (select replace(xm,'(包场)','') as xm,lb,count(1) as cnt from tb group by replace(xm,'(包场)',''),lb) b
on
  a.name=b.xm and a.lb=b.lb
order by
  a.id---结果---
name lb   cnt         
---- ---- ----------- 
tom  唱歌   2
tom  打球   1
tom  跳舞   1
mike 跳舞   0
mike 打球   2
mike 唱歌   2
john 唱歌   0
john 打球   0
john 跳舞   2（所影响的行数为 9 行）

Answer 4

SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY name ORDER BY name)=1 
          THEN name ELSE '' END AS name,
    lb,cnt
FROM (
    SELECT 
        A.name,B.lb,COUNT(C.xm) AS cnt
    FROM @tb1 AS A
        CROSS JOIN (SELECT DISTINCT lb FROM @tb2) AS B
        LEFT JOIN @tb2 AS C
    ON CHARINDEX(A.name,C.xm)>0
       AND B.lb=C.lb
    GROUP BY A.name,B.lb
) AS A

Answer 5

declare @tb table(id int,xm  varchar(20),lb varchar(10),       [time] varchar(10))insert @tb select 1   ,   'tom'  ,        '跳舞'   ,     '2009-01-09' 
insert @tb select 2   ,   'tom'   ,       '唱歌'  ,      '2009-02-21' 
insert @tb select 3  ,    'tom'   ,       '打球'   ,     '2009-01-21' 
insert @tb select 4  ,    'mike'    ,    '唱歌'    ,    '2009-03-11' 
insert @tb select 5   ,   'mike'    ,    '打球'    ,    '2009-05-02' 
insert @tb select 6  ,    'john'      ,  '跳舞'    ,    '2009-04-29' 
insert @tb select 7  ,    '(包场)tom' ,   '唱歌'   ,     '2009-01-12'
insert @tb select 8   ,   'mike'      ,  '唱歌'    ,    '2009-03-21' 
insert @tb select 9  ,    'mike'      ,  '打球'    ,    '2009-04-22' 
insert @tb select 10  ,  '(包场)john'   , '跳舞'   ,    '2009-05-01' 
select xm,lb,次数=sum(次数) from (
select xm=replace(xm,'(包场)',''),lb,次数=count(1) from @tb group by replace(xm,'(包场)',''),lb
union all
select a.xm,b.lb,0 from (select distinct xm=replace(xm,'(包场)','') from @tb) a,(select distinct lb from @tb) b) c
group by xm,lb order by xm,lb/*
xm        lb         次数          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------- 
john      唱歌         0
john      打球         0
john      跳舞         2
mike      唱歌         2
mike      打球         2
mike      跳舞         0
tom       唱歌         2
tom       打球         1
tom      跳舞          1（所影响的行数为 9 行）
*/

Answer 6

---测试数据---
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(4))
insert [ta]
select 1,'tom' union all
select 2,'mike' union all
select 3,'john'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[xm] varchar(10),[lb] varchar(4),[time] datetime)
insert [tb]
select 1,'tom','跳舞','2009-01-09' union all
select 2,'tom','唱歌','2009-02-21' union all
select 3,'tom','打球','2009-01-21' union all
select 4,'mike','唱歌','2009-03-11' union all
select 5,'mike','打球','2009-05-02' union all
select 6,'john','跳舞','2009-04-29' union all
select 7,'(包场)tom','唱歌','2009-01-12' union all
select 8,'mike','唱歌','2009-03-21' union all
select 9,'mike','打球','2009-04-22' union all
select 10,'(包场)john','跳舞','2009-05-01'
 
---查询---
select 
  replace(xm,'(包场)',''),lb,Count(*) as 次数,
case when sum(case when Charindex('(包场)',xm)>0 then 1 else 0 end)>0 then '这里边就包含了包场' else '' end
from tb
group by replace(xm,'(包场)',''),lb
order by replace(xm,'(包场)',''),lb/*        结果john 跳舞 2 这里边就包含了包场
mike 唱歌 2
mike 打球 2
tom 唱歌 2 这里边就包含了包场
tom 打球 1
tom 跳舞 1 */

Answer 7

---查询---
select a.[name],a.lb,isnull(次数,0) as 次数,isnull(memo,'') memo
from
(
select a.lb,b.[name]
from
(
select distinct [lb] from tb
) a,[ta] b
) a
left join 
(
select 
  replace(xm,'(包场)','') as xm,lb,Count(*) as 次数,
(case when sum(case when Charindex('(包场)',xm)>0 then 1 else 0 end)>0 then '这里边就包含了包场' else '' end) as memo
from tb
group by replace(xm,'(包场)',''),lb
) c on a.lb=c.lb and a.[name]=c.xm
order by a.[name],a.lb/*结果john 唱歌 0
john 打球 0
john 跳舞 2 这里边就包含了包场
mike 唱歌 2
mike 打球 2
mike 跳舞 0
tom 唱歌 2 这里边就包含了包场
tom 打球 1
tom 跳舞 1
*/