我想做一个如同下表的操作:
select sum(yuwen),sum(shuxue),xuehao,banji,sex from
biao group by xingming
出现这样的错误
列 'biao.xuehao '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 'biao.banji '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 'biao.sex '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 怎么做才能得到这些列的查询结果
select sum(yuwen),sum(shuxue),xuehao,banji,sex from
biao group by xingming
出现这样的错误
列 'biao.xuehao '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 'biao.banji '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 'biao.sex '在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 怎么做才能得到这些列的查询结果
biao group by xingming,banji,sex就是正确的语法,但是,出来结果并不一定是你想要的.
biao group by xingming,xuehao,banji,sex
或者
select sum(yuwen),sum(shuxue),max(xuehao),max(banji),max(sex) from
biao
group by xingming
biao group by xingming,banji,sex 如果这么做的话,其实结果同这条语句是一样的效果select * from biao
2,select sum(yuwen),sum(shuxue),max(xuehao),max(banji),max(sex) from
biao
group by xingming这么做的话就不会有我想要的结果了真就没有办法解决了吗?
把姓名相同的学生的记录统计(语文成绩)(数学成绩)
并且把该学生的班级,学生性别这些相关信息也显示出来
这样SQL语句我应该怎么写呢?
select * from #cj
select #cj.yuwen,xuehao from biao,#cj where #cj.xingming=biao.xingming
biao group by xingming,xuehao,banji,sex
这句就是对的!非聚合函数的列都要加在group by后面!
select * from (select sum(yuwen) as sumyuwen,sum(shuxue) as sumshuxue ,xingming from biao group by xingming ) as t left join (select banji,xuehao,sex from biao) as b on t.xingming=b.xingmingsumyuwen sumshuxue xingming banji xuehao sex
89 107 a 01 001 1
89 107 a 01 002 1
89 107 a 02 003 0
89 107 a 02 004 1
59 86 b 01 005 0
59 86 b 02 006 1
66 77 c 03 007 1 原表是:
xuehao banji sex xingming yuwen shuxue 001 01 1 a 12 21
002 01 1 a 22 31
003 02 0 a 11 22
004 02 1 a 44 33
005 01 0 b 55 32
006 02 1 b 4 54
007 03 1 c 66 77
1 1 100.0000 0
1 1 99.0000 0
2 1 65.0000 0
4 2 65.0000 1
4 2 65.0000 1
4 2 100.0000 1
1 1 100.0000 0
1 1 199.0000 0
4 2 199.0000 1
4 3 199.0000 1
4 2 100.0000 1原表:
姓名 班级 语文成绩 数学成绩 性别
4 2 100.0000 100.0000 1
4 2 99.0000 99.0000 1
2 1 99.0000 99.0000 0
4 2 65.0000 65.0000 1
1 1 100.0000 100.0000 0
1 1 100.0000 100.0000 0这样是把统计出来的结果和原表的记录都显示出来了
(当然我要做的东西不是这个是类似这个表的)
(select sum(yuwen),sum(shuxue) from biao group by xingming ) t1, biao t2
where t1.xinming=t2.xinming
select stuName ,sum(chScr),max(class),sum(maScr),max(sex) from tt group by stuName
--创建环境
drop table tt
create table tt(stuName int,class int,chScr int,maScr int,sex int)
insert into tt select 4,2,100.0000,100.0000,1
union select 4,2,99.0000,99.0000,1
union select 2,1,99.0000,99.0000,0
union select 4,2,65.0000,65.0000,1
union select 1,1,100.0000,100.0000,0
union select 1,1,100.0000,100.0000,0
--执行语句
select stuName as 姓名 ,max(class) as 班级,sum(chScr) as 语文成绩,sum(maScr) as 数学成绩,max(case sex when 1 then '男' else '女' end) as 性别
from tt group by stuName
order by stuName desc
--执行结果
4 2 264 264 男
2 1 99 99 女
1 1 200 200 女--(所影响的行数为 3 行)
<!-- #include file="Inc/Head.asp" -->
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<%
if Request("save")<>"" then
set rs=server.CreateObject("adodb.recordset")
sql="update Bs_Company set "& Request("save") & "='"& Request("nr")&"'"
set rs=conn.execute(sql)
set rs=nothing
end if
%>
<table border="0" cellpadding="0" cellspacing="0" align="center" style="border-collapse: collapse" id="AutoNumber3" height="0" width="98%">
<tr>
<td>
<%
action=Request("action")
select case action
case "profile"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td align="center"><a href="others.asp?action=paymethord"><strong><font color="#FFFFFF">公
司 介 绍</font></strong></a></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
<form name="form1" method="post" action="?save=profile&" onSubmit=" return ConfirmMsg('你确认要修改吗?');">
<tr align="center">
<td bgcolor="#FFFFFF"> <p>
<%set rs=server.CreateObject("adodb.recordset")
rs.open "select " & Request("action") & " from Bs_Company",conn,1,1%>
<INPUT name="nr" type="hidden" id="nr" value="<% = Server.HtmlEncode(rs("profile")) %>">
<iframe ID="nr" src="htmledit/ewebeditor.asp?id=nr&style=standard" frameborder="0" scrolling="no" width="650" HEIGHT="400"></iframe>
<%rs.close
set rs=nothing
Conn.Close
Set Conn = Nothing %>
<br>
<input type="submit" name="Submit" value="提 交">
<input type="button" name="Submit2" value="返 回" onClick="javascript:history.go(-1)">
</p></td>
</tr>
</form>
</table></td>
</tr>
</table>
<% case "ceo"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td> <div align="center"><strong><font color="#FFFFFF">总 裁 致 辞</font></strong></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
<form name="form1" method="post" action="?save=ceo" onSubmit=" return ConfirmMsg('你确认要修改吗?');">
<tr>
<td bgcolor="#FFFFFF"> <div align="center">
<p>
<%set rs=server.CreateObject("adodb.recordset")
rs.open "select " & Request("action") & " from Bs_Company",conn,1,1%>
<INPUT name="nr" type="hidden" id="nr" value="<% = Server.HtmlEncode(rs("ceo")) %>">
<iframe ID="nr" src="htmledit/ewebeditor.asp?id=nr&style=standard" frameborder="0" scrolling="no" width="650" HEIGHT="400"></iframe>
<%rs.close
set rs=nothing
Conn.Close
Set Conn = Nothing %>
<br>
<input type="submit" name="Submit3" value="提 交">
<input type="button" name="Submit22" value="返 回" onClick="javascript:history.go(-1)">
</p>
</div></td>
</tr>
</form>
</table></td>
</tr>
</table>
<% case "culture"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td height="15">
<div align="center"><strong><font color="#FFFFFF">公 司 文 化</font></strong></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellspacing="1" bgcolor="#6699cc">
<tr>
<td> <div align="center"><a target="main" href="Bs_Company.asp?UrlName=Organize"><font color="#FFFFFF"><strong>组
织 机 构</strong></font></a></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
<!-- #include file="Inc/Head.asp" -->
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<%
if Request("save")<>"" then
set rs=server.CreateObject("adodb.recordset")
sql="update Bs_Company set "& Request("save") & "='"& Request("nr")&"'"
set rs=conn.execute(sql)
set rs=nothing
end if
%>
<table border="0" cellpadding="0" cellspacing="0" align="center" style="border-collapse: collapse" id="AutoNumber3" height="0" width="98%">
<tr>
<td>
<%
action=Request("action")
select case action
case "profile"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td align="center"><a href="others.asp?action=paymethord"><strong><font color="#FFFFFF">公
司 介 绍</font></strong></a></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
<form name="form1" method="post" action="?save=profile&" onSubmit=" return ConfirmMsg('你确认要修改吗?');">
<tr align="center">
<td bgcolor="#FFFFFF"> <p>
<%set rs=server.CreateObject("adodb.recordset")
rs.open "select " & Request("action") & " from Bs_Company",conn,1,1%>
<INPUT name="nr" type="hidden" id="nr" value="<% = Server.HtmlEncode(rs("profile")) %>">
<iframe ID="nr" src="htmledit/ewebeditor.asp?id=nr&style=standard" frameborder="0" scrolling="no" width="650" HEIGHT="400"></iframe>
<%rs.close
set rs=nothing
Conn.Close
Set Conn = Nothing %>
<br>
<input type="submit" name="Submit" value="提 交">
<input type="button" name="Submit2" value="返 回" onClick="javascript:history.go(-1)">
</p></td>
</tr>
</form>
</table></td>
</tr>
</table>
<% case "ceo"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td> <div align="center"><strong><font color="#FFFFFF">总 裁 致 辞</font></strong></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
<form name="form1" method="post" action="?save=ceo" onSubmit=" return ConfirmMsg('你确认要修改吗?');">
<tr>
<td bgcolor="#FFFFFF"> <div align="center">
<p>
<%set rs=server.CreateObject("adodb.recordset")
rs.open "select " & Request("action") & " from Bs_Company",conn,1,1%>
<INPUT name="nr" type="hidden" id="nr" value="<% = Server.HtmlEncode(rs("ceo")) %>">
<iframe ID="nr" src="htmledit/ewebeditor.asp?id=nr&style=standard" frameborder="0" scrolling="no" width="650" HEIGHT="400"></iframe>
<%rs.close
set rs=nothing
Conn.Close
Set Conn = Nothing %>
<br>
<input type="submit" name="Submit3" value="提 交">
<input type="button" name="Submit22" value="返 回" onClick="javascript:history.go(-1)">
</p>
</div></td>
</tr>
</form>
</table></td>
</tr>
</table>
<% case "culture"%>
<table width="100%" border="0" align="center" cellpadding="1" cellspacing="1" bgcolor="#6699cc">
<tr>
<td height="15">
<div align="center"><strong><font color="#FFFFFF">公 司 文 化</font></strong></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellspacing="1" bgcolor="#6699cc">
<tr>
<td> <div align="center"><a target="main" href="Bs_Company.asp?UrlName=Organize"><font color="#FFFFFF"><strong>组
织 机 构</strong></font></a></div></td>
</tr>
<tr>
<td height="83" bgcolor="#FFFFFF"><br> <table width="95%" border="0" align="center" cellpadding="1" cellspacing="0" bgcolor="#FFFFFF">
聚合函数产生的值是一个聚合的值,也就是一个值,但是其他的列的值不止一个,
这样,在一个二维表中就无法表达。
例如:
----------------------
sum(成绩)|班级|姓名|
-----------------------
246 |4 |4 |
-----------------------
|4 |4 |
-----------------------
|4 |4 |
-----------------------
这样的表数据是无法返回的,
------------------
非著名数据库专家,立志成为数据库专家,参考参考