多表查询行列统计

------------------------------------------------ 
/** 
表1 
编号1            IP   用户      终端                日期1                      日期2 
1   218.18.189.127   00001 00e08141fbEf     2009-06-08 17:10:47.997       2009-06-08 17:10:47.997 
2   218.15.120.190   00001 000B2F0AC6D0     2009-06-09 12:43:15.060       2009-06-09 12:22:56.000 
3   58.251.125.158   00002 000c29fb4ba3     2009-06-10 14:12:47.700       2009-06-09 18:21:01.000 
4   58.251.125.158   00002 002127aa6dce     2009-06-10 07:33:07.607       2009-06-10 07:33:07.607 
5   219.134.37.66    00002 005056C00001     2009-06-12 09:38:14.167       2009-06-08 10:17:16.000 
6   218.75.3.18      00003 00e08101fbd9     2009-06-11 12:38:37.327       2009-06-11  12:38:23.000 
7   222.208.6.55     00004 00e08101fb3f     2009-06-11 13:01:28.597       2009-06-08 17:16:04.000 
8   202.98.153.162   00005 00016cbb07ab     2009-06-11 19:46:59.273       2009-06-11 14:04:33.000 
9   202.98.153.171   00005 0004614b132b     2009-06-11 17:52:12.790       2009-06-10 21:36:54.000 
10  202.98.153.171   00001 000461769e41     2009-06-11 21:14:13.853       2009-06-10 14:08:03.000 
---------------------------------------------------- 
表2 
标识      编号1 数据编号                    次数              日期3 
1          1      1                          3   2009-06-06 17:20:57.873 
2          1      1                          31   2009-06-07 17:20:03.077 
3          3      1                          19   2009-06-07 17:20:21.653 
4          5      1                          43   2009-06-06 17:20:08.670 
5          5      2                          1     2009-06-06 17:20:11.857 
6          6      2                          2   2009-06-06 17:20:44.123 
7          8      2                          1   2009-06-06 17:20:32.200 
8          8      2                          11   2009-06-12 17:20:52.200 
9          9      2                          1   2009-06-06 17:21:03.687 
10         5      2                          5   2009-06-14 17:21:04.263 表2的【编号1】和表1的【编号1】是关联的 
----------------------------- 
现在要查询出： 用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMac,数据编号1，数据编号2 
00001  3                      3                34                        0 
00002  2                      3                  62                        6 
00003  1                      1                  0                        2 
00004  1                      1                  0                        0 
00005  2                      2                  0                        12 
**/ 
各位帮帮忙！ 有筛选条件的，根据用户，日期1，日期3等为条件查询 
我写的是，用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMacfrom TerminalInfo group by 用户,可以得到结果，但是少了2列，我要把数据编号1，数据编号2都查询出来。 
各位帮帮忙。 
不知道上面的怎么就歪了，没有对齐，现在重新写了下。CSDN怎么没有预览功能的..希望各位能帮忙优化，我现在是做了多次查询，速度很慢，请高手帮忙！

