Chinaunix

标题: [SQL GROUP BY]一个经典的SQL问题 [打印本页]
作者: gqywf    时间: 2006-03-03 08:55
标题: [SQL GROUP BY]一个经典的SQL问题
我有一张表ttt，共有四个字段:

StudentID(学生), ClassID(班级), Mark(考试分数),fen(品德分数)

我想得到每个班级考试60分以上和品德70分以上的学生人数，能用一条SQL解决么？

[ 本帖最后由 gqywf 于 2006-3-3 09:07 编辑 ]
作者: yejr    时间: 2006-03-03 08:56
轻松，用group by，详细的看手册
另，不要拿标题搞噘头

[ 本帖最后由 yejr 于 2006-3-3 08:57 编辑 ]
作者: macrodba    时间: 2006-03-03 08:59
select StudentID(学生), ClassID(班级), Mark(考试分数) from ttt group by StudentID(学生), ClassID(班级), Mark(考试分数)
where mark > 60
作者: gqywf    时间: 2006-03-03 10:00
我把问题修正了一下，刚才没打完。
作者: rardge    时间: 2006-03-03 12:12
select classid, count(mark) as count from
(
  select classid, mark from s where mark > 60
  union select classid, fen from s where fen > 70
) as tmp
group by classid;

一个思路

[ 本帖最后由 rardge 于 2006-3-3 12:13 编辑 ]
作者: gqywf    时间: 2006-03-03 14:03
union 是什么意思，手册里怎么没有？
作者: gqywf    时间: 2006-03-03 14:10
是不是要mysql 5.0才行啊？
作者: rardge    时间: 2006-03-03 14:19
6.4.1.2 UNION 句法

SELECT ...
UNION [ALL]
SELECT ...
  [UNION
   SELECT ...]

UNION 在 MySQL 4.0.0 中被实现。

UNION 用于将多个 SELECT 语句的结果联合到一个结果集中。

在 SELECT 中的 select_expression 部分列出的列必须具有同样的类型。第一个 SELECT 查询中使用的列名将作为结果集的列名返回。

SELECT 命令是一个普通的选择命令，但是有下列的限制：

只有最后一个 SELECT 命令可以有 INTO OUTFILE。
如果你不为 UNION 使用关键词 ALL，所有返回的记录行将是唯一的，就好像你为整个返回集使用了一个 DISTINCT。如果你指定了 ALL，那么你将得到从所有使用的 SELECT 语句中返回的所有匹配记录行。

如果你希望对整个 UNION 结果使用一个 ORDER BY，你应该使用圆括号：

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;
作者: gqywf    时间: 2006-03-03 15:43
奶奶的，我的手册怎么没有，我去找个新的手册

谢谢了。。。
作者: Namelessxp    时间: 2006-03-04 08:51

  2. SELECT COUNT(*),CASE WHEN Mark>60 THEN 1 WHEN fen>70 THEN 2 ELSE 3 END as gid FROM ttt GROUP BY (gid);
复制代码
作者: gqywf    时间: 2006-03-07 08:59
(
SELECT class, count( mark ) , count( fen )
FROM DATA WHERE mark >60
GROUP BY class
)
UNION (

SELECT class, count( mark ) , count( fen )
FROM DATA WHERE fen =70
GROUP BY class
)

弄了好几天，上边的语句终于可以运行，
不过结果是两个语句insert起来的样子，
我想要的是两个语句jion起来的样子,有办法吗？

楼上的结果只有2行记录，不行。

