如何从sql server中获取数据 [英] how to grab the data from sql server

问题描述

我有像这样的SQL表：

i have sql table like:

Date            Pagename
6/6/2015         home.aspx
6/6/2015         home.aspx
6/6/2015         home.aspx
6/6/2015         home.aspx
6/6/2015         home.aspx
6/6/2015         homebn.aspx
6/6/2015         homebn.aspx
6/6/2015         homebn.aspx
6/6/2015         homebn.aspx
7/6/2015         welcome.aspx
7/6/2015         welcome.aspx
7/6/2015         welcome.aspx
7/6/2015         welcome.aspx
7/6/2015         welcomebn.aspx

现在我想从这个表中获取数据我的输出应该是：

now i want to fetch data from this table and my output should be:

Date		Pagename            Bn     En    
6/6/2015        home.aspx	    4      5
7/6/2015     welcomebn.aspx     1      4

当找到相同的页面名称时如何计算同一页面

how can i count samepage when it found the same page name

推荐答案

计数很简单，只需将COUNT函数与GROUP BY一起使用：

http://www.w3schools.com/sql/sql_func_count.asp [ ^ ]

http://www.w3schools.com/sql/sql_groupby.asp [ ^ ]



但首先，您必须确切地确定您想要实现的目标以及达到这一点的规则：为什么SQL应该返回home.aspx而不是homebn.aspx而不是welcomebn.aspx而不是welcome.aspx并不是很明显，这可能会对你的事件产生重大影响ual query。

Counting is simple, just use the COUNT function in conjunction with GROUP BY:
http://www.w3schools.com/sql/sql_func_count.asp[^]
http://www.w3schools.com/sql/sql_groupby.asp[^]

But first, you will have to work out exactly what you want to achieve and what the rules are to get to that point: it is not at all obvious why SQL should return "home.aspx" instead of "homebn.aspx" but also "welcomebn.aspx" instead of "welcome.aspx" and that is likely to have a big effect on your eventual query.

select date, Replace(Pagename,'bn.','.'),
count(case when charindex('bn.',Pagename,0) > 0 then 1 else null end),
count(case when charindex('bn.',Pagename,0) = 0 then 1 else null end)
FROM Pages
group by date, Replace(Pagename,'bn.','.')

This will only give you the base name without 'bn' in

Output

Date            Pagename        bn    en              
2015-06-06      home.aspx       4     5
2015-06-07      welcome.aspx    1     4

问候

Kevin

Regards
Kevin

这篇关于如何从sql server中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！