解决方案 »

  1.   

    先说明下，pivot在SQL2000里面不能用，我的数据库是SQL2000的
      

  2.   

    数据编号1，数据编号2是--表2中字段数据编号这个字段是与一个类别表关联的
    该类别表为：DataType
    TypeID   typeName
    1         数据编号1
    2         数据编号2
    3         数据编号3
    ....
      

  3.   

    try：
    select 
        a.用户                                                   ,
        count(distinct   a.IP) as SumIP                          , 
        count(distinct a.终端) as SumMacfrom                     ,
        sum(case b.数据编号 when 1 then 1 else 0 end) as 数据编号1,
        sum(case b.数据编号 when 2 then 1 else 0 end) as 数据编号2
    from
        表1 a,表2 b
    where
        a.编号1=b.编号1
    group by 
        a.用户
      

  4.   

    create function f(@userid varchar(10))
    as return int
    begin 
    declare @cnt int
    select @cnt=isnull(sum(次数),0) from 表2 a inner join 表1 b on a.编号1=b.编号1 where a.用户 = @userid and 数据编号 = 1

    return @cnt
    end
    go
    create function f2(@userid varchar(10))
    as return int
    begin 
    declare @cnt int
    select @cnt=isnull(sum(次数),0) from 表2 a inner join 表1 b on a.编号1=b.编号1 where a.用户 = @userid and 数据编号 = 2

    return @cnt
    endgo
    select 用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMacfrom TerminalInfo ,dbo.f(用户),dbo.f2(用户)
    from 表1
    group by 用户
      

  5.   

    declare @t1 table(编号1 int,IP varchar(20),用户 varchar(10),终端 varchar(20),日期1 datetime,日期2 datetime)
    insert into @t1 select  1,'218.18.189.127','00001','00e08141fbEf','2009-06-08 17:10:47.997','2009-06-08 17:10:47.997' 
    insert into @t1 select  2,'218.15.120.190','00001','000B2F0AC6D0','2009-06-09 12:43:15.060','2009-06-09 12:22:56.000' 
    insert into @t1 select  3,'58.251.125.158','00002','000c29fb4ba3','2009-06-10 14:12:47.700','2009-06-09 18:21:01.000' 
    insert into @t1 select  4,'58.251.125.158','00002','002127aa6dce','2009-06-10 07:33:07.607','2009-06-10 07:33:07.607' 
    insert into @t1 select  5,'219.134.37.66 ','00002','005056C00001','2009-06-12 09:38:14.167','2009-06-08 10:17:16.000' 
    insert into @t1 select  6,'218.75.3.18   ','00003','00e08101fbd9','2009-06-11 12:38:37.327','2009-06-11 12:38:23.000' 
    insert into @t1 select  7,'222.208.6.55  ','00004','00e08101fb3f','2009-06-11 13:01:28.597','2009-06-08 17:16:04.000' 
    insert into @t1 select  8,'202.98.153.162','00005','00016cbb07ab','2009-06-11 19:46:59.273','2009-06-11 14:04:33.000' 
    insert into @t1 select  9,'202.98.153.171','00005','0004614b132b','2009-06-11 17:52:12.790','2009-06-10 21:36:54.000' 
    insert into @t1 select 10,'202.98.153.171','00001','000461769e41','2009-06-11 21:14:13.853','2009-06-10 14:08:03.000' declare @t2 table(标识 int,编号1 int,数据编号 int,次数 int,日期3 datetime)
    insert into @t2 select  1,1,1, 3,'2009-06-06 17:20:57.873' 
    insert into @t2 select  2,1,1,31,'2009-06-07 17:20:03.077' 
    insert into @t2 select  3,3,1,19,'2009-06-07 17:20:21.653' 
    insert into @t2 select  4,5,1,43,'2009-06-06 17:20:08.670' 
    insert into @t2 select  5,5,2, 1,'2009-06-06 17:20:11.857' 
    insert into @t2 select  6,6,2, 2,'2009-06-06 17:20:44.123' 
    insert into @t2 select  7,8,2, 1,'2009-06-06 17:20:32.200' 
    insert into @t2 select  8,8,2,11,'2009-06-12 17:20:52.200' 
    insert into @t2 select  9,9,2, 1,'2009-06-06 17:21:03.687' 
    insert into @t2 select 10,5,2, 5,'2009-06-14 17:21:04.263' select 
        a.用户,
        count(distinct   a.IP) as SumIP, 
        count(distinct a.终端) as SumMacfrom,
        sum(case b.数据编号 when 1 then b.次数 else 0 end) as 数据编号1,
        sum(case b.数据编号 when 2 then b.次数 else 0 end) as 数据编号2
    from
        @t1 a
    left join
        @t2 b
    on
        a.编号1=b.编号1
    group by 
        a.用户/*
    用户         SumIP       SumMacfrom  数据编号1       数据编号2
    ---------- ----------- ----------- ----------- ----------- 
    00001      3           3           34          0
    00002      2           3           62          6
    00003      1           1           0           2
    00004      1           1           0           0
    00005      2           2           0           13
    */
      

  6.   

        a.用户 ,
        count(distinct   a.IP) as SumIP , 
        count(distinct a.终端) as SumMacfro,
        sum(case b.数据编号 when 1 then 1 else 0 end) as 数据编号1,
        sum(case b.数据编号 when 2 then 1 else 0 end) as 数据编号2
      

  7.   

    create table t1(id1 int,IP varchar(20),userid varchar(10),terminal varchar(20),d1 datetime,d2 datetime)insert t1
    select 1   ,'218.18.189.127'   ,'00001','00e08141fbEf','2009-06-08 17:10:47.997','2009-06-08 17:10:47.997' 
    union all select 2   ,'218.15.120.190'   ,'00001','000B2F0AC6D0','2009-06-09 12:43:15.060','2009-06-09 12:22:56.000' 
    union all select 3   ,'58.251.125.158'   ,'00002','000c29fb4ba3','2009-06-10 14:12:47.700','2009-06-09 18:21:01.000' 
    union all select 4   ,'58.251.125.158'   ,'00002','002127aa6dce','2009-06-10 07:33:07.607','2009-06-10 07:33:07.607' 
    union all select 5   ,'219.134.37.66'    ,'00002','005056C00001','2009-06-12 09:38:14.167','2009-06-08 10:17:16.000' 
    union all select 6   ,'218.75.3.18'      ,'00003','00e08101fbd9','2009-06-11 12:38:37.327','2009-06-11 12:38:23.000' 
    union all select 7   ,'222.208.6.55'     ,'00004','00e08101fb3f','2009-06-11 13:01:28.597','2009-06-08 17:16:04.000' 
    union all select 8   ,'202.98.153.162'   ,'00005','00016cbb07ab','2009-06-11 19:46:59.273','2009-06-11 14:04:33.000' 
    union all select 9   ,'202.98.153.171'   ,'00005','0004614b132b','2009-06-11 17:52:12.790','2009-06-10 21:36:54.000' 
    union all select 10  ,'202.98.153.171'   ,'00001','000461769e41','2009-06-11 21:14:13.853','2009-06-10 14:08:03.000' gocreate table t2(id int,id1 int,datano int,degree int,d3 datetime )
    insert t2
    select   1          ,1     , 1                         , 3   ,'2009-06-06 17:20:57.873' 
    union all select 2          ,1     , 1                         , 31   ,'2009-06-07 17:20:03.077' 
    union all select 3          ,3     , 1                         , 19   ,'2009-06-07 17:20:21.653' 
    union all select 4          ,5     , 1                         , 43   ,'2009-06-06 17:20:08.670' 
    union all select 5          ,5     , 2                         , 1    ,'2009-06-06 17:20:11.857' 
    union all select 6          ,6     , 2                         , 2   ,'2009-06-06 17:20:44.123' 
    union all select 7          ,8     , 2                         , 1   ,'2009-06-06 17:20:32.200' 
    union all select 8          ,8     , 2                         , 11   ,'2009-06-12 17:20:52.200' 
    union all select 9          ,9     , 2                         , 1   ,'2009-06-06 17:21:03.687' 
    union all select 10         ,5     , 2                         , 5   ,'2009-06-14 17:21:04.263' go
    create function f(@userid varchar(10),@flag int)
    returns int
    as
    begin 
    declare @cnt int
    select @cnt=isnull(sum(degree),0) from t2 a inner join t1 b on a.id1=b.id1 where b.userid = @userid and datano = @flag

    return @cnt
    endgo
    select userid,count(distinct(IP)) as SumIP,count(distinct(terminal))as SumMacfrom ,dbo.f(userid,1),dbo.f(userid,2)
    from t1
    group by userid
    drop table t1,t2drop function f
    /*userid     SumIP       SumMacfrom                          
    ---------- ----------- ----------- ----------- ----------- 
    00001      3           3           34          0
    00002      2           3           62          6
    00003      1           1           0           2
    00004      1           1           0           0
    00005      2           2           0           13
    */
      

  8.   

    To:libin_ftsafe 
    你好，谢谢你了代码，是可以执行，不过如果我有很多的数据编号呢？
    那这样不是很麻烦？不好意思啊，要纠正个错误，我的数据编号列，刚才说的有点问题
    数据编号---来源与数据表DataInfo
    ID   Name      TypeID
    1    数据编号1   1
    2    数据编号2   2
    3    数据编号3   1.....
    类别表：DataType
    TypeID TypeName
    1       类别1
    2        类别2
    -------------------
    要最后的结果是：
    用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMac,类别1，类别2
    00001  3                      3                34                        0 
    00002  2                      3                  62                        6 
    00003  1                      1                  0                        2 
    00004  1                      1                  0                        0 
    00005  2                      2                  0                        12 
    **/ 应该是这样，希望大家再帮忙看看。
      

  9.   

    我想有通用的就好了，不然我有很多的TypeID就麻烦了，
    不是要很多的--这种语句吗?
    sum(case b.数据编号 when 2 then b.次数 else 0 end) as 数据编号2
      

  10.   


    动态交叉表:create table T1(编号1 int,IP varchar(20),用户 varchar(10),终端 varchar(20),日期1 datetime,日期2 datetime)
    insert into T1 select  1,'218.18.189.127','00001','00e08141fbEf','2009-06-08 17:10:47.997','2009-06-08 17:10:47.997' 
    insert into T1 select  2,'218.15.120.190','00001','000B2F0AC6D0','2009-06-09 12:43:15.060','2009-06-09 12:22:56.000' 
    insert into T1 select  3,'58.251.125.158','00002','000c29fb4ba3','2009-06-10 14:12:47.700','2009-06-09 18:21:01.000' 
    insert into T1 select  4,'58.251.125.158','00002','002127aa6dce','2009-06-10 07:33:07.607','2009-06-10 07:33:07.607' 
    insert into T1 select  5,'219.134.37.66 ','00002','005056C00001','2009-06-12 09:38:14.167','2009-06-08 10:17:16.000' 
    insert into T1 select  6,'218.75.3.18   ','00003','00e08101fbd9','2009-06-11 12:38:37.327','2009-06-11 12:38:23.000' 
    insert into T1 select  7,'222.208.6.55  ','00004','00e08101fb3f','2009-06-11 13:01:28.597','2009-06-08 17:16:04.000' 
    insert into T1 select  8,'202.98.153.162','00005','00016cbb07ab','2009-06-11 19:46:59.273','2009-06-11 14:04:33.000' 
    insert into T1 select  9,'202.98.153.171','00005','0004614b132b','2009-06-11 17:52:12.790','2009-06-10 21:36:54.000' 
    insert into T1 select 10,'202.98.153.171','00001','000461769e41','2009-06-11 21:14:13.853','2009-06-10 14:08:03.000' create table T2(标识 int,编号1 int,数据编号 int,次数 int,日期3 datetime)
    insert into T2 select  1,1,1, 3,'2009-06-06 17:20:57.873' 
    insert into T2 select  2,1,1,31,'2009-06-07 17:20:03.077' 
    insert into T2 select  3,3,1,19,'2009-06-07 17:20:21.653' 
    insert into T2 select  4,5,1,43,'2009-06-06 17:20:08.670' 
    insert into T2 select  5,5,2, 1,'2009-06-06 17:20:11.857' 
    insert into T2 select  6,6,2, 2,'2009-06-06 17:20:44.123' 
    insert into T2 select  7,8,2, 1,'2009-06-06 17:20:32.200' 
    insert into T2 select  8,8,2,11,'2009-06-12 17:20:52.200' 
    insert into T2 select  9,9,2, 1,'2009-06-06 17:21:03.687' 
    insert into T2 select 10,5,2, 5,'2009-06-14 17:21:04.263' create table T3(TypeID int,TypeName varchar(20))
    insert into T3 select 1,'类别1' 
    insert into T3 select 2,'类别2' 
    go
    --动态交叉表的实现
    declare @sql varchar(8000)
    set @sql='select a.用户,count(distinct   a.IP) as SumIP,count(distinct a.终端) as SumMacfrom'
    select @sql=@sql+',['+TypeName+']=sum(case b.数据编号 when '+rtrim(TypeID)+' then b.次数 else 0 end)' from T3
    set @sql=@sql+'from T1 a left join T2 b on a.编号1=b.编号1 group by a.用户'
    exec(@sql)
    go/*用户         SumIP       SumMacfrom  类别1         类别2         
    ---------- ----------- ----------- ----------- ----------- 
    00001      3           3           34          0
    00002      2           3           62          6
    00003      1           1           0           2
    00004      1           1           0           0
    00005      2           2           0           13
    */drop table T1,T2,T3
    go
      

  11.   

    select 
        a.用户,
        count(distinct   a.IP) as SumIP, 
        count(distinct a.终端) as SumMacfrom,
        sum(case b.数据编号 when 1 then b.次数 else 0 end) as 数据编号1,
        sum(case b.数据编号 when 2 then b.次数 else 0 end) as 数据编号2
    from @t1 a left join @t2 b on a.编号1=b.编号1
    group by a.用户
      

  12.   

    declare @sql varchar(8000)
    set @sql='select a.用户,count(distinct   a.IP) as SumIP,count(distinct a.终端) as SumMacfrom'
    select @sql=@sql+',['+TypeName+']=sum(case b.数据编号 when '+rtrim(TypeID)+' then b.次数 else 0 end)' from T3
    set @sql=@sql+'from T1 a left join T2 b on a.编号1=b.编号1 group by a.用户'
    exec(@sql)
    go这个可以帮你解决
      

  13.   

    SQL语句本身没什么可以优化的余地，只能在表上合理的创建索引，提高数据检索的效率。
      

  14.   

    你试一下我写的代码，我机器上运行结果跟你所要查询结果完全一致。select a.[member] 用户,count(distinct(IP)) IP,count(distinct(zd)) 终端,
    sum(case data1 when 1 then [count] else 0 end) 数据编号1,
    sum(case data1 when 2 then [count] else 0 end) 数据编号2
    from 表1 left join 
    (select a.member as m2,b.aid,b.data1,b.[count] from 表1 a,表2 b where a.ID=b.aid)c 
    on a.ID=c.aid and a.member=c.m2 group by member order by member
    如有不明白，请联系我
      

  15.   

    仔细测试后，发现与我想要的结果有区别，并没有加入TypeID计算，
    结果是错误的。
      

  16.   


    --有问题的：
    sum(case data1 when 1 then [count] else 0 end) 数据编号1, 
    when 1是错误的
    --应该是，
    select ID Name from DataInfo where typeID in （select TypeID from DataType)
      

  17.   

    Any issue?declare @sql varchar(8000)
    set @sql='select a.用户,count(distinct   a.IP) as SumIP,count(distinct a.终端) as SumMacfrom'
    select @sql=@sql+',['+TypeName+']=sum(case b.数据编号 when '+rtrim(TypeID)+' then b.次数 else 0 end)' from T3
    set @sql=@sql+'from T1 a left join T2 b on a.编号1=b.编号1 group by a.用户'--以print方式输出拼接的SQL语句
    print @sql--输出的SQL语句重新整理格式后，显示如下：
    /*
    select 
        a.用户,
        count(distinct   a.IP) as SumIP,
        count(distinct a.终端) as SumMacfrom,
        [类别1]=sum(case b.数据编号 when 1 then b.次数 else 0 end),
        [类别2]=sum(case b.数据编号 when 2 then b.次数 else 0 end)
    from 
        T1 a 
    left join 
        T2 b 
    on 
        a.编号1=b.编号1 
    group by 
        a.用户
    */
      

  18.   

    try:
    declare @sql varchar(8000)
    set @sql='select a.用户,count(distinct   a.IP) as SumIP,count(distinct a.终端) as SumMacfrom'
    select @sql=@sql+',['+TypeName+']=sum(case b.数据编号 when '+rtrim(TypeID)+' then b.[count] else 0 end)' from T3
    set @sql=@sql+'from T1 a left join (select c.member as m2,d.aid,d.data1,d.[count] from T1 c,T2 d where c.ID=d.aid) b'
                 +' on a.ID=b.aid and a.member=b.m2 group by a.用户'
      

  19.   

    Yes, data is not what I want!
      

  20.   

    Like this数据编号---来源与数据表DataInfo 
    ID  Name      TypeID 
    1    数据编号1  1 
    2    数据编号2  2 
    3    数据编号3  1 ..... 
    类别表：DataType 
    TypeID TypeName 
    1      类别1 
    2        类别2 
    ------------------- 
    ---------
    上面的我的查询结果，是错的，你按照我的那个结果，也错了，现在应该是4张表关联
      

  21.   

    表2中--字段【数据编号】，与表DataInfo--Name字段关联
    表DataInfo表TypeID与DataType表TypeID关联。
    如此！
      

  22.   

    create table T1(编号1 int,IP varchar(20),用户 varchar(10),终端 varchar(20),日期1 datetime,日期2 datetime)
    insert into T1 select  1,'218.18.189.127','00001','00e08141fbEf','2009-06-08 17:10:47.997','2009-06-08 17:10:47.997' 
    insert into T1 select  2,'218.15.120.190','00001','000B2F0AC6D0','2009-06-09 12:43:15.060','2009-06-09 12:22:56.000' 
    insert into T1 select  3,'58.251.125.158','00002','000c29fb4ba3','2009-06-10 14:12:47.700','2009-06-09 18:21:01.000' 
    insert into T1 select  4,'58.251.125.158','00002','002127aa6dce','2009-06-10 07:33:07.607','2009-06-10 07:33:07.607' 
    insert into T1 select  5,'219.134.37.66 ','00002','005056C00001','2009-06-12 09:38:14.167','2009-06-08 10:17:16.000' 
    insert into T1 select  6,'218.75.3.18   ','00003','00e08101fbd9','2009-06-11 12:38:37.327','2009-06-11 12:38:23.000' 
    insert into T1 select  7,'222.208.6.55  ','00004','00e08101fb3f','2009-06-11 13:01:28.597','2009-06-08 17:16:04.000' 
    insert into T1 select  8,'202.98.153.162','00005','00016cbb07ab','2009-06-11 19:46:59.273','2009-06-11 14:04:33.000' 
    insert into T1 select  9,'202.98.153.171','00005','0004614b132b','2009-06-11 17:52:12.790','2009-06-10 21:36:54.000' 
    insert into T1 select 10,'202.98.153.171','00001','000461769e41','2009-06-11 21:14:13.853','2009-06-10 14:08:03.000' create table T2(标识 int,编号1 int,数据编号 int,次数 int,日期3 datetime)
    insert into T2 select  1,1,1, 3,'2009-06-06 17:20:57.873' 
    insert into T2 select  2,1,1,31,'2009-06-07 17:20:03.077' 
    insert into T2 select  3,3,1,19,'2009-06-07 17:20:21.653' 
    insert into T2 select  4,5,1,43,'2009-06-06 17:20:08.670' 
    insert into T2 select  5,5,2, 1,'2009-06-06 17:20:11.857' 
    insert into T2 select  6,6,2, 2,'2009-06-06 17:20:44.123' 
    insert into T2 select  7,8,2, 1,'2009-06-06 17:20:32.200' 
    insert into T2 select  8,8,2,11,'2009-06-12 17:20:52.200' 
    insert into T2 select  9,9,2, 1,'2009-06-06 17:21:03.687' 
    insert into T2 select 10,5,2, 5,'2009-06-14 17:21:04.263' create table T3(TypeID int,TypeName varchar(20))
    insert into T3 select 1,'类别1' 
    insert into T3 select 2,'类别2' create table T4(ID int,Name varchar(10),TypeID int)
    insert into T4 select 1,'数据编号1',1 
    insert into T4 select 2,'数据编号2',2 
    insert into T4 select 3,'数据编号3',1 
    go
    --动态交叉表的实现
    declare @sql varchar(8000)
    set @sql='select a.用户,count(distinct   a.IP) as SumIP,count(distinct a.终端) as SumMacfrom'
    select @sql=@sql+',['+TypeName+']=sum(case c.TypeID when '+rtrim(TypeID)+' then b.次数 else 0 end)' from T3
    set @sql=@sql+'from T1 a cross join T4 c left join T2 b on a.编号1=b.编号1 and b.数据编号=c.ID group by a.用户'
    exec(@sql)
    go/*用户         SumIP       SumMacfrom  类别1         类别2         
    ---------- ----------- ----------- ----------- ----------- 
    00001      3           3           34          0
    00002      2           3           62          6
    00003      1           1           0           2
    00004      1           1           0           0
    00005      2           2           0           13
    */drop table T1,T2,T3,T4
    go
      

  23.   

    I'm sorry.The results are still not correct!
      

  24.   

    好的。
    新帖已经写好了，地址是：
    http://topic.csdn.net/u/20090618/17/30d7901a-a9ed-4f68-a62c-6371a5534d32.html
    麻烦你了，兄弟