跨表查询并排序及统计数据优化 不好意思,文中TermID,表示DataTable表的ID列 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我都不想用in,不知道有什么好的办法?in效率不高,我知道,不好意思啊,是有点乱!---------关键的问题是,还需要排序,不知道大家怎么想呢?给我个思路吧。我有用【存储过程分页】,但是这样查询出来的不是表,不能分页,于是我想创建临时表用作分页和排序,但是这临时表怎么插入数据呢?如果要按上面的方式,我查询的时候,速度会下降的。 嗯,好的。我写在后面。------------------------------------------------/**表1编号1 IP 用户 终端 日期1 日期21 218.18.189.127 00001 00e08141fbEf 2009-06-08 17:10:47.997 2009-06-08 17:10:47.9972 218.15.120.190 00001 000B2F0AC6D0 2009-06-09 12:43:15.060 2009-06-09 12:22:56.0003 58.251.125.158 00002 000c29fb4ba3 2009-06-10 14:12:47.700 2009-06-09 18:21:01.0004 58.251.125.158 00002 002127aa6dce 2009-06-10 07:33:07.607 2009-06-10 07:33:07.6075 219.134.37.66 00002 005056C00001 2009-06-12 09:38:14.167 2009-06-08 10:17:16.0006 218.75.3.18 00003 00e08101fbd9 2009-06-11 12:38:37.327 2009-06-11 12:38:23.0007 222.208.6.55 00004 00e08101fb3f 2009-06-11 13:01:28.597 2009-06-08 17:16:04.0008 202.98.153.162 00005 00016cbb07ab 2009-06-11 19:46:59.273 2009-06-11 14:04:33.0009 202.98.153.171 00005 0004614b132b 2009-06-11 17:52:12.790 2009-06-10 21:36:54.00010 202.98.153.171 00001 000461769e41 2009-06-11 21:14:13.853 2009-06-10 14:08:03.000----------------------------------------------------表2标识 编号1 数据编号 次数 日期31 1 1 3 2009-06-06 17:20:57.8732 1 1 31 2009-06-07 17:20:03.0773 3 1 19 2009-06-07 17:20:21.6534 5 1 43 2009-06-06 17:20:08.6705 5 2 1 2009-06-06 17:20:11.8576 6 2 2 2009-06-06 17:20:44.1237 8 2 1 2009-06-06 17:20:32.2008 8 2 11 2009-06-12 17:20:52.2009 9 2 1 2009-06-06 17:21:03.68710 5 2 5 2009-06-14 17:21:04.263表2的【编号1】和表1的【编号1】是关联的-----------------------------现在要查询出:用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMac,数据编号1,数据编号200001 3 3 34 000002 2 3 62 600003 1 1 0 200004 1 1 0 000005 2 2 0 12**/各位帮帮忙! 嗯,好的。我写在后面。------------------------------------------------/**表1编号1 IP 用户 终端 日期1 日期21 218.18.189.127 00001 00e08141fbEf 2009-06-08 17:10:47.997 2009-06-08 17:10:47.9972 218.15.120.190 00001 000B2F0AC6D0 2009-06-09 12:43:15.060 2009-06-09 12:22:56.0003 58.251.125.158 00002 000c29fb4ba3 2009-06-10 14:12:47.700 2009-06-09 18:21:01.0004 58.251.125.158 00002 002127aa6dce 2009-06-10 07:33:07.607 2009-06-10 07:33:07.6075 219.134.37.66 00002 005056C00001 2009-06-12 09:38:14.167 2009-06-08 10:17:16.0006 218.75.3.18 00003 00e08101fbd9 2009-06-11 12:38:37.327 2009-06-11 12:38:23.0007 222.208.6.55 00004 00e08101fb3f 2009-06-11 13:01:28.597 2009-06-08 17:16:04.0008 202.98.153.162 00005 00016cbb07ab 2009-06-11 19:46:59.273 2009-06-11 14:04:33.0009 202.98.153.171 00005 0004614b132b 2009-06-11 17:52:12.790 2009-06-10 21:36:54.00010 202.98.153.171 00001 000461769e41 2009-06-11 21:14:13.853 2009-06-10 14:08:03.000----------------------------------------------------表2标识 编号1 数据编号 次数 日期31 1 1 3 2009-06-06 17:20:57.8732 1 1 31 2009-06-07 17:20:03.0773 3 1 19 2009-06-07 17:20:21.6534 5 1 43 2009-06-06 17:20:08.6705 5 2 1 2009-06-06 17:20:11.8576 6 2 2 2009-06-06 17:20:44.1237 8 2 1 2009-06-06 17:20:32.2008 8 2 11 2009-06-12 17:20:52.2009 9 2 1 2009-06-06 17:21:03.68710 5 2 5 2009-06-14 17:21:04.263表2的【编号1】和表1的【编号1】是关联的-----------------------------现在要查询出:用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMac,数据编号1,数据编号200001 3 3 34 000002 2 3 62 600003 1 1 0 200004 1 1 0 000005 2 2 0 12**/各位帮帮忙!有筛选条件的,根据用户,日期1,日期3等为条件查询我写的是,用户,count(distinct(IP)) as SumIP,count(distinct(终端))as SumMacfrom TerminalInfo group by 用户,可以得到结果,但是少了2列,我要把数据编号1,数据编号2都查询出来。各位帮帮忙。不知道上面的怎么就歪了,没有对齐,现在重新写了下。CSDN怎么没有预览功能的.. declare @t table(编号1 int,IP varchar(20),用户 varchar(10),终端 varchar(20),日期1 datetime,日期2 datetime)insert @t select 1, '218.18.189.127', '00001', '00e08141fbEf', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 2, '218.15.120.190', '00001', '000B2F0AC6D0', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 3, '58.251.125.158', '00002', '000c29fb4ba3', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 4, '58.251.125.158', '00002', '002127aa6dce', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 5, '219.134.37.66', '00002', '005056C00001', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 6, '218.75.3.18', '00003', '00e08101fbd9', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 7, '222.208.6.55', '00004', '00e08101fb3f', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 8, '202.98.153.162', '00005', '00016cbb07ab', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 9, '202.98.153.171', '00005', '0004614b132b', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union allselect 10, '202.98.153.171', '00001', '000461769e41', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' declare @t1 table(标识 int, 编号1 int, 数据编号 int, 次数 int, 日期3 datetime)insert @t1select 1, 1, 1, 3, '2009-06-06 17:20:57.873' union allselect 2, 1, 1, 31, '2009-06-06 17:20:57.873' union allselect 3, 3, 1, 19, '2009-06-06 17:20:57.873' union allselect 4, 5, 1, 43, '2009-06-06 17:20:57.873' union allselect 5, 5, 2, 1, '2009-06-06 17:20:57.873' union allselect 6, 6, 2, 2, '2009-06-06 17:20:57.873' union allselect 7, 8, 2, 1, '2009-06-06 17:20:57.873' union allselect 8, 8, 2, 11, '2009-06-06 17:20:57.873' union allselect 9, 9, 2, 1, '2009-06-06 17:20:57.873' union allselect 10, 5, 2, 5, '2009-06-06 17:20:57.873' ;with cte1 as(select t.编号1,tmp1.用户,sumip,sumMac from (select 用户,count(distinct(IP)) as SumIP, count(distinct(终端))as SumMac from @t t left join @t1 t1 on t.编号1=t1.编号1 group by 用户) tmp1 join @t t on tmp1.用户=t.用户),cte2 as(select c1.用户,sumip,sumMac,数据编号,次数 from cte1 c1 left join @t1 t1 on c1.编号1=t1.编号1)select 用户,SumIP,SumMac,isnull([1],0) 数据编号1,isnull([2],0) 数据编号2 from cte2pivot( sum(次数) for 数据编号 in([1],[2])) p/*用户 SumIP SumMac 数据编号1 数据编号2---------- ----------- ----------- ----------- -----------00001 3 3 34 000002 2 3 62 600003 1 1 0 200004 1 1 0 000005 2 2 0 13(5 行受影响)*/ TO:xiequan2--------------不好意思,最后的我看不太懂,能不能讲讲?select 用户,SumIP,SumMac,isnull([1],0) 数据编号1,isnull([2],0) 数据编号2 from cte2 pivot ( sum(次数) for 数据编号 in([1],[2]) ) p ----------这些! Pivot研究:http://hi.baidu.com/ai_li7758521/blog/item/4eecac8632665f3667096efa.html 怎么无人来关注了,pivot在sql2000上无法运行的。 触发器出了小问题要问…… SQL2000 update的问题 如何删除临时表上的唯一约束 有没有条件表达式的函数 一个查询语句 sql server插入多行??? 如何用一个SQL语句将一个字段的标识属性去掉 SQL Server出现异常,内存占用非常之高(300多M,平常是100多M),可能是什么原因?怎样分析? 高分!!!-- 关于快照的刷新 SQL密码保存问题 关于树形结构存储的路径问题 高难度SQL语句求助!
---------
关键的问题是,还需要排序,不知道大家怎么想呢?给我个思路吧。我有用【存储过程分页】,但是这样查询出来的不是表,不能分页,于是我想创建临时表用作分页和排序,
但是这临时表怎么插入数据呢?如果要按上面的方式,我查询的时候,速度会下降的。
嗯,好的。我写在后面。------------------------------------------------
/**
表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
编号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怎么没有预览功能的..
insert @t
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-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 3, '58.251.125.158', '00002', '000c29fb4ba3', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 4, '58.251.125.158', '00002', '002127aa6dce', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 5, '219.134.37.66', '00002', '005056C00001', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 6, '218.75.3.18', '00003', '00e08101fbd9', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 7, '222.208.6.55', '00004', '00e08101fb3f', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 8, '202.98.153.162', '00005', '00016cbb07ab', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 9, '202.98.153.171', '00005', '0004614b132b', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' union all
select 10, '202.98.153.171', '00001', '000461769e41', '2009-06-08 17:10:47.997', '2009-06-08 17:10:47.997' declare @t1 table(标识 int, 编号1 int, 数据编号 int, 次数 int, 日期3 datetime)
insert @t1
select 1, 1, 1, 3, '2009-06-06 17:20:57.873' union all
select 2, 1, 1, 31, '2009-06-06 17:20:57.873' union all
select 3, 3, 1, 19, '2009-06-06 17:20:57.873' union all
select 4, 5, 1, 43, '2009-06-06 17:20:57.873' union all
select 5, 5, 2, 1, '2009-06-06 17:20:57.873' union all
select 6, 6, 2, 2, '2009-06-06 17:20:57.873' union all
select 7, 8, 2, 1, '2009-06-06 17:20:57.873' union all
select 8, 8, 2, 11, '2009-06-06 17:20:57.873' union all
select 9, 9, 2, 1, '2009-06-06 17:20:57.873' union all
select 10, 5, 2, 5, '2009-06-06 17:20:57.873'
;
with cte1 as
(
select t.编号1,tmp1.用户,sumip,sumMac from (select 用户,count(distinct(IP)) as SumIP, count(distinct(终端))as SumMac from @t t left join @t1 t1 on t.编号1=t1.编号1 group by 用户) tmp1 join @t t on tmp1.用户=t.用户
),
cte2 as
(
select c1.用户,sumip,sumMac,数据编号,次数 from cte1 c1 left join @t1 t1 on c1.编号1=t1.编号1
)
select 用户,SumIP,SumMac,isnull([1],0) 数据编号1,isnull([2],0) 数据编号2 from cte2
pivot
(
sum(次数) for 数据编号 in([1],[2])
) p/*
用户 SumIP 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 13(5 行受影响)
*/
--------------
不好意思,最后的我看不太懂,能不能讲讲?
select 用户,SumIP,SumMac,isnull([1],0) 数据编号1,isnull([2],0) 数据编号2 from cte2
pivot
(
sum(次数) for 数据编号 in([1],[2])
) p
----------
这些!