复杂的内连接? [英] Complicated inner join??
问题描述
使用Access DB
我的数据库中有一个名为members的表。在该表中,我有2个表格我将使用用户名和用户名。和点
现在,我还有一个名为all_matches的表。此表包含每场比赛
报告。超过25,000人。我有一个用户名字段和结果字段字段a
" username1"字段和extra_match字段。
我需要做的是做一个从表中获取用户名的查询
" members"并使用用户名找到:
在会员表中的字段,我需要获得统计数据
给我以下内容。
1.)总共有多少场比赛。这只是all_matches表中
用户名字段的计数。由于人们报告比赛胜利
或输了。
2.)多少胜利,其中extra_match = 0.这将是一个用户名
$ b all_matches表中的$ b,其中结果是=''赢''和extra_match = 0.
3.)多少次胜利,其中extra_match = 1.这将是all_matches表中的用户名
的计数其中结果 =''赢''和extra_match = 1
4.)多少损失,其中extra_match = 0.这将是all_matches表中
用户名的计数"结果" ='''失去''和
extra_match = 0 ..
5.)多少损失,其中extra_match = 1.这将是
all_matches表中的
用户名,其中结果为=''失去'和
extra_match = 1.
6.)然后将胜利分成总比赛并获得胜利%
>
现在我做了这个,使用前50名因此它不是太强烈,因为在all_matches表中有很多行。但我不能做的就是获得正确的分类
。但我认为如果我可以在访问中执行此操作,而不是在asp
页面上执行此操作,则会更容易。
我有一个是在另一个数据库上为我完成,但我无法修改它
这个数据库。
我可以向你展示我在asp页面上所做的脚本,但是相反,它很邋b,而且无论如何都不行。所以我需要帮助在
访问中创建这个SQL作为查询。
我知道这很多,
但是任何帮助将不胜感激。我知道基本的SQL,但我不知道内部
和外连接。
谢谢
bam
Jeff写道:Ok gang。这里有一些复杂的东西,至少对我而言。
使用Access DB
我的数据库中有一个名为members的表。在该表格中,我将使用2个表格用户名和点
请在表格描述中包含数据类型
表:成员
列:用户名文字
分数?长?单身?
现在,我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match领域。
同样的交易。 username1的目的是什么?
我需要做的是使用表格中的用户名进行查询
" members"并找到:
使用用户名在成员表中的字段中,我需要获取给我以下内容的统计数据。
1.)总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。
SELECT mb.username,count(m.username)as TotalMatches
来自会员的mb左连接匹配为m
ON mb.username = m.username
GROUP BY mb.username
2.)多少次胜利,其中extra_match = 0.这将是一个计数在all_matches表中
用户名,其中结果是=''赢''和
extra_match = 0。
< FROM和GROUP子句不会改变,所以我不会重复然后>
SELECT mb.username,count(m .username)作为TotalMatches,
SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins
3.)有多少胜利,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果为=''赢''和
extra_match = 1
SELECT mb.username,count(m.username)为TotalMatches,
SUM( Iif([结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match ] = 1,1,0))作为TotalExtraWins
4.)多少损失,其中extra_match = 0.这将是all_matches表中
用户名的计数,其中结果 ; =''丢失''和
extra_match = 0 ..
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([outcome] =''Win''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([results] =''Win''AND [extra_match] = 1,1,0))作为TotalExtraWins,
SUM(Iif([结果] =''损失''和[extra_match] = 0,1,0))作为TotalLosses <
5.)多少损失,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果是=''失去''和
extra_match = 1。
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([outcome] =''Win''和[extra_match ] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0))作为TotalExtraWins,
SUM(Iif([结果] =''损失''和[extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([结果] = ''亏损'和[extra_match] = 1,1,0))作为TotalExtraLosses
6.)然后将胜利分成总游戏并获得胜利%
是胜利定义为TotalWins + TotalExtraWins?如果是这样的话:
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([outcome] =''Win''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0))作为TotalExtraWins,>
SUM(Iif([结果] =''亏损'和[extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([results] =' 'Loss''和[extra_match] = 1,1,0))作为TotalExtraLosses,
(SUM(Iif([results] =''Win''和[extra_match] = 0,1, 0))+
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0)))/
count(m .username)为WinPercent
< snip>我知道这很多,
但是任何帮助都会受到赞赏。我知道基本的SQL,但我不知道
内部和外部联接。
:-)
那是IS 基本SQL ...
这是GROUP BY和使用Iif将其带入高级
领域。 :-)
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱,因此我不经常检查它。通过发布到新闻组,您将获得更快的回复。
" Bob Barrows [MVP] " <再****** @ NOyahoo.SPAMcom>在消息中写道
新闻:%2 **************** @ TK2MSFTNGP10.phx.gbl ...Jeff写道:Ok gang。这里有一些复杂的东西,至少对我而言。
使用Access DB
我的数据库中有一个名为members的表。在该表格中,我将使用2个表格用户名和积分
请在表格描述中包含数据类型
表:成员
栏目:用户名文字
分数?长?单身?
ok。 username是55 ch文本,points是长整数。
现在,我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match领域。
同样的交易。 username1的目的是什么?
用户名1的目的是报告的人是用户名...
他报告说他演奏的是username1。我接管了这个,并且
无法将所有内容排序,并使其更容易阅读。
什么我需要做的是,使用表格中的用户名进行查询
" members"并找到:
使用用户名在成员表中的字段中,我需要获取给我以下内容的统计数据。
1.)总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。
SELECT mb.username,count(m.username)为TotalMatches
FROM成员为mb左连接匹配为m
ON mb。 username = m.username
GROUP BY mb.username
2.)多少次胜利,其中extra_match = 0.这将是
extra_match = 0.
< FROM和GROUP子句不会改变,所以我不会重复然后>
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([outcome] =''Win''和[extra_match] = 0,1,0))为TotalWins
3.)多少次胜利,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果为=''赢''和
extra_match = 1
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([结果] ] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0) )作为TotalExtraWins
4.)多少损失,其中extra_match = 0.这将是all_matches表中
用户名的计数,其中结果为=''丢失''和
extra_match = 0 ..
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif( [结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1, 0))作为TotalExtraWins,
SUM(Iif([results] =''Loss''和[extra_match] = 0,1,0))作为TotalLosses
5.)多少损失,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果是=''丢失''和
extra_match = 1.
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0 ))作为TotalExtraWins,
SUM(Iif([结果] =''亏损'和[extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([结果] =' 'Loss''和[extra_match] = 1,1,0))作为TotalExtraLosses
6.)然后将胜利分成总游戏并获得胜利%
是胜利定义为TotalWins + TotalExtraWins?如果是这样的话:
是的,TotalWins将是extra_match = 0和extra_match = 1的总和。
SELECT mb.username,count( m.username)作为TotalMatches,
SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0))作为TotalExtraWins,
SUM(Iif([结果] =''丢失''和[extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([结果] =''亏损'和[extra_match] = 1,1,0))作为TotalExtraLosses,
(SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))+
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0)))/
count(m.username)为WinPercent
< snip>我知道这很多,
但任何帮助都会受到赞赏。我知道基本的SQL,但我不知道
内部和外部联接。
:-)
这是基本SQL。 ......这是GROUP BY和使用Iif将其带入高级领域。 :-)
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱,因此我不经常检查它。通过发布到新闻组,您将得到更快的回复。
好的,所以我需要将所有这些放入sql访问。希望没有什么
随着我提出的问题后的信息而改变。实际上,
用户名1甚至不应该为此发挥作用我不会想到。
感谢Bob
Jeff
现在如果我想按TotalWins排序,我会在
GROUP BY之前添加它吗?
Bob Barrows [MVP]" <再****** @ NOyahoo.SPAMcom>在消息中写道
新闻:%2 **************** @ TK2MSFTNGP10.phx.gbl ...Jeff写道:Ok gang。这里有一些复杂的东西,至少对我而言。
使用Access DB
我的数据库中有一个名为members的表。在该表格中,我将使用2个表格用户名和点
请在表的描述中包含数据类型
表:成员
列:用户名文本
点数?长? Single?
现在,我还有一个名为all_matches的表。此表包含
每个匹配报告。超过25,000人。我有一个用户名字段a
结果字段" username1字段和extra_match field。
相同的交易。 username1的目的是什么?
我需要做的是,使用表格中的用户名进行查询
" members"并找到:
使用用户名在成员表中的字段中,我需要获取给我以下内容的统计数据。
1.)总共进行了多少次比赛。这只是all_matches表中用户名字段的计数。由于人们报告
匹配胜负。
SELECT mb.username,count(m.username)为TotalMatches
FROM成员为mb left join匹配为m
ON mb.username = m.username
GROUP BY mb.username
2.)多少次胜利,其中extra_match = 0。这将是all_matches表中
用户名的计数,其中结果是=''赢''和
extra_match = 0.
< FROM和GROUP子句不会改变,所以我不会重复然后>
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([outcome] =''Win''和[extra_match] = 0,1,0))为TotalWins
3.)多少次胜利,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果为=''赢''和
extra_match = 1
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([结果] ] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0) )作为TotalExtraWins
4.)多少损失,其中extra_match = 0.这将是all_matches表中
用户名的计数,其中结果为=''丢失''和
extra_match = 0 ..
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif( [结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1, 0))作为TotalExtraWins,
SUM(Iif([results] =''Loss''和[extra_match] = 0,1,0))作为TotalLosses
5.)多少损失,其中extra_match = 1.这将是all_matches表中
用户名的计数,其中结果是=''丢失''和
extra_match = 1.
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0 ))作为TotalExtraWins,
SUM(Iif([结果] =''亏损'和[extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([结果] =' 'Loss''和[extra_match] = 1,1,0))作为TotalExtraLosses
6.)然后将胜利分成总游戏并获得胜利%
是胜利定义为TotalWins + TotalExtraWins?如果是这样的话:
SELECT mb.username,count(m.username)为TotalMatches,
SUM(Iif([result] =''Win''和[extra_match] = 0,1,0))作为TotalWins,
SUM(Iif([结果] =''赢''和[extra_match] = 1,1,0))作为TotalExtraWins,
SUM(Iif([结果] =''损失''AND [extra_match] = 0,1,0))作为TotalLosses,
SUM(Iif([outcome] =''Loss''和[extra_match] = 1,1,0))作为TotalExtraLosses,< br(>(SUM(Iif([结果] =''赢''和[extra_match] = 0,1,0))+
SUM(Iif([结果] =''赢''和[extra_match ] = 1,1,0)))/
计数(m.username)为WinPercent
< snip>我知道这很多,
但任何帮助将不胜感激。我知道基本的SQL,但我不知道
内部和外部联接。
:-)
这是基本SQL。 ......这是GROUP BY和使用Iif将其带入高级领域。 :-)
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱,因此我不经常检查它。通过发布到新闻组,您将得到更快的回复。
Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2 tables I
will be using "username" and "points"
Now, I also have a table called all_matches. This table contains every match
report. Over 25,000 of them. I have a "username" field an "outcome" field an
"username1" field and "extra_match" field.
What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain stats
that give me the following.
1.) how many total matches played. Which would simply be a count of the
username field in the all_matches table. Since people report the match win
or lose.
2.) how many wins, where extra_match = 0. This would be a count of username
in the all_matches table where "outcome" = ''Win'' and extra_match=0.
3.) how many wins, where extra_match = 1. This would be a count of username
in the all_matches table where "outcome" = ''Win'' and extra_match=1
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
6.) Then divide the wins into the total games and get a win %
now I did this, using top 50 so it wasn''t too intensive, since there are so
many lines in the all_matches table. But what I can''t do is get the sort
right. But I think if i can do this in access, instead of doing it on an asp
page, it will be easier.
I had one that was done for me on another DB, but I was unable to modify it
for this DB.
I can show you the script of what I did on the asp page, but it is rather
sloppy, and it doesn''t work anyway. So I need help creating this SQL within
access as a query.
I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know inner
and outer joins.
THanks
bam
Jeff wrote:Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"
Please include datatypes in your descriptions of your tables
Table: Members
Columns: username Text
points Number? Long? Single?
Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.
Same deal. What is the purpose of "username1"?
What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.
1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.
SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username
2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.
<the FROM and GROUP clauses will not change, so I will not repeat then>
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins
3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses
5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses
6.) Then divide the wins into the total games and get a win %
Is "wins" defined as TotalWins + TotalExtraWins? if so:
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
<snip> I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.
:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...Jeff wrote:Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"
Please include datatypes in your descriptions of your tables
Table: Members
Columns: username Text
points Number? Long? Single?
ok. username is 55 ch text, points is long integer.
Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.
Same deal. What is the purpose of "username1"?
the purpose of username1 is that the person reporting is the username... the
person he is reporting that he played is username1. I took over this, and
haven''t been able to sort everything out, and make it easier to read yet.
What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.
1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.
SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.
<the FROM and GROUP clauses will not change, so I will not repeat then>
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses6.) Then divide the wins into the total games and get a win %
Is "wins" defined as TotalWins + TotalExtraWins? if so:
yes, TotalWins would be the total from extra_match = 0 and extra_match = 1.
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
<snip>I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.
:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.
Ok, so I need to put all these into the sql in access. Hopefully nothing
changed with the info I gave after your questions. In all actuality, the
username1 shouldn''t even come into play for this I don''t think.
Thanks Bob
Jeff
Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY??
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...Jeff wrote:Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2
tables I will be using "username" and "points"
Please include datatypes in your descriptions of your tables
Table: Members
Columns: username Text
points Number? Long? Single?
Now, I also have a table called all_matches. This table contains
every match report. Over 25,000 of them. I have a "username" field an
"outcome" field an "username1" field and "extra_match" field.
Same deal. What is the purpose of "username1"?
What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain
stats that give me the following.
1.) how many total matches played. Which would simply be a count of
the username field in the all_matches table. Since people report the
match win or lose.
SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username2.) how many wins, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=0.
<the FROM and GROUP clauses will not change, so I will not repeat then>
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins3.) how many wins, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Win'' and
extra_match=1
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=0..
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = ''Lose'' and
extra_match=1.
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses6.) Then divide the wins into the total games and get a win %
Is "wins" defined as TotalWins + TotalExtraWins? if so:
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]=''Loss'' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]=''Win'' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]=''Win'' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
<snip>I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don''t know
inner and outer joins.
:-)
That IS "basic SQL" ...
It''s the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don''t check it very often. You will get a
quicker response by posting to the newsgroup.
这篇关于复杂的内连接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!