SELECT METERNLLJ,COUNT(DISTINCT CLIENTNO) AS COUNT FROM
(SELECT c.CLIENTNO AS CLIENTNO,(c.METERNLLJ-d.METERNLLJ) AS METERNLLJ FROM
(SELECT DISTINCT T.METERNLLJ,T.METERID,T.AREAGUID,V.CLIENTNO FROM TWATERNOWFOURYEAR T ,TDOOR_METER D,VCLIENTINFO V WHERE T.METERID=D.METERNO AND T.AREAGUID=D.AREAGUID AND D.AREAGUID=V.AREAGUID AND D.CLIENTNO=V.CLIENTNO AND T.DDATE='2010-10-7' AND T.DEVICETYPE=16 AND T.DEVICESTATUS=0 AND V.CLIENTCATONEID =2 ) c,
(SELECT DISTINCT T.METERNLLJ,T.METERID,T.AREAGUID,V.CLIENTNO FROM TWATERNOWFOURYEAR T ,TDOOR_METER D,VCLIENTINFO V WHERE T.METERID=D.METERNO AND T.AREAGUID=D.AREAGUID AND D.AREAGUID=V.AREAGUID AND D.CLIENTNO=V.CLIENTNO AND T.DDATE='2010-10-1' AND T.DEVICETYPE=16 AND T.DEVICESTATUS=0 AND V.CLIENTCATONEID =2) d
WHERE c.METERID=d.METERID AND c.CLIENTNO=d.CLIENTNO AND c.AREAGUID=d.AREAGUID ) GROUP BY METERNLLJ ORDER BY METERNLLJ如下结果,
METERNLLJ COUNT
1 -93 1
2 -2 1
3 -1 2
4 0 2252
5 1 3344
6 2 1449
7 3 440
8 4 136
9 5 54
10 6 34
11 7 12
12 8 12
13 9 11
14 10 3
15 11 2
16 12 2
17 13 2
18 15 2
19 16 1
20 17 1
21 19 1
22 20 1
23 82 1
24 90 1
25 93 1
=======手工计算sum(COUNT)=7766
解决方案 »
- 一般性网络错误,ConnectionRead(recv())
- 是否存在这样的sql语句
- 急急!!在线等,请教MS-SQL server2005存储过程实现拆分计算
- 求教一个单表的sql语句
- 我能将数据库中的数据表设置为只读,不能更改吗?
- 如何从一个表中统计每周不同用户操作的次数,再根据次数的不同把用户的信息分别添加到不同的表中?
- select convert(text,content) as content from...数据少取了
- sql sever时间转换,在线等精通帝
- 我想导入一个 word文件,这个表怎莫作阿,怎莫导入阿!!!!!!!!1
- Access SQL 高深问题,一定加分????
- SqlExpress_2005_SP2安装后怎么创建数据库呀?
- 奇怪的level,请告诉是怎么用的?
SELECT COUNT(DISTINCT CLIENTNO) AS COUNT FROM
(SELECT c.CLIENTNO AS CLIENTNO,(c.METERNLLJ-d.METERNLLJ) AS METERNLLJ FROM
(SELECT DISTINCT T.METERNLLJ,T.METERID,T.AREAGUID,V.CLIENTNO FROM TWATERNOWFOURYEAR T ,TDOOR_METER D,VCLIENTINFO V WHERE T.METERID=D.METERNO AND T.AREAGUID=D.AREAGUID AND D.AREAGUID=V.AREAGUID AND D.CLIENTNO=V.CLIENTNO AND T.DDATE='2010-10-7' AND T.DEVICETYPE=16 AND T.DEVICESTATUS=0 AND V.CLIENTCATONEID =2 ) c,
(SELECT DISTINCT T.METERNLLJ,T.METERID,T.AREAGUID,V.CLIENTNO FROM TWATERNOWFOURYEAR T ,TDOOR_METER D,VCLIENTINFO V WHERE T.METERID=D.METERNO AND T.AREAGUID=D.AREAGUID AND D.AREAGUID=V.AREAGUID AND D.CLIENTNO=V.CLIENTNO AND T.DDATE='2010-10-1' AND T.DEVICETYPE=16 AND T.DEVICESTATUS=0 AND V.CLIENTCATONEID =2) d
WHERE c.METERID=d.METERID AND c.CLIENTNO=d.CLIENTNO AND c.AREAGUID=d.AREAGUID ) COunt=7595CLIENTNO有重复值,区别在于下面的没有分组,结果不一样
insert into tb values(1 ,-93, 1)
insert into tb values(2 ,-2 ,1 )
insert into tb values(3 ,-1 ,2 )
insert into tb values(4 ,0 ,2252)
insert into tb values(5 ,1 ,3344 )
insert into tb values(6 ,2 ,1449 )
insert into tb values(7 ,3 ,440 )
insert into tb values(8 ,4 ,136 )
insert into tb values(9 ,5 ,54 )
insert into tb values(10, 6 ,34 )
insert into tb values(11, 7 ,12 )
insert into tb values(12, 8 ,12 )
insert into tb values(13, 9 ,11 )
insert into tb values(14, 10, 3 )
insert into tb values(15, 11, 2 )
insert into tb values(16, 12, 2 )
insert into tb values(17, 13, 2 )
insert into tb values(18, 15, 2 )
insert into tb values(19, 16, 1 )
insert into tb values(20, 17, 1 )
insert into tb values(21, 19, 1 )
insert into tb values(22, 20, 1 )
insert into tb values(23, 82, 1 )
insert into tb values(24, 90, 1 )
insert into tb values(25, 93, 1 )
goselect sum([count]) from tbdrop table tb/*
-----------
7766(所影响的行数为 1 行)
*/
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
这四条数据 查询1 总数 为 2 + 2 = 4 (分组1内 aaa 与 bbb 不重复,分组内aaa 与 ccc 不重复), 查询2 总数为 4 - 1 = 3 (aaa 重复了)