[ 本帖最后由 gqywf 于 2006-3-7 13:40 编辑 ]
作者: Namelessxp    时间: 2006-03-07 10:33
原帖由 gqywf 于 2006-3-7 08:59 发表
(
SELECT student, count( mark ) , count( fen )
FROM DATA WHERE mark >60
GROUP BY class
)
UNION (

SELECT student, count( mark ) , count( fen )
FROM DATA WHERE fen =70
GROUP BY class
...


没看清，照你的要求，在classID上进行分组就行了，具体的WHEN条件，可以自己组合

  2. SELECT COUNT(*),CASE WHEN Mark>60 THEN 1 WHEN fen>70 THEN 2 ELSE 3 END as gid,classID FROM ttt GROUP BY classID;
复制代码
作者: gqywf    时间: 2006-03-07 13:51
我明白了，原来的问题没说明白，应该是类似这样：
SELECT
class, count( mark ) as mmm , count( fen ) as fff
FROM DATA
WHERE （？？）
GROUP BY class
条件是mark>60 的合计为mmm，fen>70的合计为fff，
mark 和 fen 单独合计，互不影响，但我不知道应该怎样写。

[ 本帖最后由 gqywf 于 2006-3-7 13:57 编辑 ]
作者: gqywf    时间: 2006-03-07 13:56
我这几天都快被搞晕了，本来用循环可以搞定，但是有几万个学生，几百个班级

运行太慢，受不了。
作者: rardge    时间: 2006-03-07 14:33
classid mark fen
1          61    60
1          50    71
1          70    68

你要的效果是不是
classid mark fen
1          2      1
作者: gqywf    时间: 2006-03-07 14:55
对啊，应该怎么弄啊。
作者: rardge    时间: 2006-03-07 15:01
select classid, count(case when mark > 60 then 1 end) as m, count(case when fen > 70 then 1 end) as f from tbname group by classid
受 Namelessxp 启发而写，我老是不记得去用 case when，一直习惯用我的那种方法来合成结果。
作者: gqywf    时间: 2006-03-07 15:20
谢谢了，我去试试先。。。
作者: gqywf    时间: 2006-03-07 15:23
太好了，终于搞定，高兴ing。。。。。。。。。。。
作者: Namelessxp    时间: 2006-03-07 15:26
原帖由 rardge 于 2006-3-7 15:01 发表
select classid, count(case when mark > 60 then 1 end) as m, count(case when fen > 70 then 1 end) as f from tbname group by classid
受 Namelessxp 启发而写，我老是不记得去用 case when，一直习惯 ...


结果正确
附上我的验证用的，不知道为啥，不爱在count里面用case when，下面是跟PHP数组结合做的

  2. $dbhost = 'localhost';
  3. $dbuser = 'root';
  4. $dbpass = '';
  5. $dbused = 'test';
  6. $tbname = 'ttt';

  8. $sql = "SELECT COUNT(*) as num,CASE WHEN Mark>60 AND fen>70 THEN 1 WHEN fen>70 THEN 2 WHEN Mark>60 THEN 3 ELSE 4 END as gid,classID FROM $tbname GROUP BY classID,gid";
  9. $dh = mysql_connect($dbhost,$dbuser,$dbpass) or die("Connect fail");
  10. mysql_select_db('test',$dh) or die("Select DB fail");
  11. $query = mysql_query($sql) or die("Query fail");
  12. while ($row = mysql_fetch_array($query)){
  13.         $result[$row['classID']][$row['gid']] = $row['num'];
  14. }
  15. mysql_close($dh);
  16. echo '<plaintext>';
  17. var_dump($result);
  18. if (is_array($result)){
  19.         foreach ($result as $key => $val){
  20.                 echo "班级:$key \n";
  21.                 echo "fen:".($val[1]+$val[2]+0);
  22.                 echo " Mark:".($val[1]+$val[3]+0);
  23.                 echo " Other:".($val[4]+0)."\n";
  24.         }
  25. }
  26. exit;
复制代码

  2. CREATE TABLE `ttt` (
  3.   `StudentID` int(10) default NULL,
  4.   `ClassID` int(10) default NULL,
  5.   `Mark` int(10) default NULL,
  6.   `fen` int(10) default NULL
  7. ) TYPE=MyISAM
复制代码
作者: zhanglianxiao    时间: 2007-01-29 13:14
原帖由 rardge 于 2006-3-7 15:01 发表
select classid, count(case when mark > 60 then 1 end) as m, count(case when fen > 70 then 1 end) as f from tbname group by classid
受 Namelessxp 启发而写，我老是不记得去用 case when，一直习惯 ...

谢谢了，学习了



欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2