请教老鸟: 我有一个表 tbl字段如下
str_ID strName strTitle strUrl strClick
1 zs title1 a 3
2 zs title1 a 3
3 ls title2 b 3
4 ik title1 c 2
5 ii title3 f 2
6 ls title3 b 5我现在想把 strClick 的值算出它的合,然后取出总记录,还有把 strName ,strtitle strUrl 各个的 总和(除去重复的值)如选出的结果为
strClcik = 18 总记录:6 strName =4 strtitle=3 strUrl = 4应该怎么写??谢谢!
str_ID strName strTitle strUrl strClick
1 zs title1 a 3
2 zs title1 a 3
3 ls title2 b 3
4 ik title1 c 2
5 ii title3 f 2
6 ls title3 b 5我现在想把 strClick 的值算出它的合,然后取出总记录,还有把 strName ,strtitle strUrl 各个的 总和(除去重复的值)如选出的结果为
strClcik = 18 总记录:6 strName =4 strtitle=3 strUrl = 4应该怎么写??谢谢!
select StrClick=sum(StrClick),总记录=sum(1),StrName=(select count(distinct StrName) from tbl),StrTitle=(select count(distinct StrTitle) from tbl),StrUrl=(select count(distinct StrUrl) from tbl) from tbl
from tbl
insert tbl select 1, 'zs', 'title1', 'a', 3
union all select 2, 'zs', 'title1', 'a', 3
union all select 3, 'ls', 'title2', 'b', 3
union all select 4, 'ik', 'title1', 'c', 2
union all select 5, 'ii', 'title3', 'f', 2
union all select 6, 'ls', 'title3', 'b', 5select
strClcik = (select sum(strClick) from tbl),
strName =(select count(distinct strName) from tbl),
strTitle=(select count(distinct strTitle) from tbl),
strUrl=(select count(distinct strUrl) from tbl)--result
strClcik strName strTitle strUrl
----------- ----------- ----------- -----------
18 4 3 4(1 row(s) affected)
drop table tb
gocreate table tb
(
str_ID int,
strName varchar(10),
strTitle varchar(10),
strUrl varchar(10),
strClick int
)insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(1,'zs','title1','a',3)
insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(2,'zs','title1','a',3)
insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(3,'ls','title2','b',3)
insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(4,'ik','title1','c',2)
insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(5,'ii','title3','f',2)
insert into tb(str_ID,strName,strTitle,strUrl,strClick) values(6,'ls','title3','b',5)select sum(strClick) as strClick,
count(*) as 总记录,
count(distinct strName) as strName,
count(distinct strTitle) as strTitle,
count(distinct strUrl) as strUrl
from tbdrop table tb
strClick 总记录 strName strTitle strUrl
----------- ----------- ----------- ----------- -----------
18 6 4 3 4(所影响的行数为 1 行)
但是到了 Access 又不行了? Access 应该如何解决呢?? 谢放
count(*) as 总记录,
count(distinct strName) as strName,
count(distinct strTitle) as strTitle,
count(distinct strUrl) as strUrl
from tb1