select 学号,case when 科目='数学' then 成绩 else 0 end as 数学, case when 科目='数学' then 成绩 else 0 end as 数学, case when 科目='数学' then 成绩 else 0 end as 数学, case when 科目='数学' then 成绩 else 0 end as 数学, 数学 语文 外语 C语言
对不起,刚才按错键了select 学号,sum(数学) as 数学,sum(语文) as 语文, sum(外语) as 外语,sum(C语言) as C语言 from (select 学号,case when 科目='数学' then 成绩 else 0 end as 数学, case when 科目='语文' then 成绩 else 0 end as 语文, case when 科目='外语' then 成绩 else 0 end as 外语, case when 科目='C语言' then 成绩 else 0 end as C语言 from tablename ) as a group by 学号
CSDN不能删除发错的回复,遗憾!!!
Haier; 你是在张瑞敏手下干吗? 羡慕透了我最欣赏的两个中国人:1.朱容基2.张瑞敏
你用的是access是把,用交叉表就可以语句如下: transform sum(成绩) as 成绩 select 科目 from table group by 科目 pivot 学号
如果你是用ACCESS好办,ACCESS中有生成交叉表的向导,或用以下语句也可以: TRANSFORM Sum(score.fscore) AS fscore SELECT score.fstudentid, Sum(score.fscore) AS [总计: fscore] FROM score GROUP BY score.fstudentid PIVOT score.fclassid;
谢谢各位!!! "海阔天空" ,你很认真,谢谢. 我会尝试你的方法. "我很迷茫 " 的方法也应该可行, 在 Access里面, 可直接可视化的建立交叉表(我刚尝试过,该方法应该最为快捷!).用语言 transform sum(成绩) as 成绩 select 科目 from table group by 科目 pivot 学号 和"按钮工厂"的, 我正准备试!
SQL Server: SELECT 表.学号,MAX(T1.成绩) AS 数学, MAX(T2.成绩) AS 语文, MAX(T3.成绩) AS 外语 ,MAX(T4.成绩) AS C语言 FROM 表 LEFT JOIN 表 AS T1 ON 表.学号 = T1.学号 AND T1.科目='数学' LEFT JOIN 表 AS T2 ON 表.学号 = T2.学号 AND T2.科目='语文' LEFT JOIN 表 AS T3 ON 表.学号 = T3.学号 AND T3.科目='外语' LEFT JOIN 表 AS T4 ON 表.学号 = T4.学号 AND T4.科目='C语言' GROUP BY 表.学号或:SELECT DISTINCT 表.学号,T1.成绩 AS 数学, T2.成绩 AS 语文, T3.成绩 AS 外语 ,T4.成绩 AS C语言 FROM 表 LEFT JOIN 表 AS T1 ON 表.学号 = T1.学号 AND T1.科目='数学' LEFT JOIN 表 AS T2 ON 表.学号 = T2.学号 AND T2.科目='语文' LEFT JOIN 表 AS T3 ON 表.学号 = T3.学号 AND T3.科目='外语' LEFT JOIN 表 AS T4 ON 表.学号 = T4.学号 AND T4.科目='C语言'
过去的, 怎么查呀?(好像只能查最近的100条)
case when 科目='数学' then 成绩 else 0 end as 数学,
case when 科目='数学' then 成绩 else 0 end as 数学,
case when 科目='数学' then 成绩 else 0 end as 数学,
数学 语文 外语 C语言
sum(外语) as 外语,sum(C语言) as C语言
from
(select 学号,case when 科目='数学' then 成绩 else 0 end as 数学,
case when 科目='语文' then 成绩 else 0 end as 语文,
case when 科目='外语' then 成绩 else 0 end as 外语,
case when 科目='C语言' then 成绩 else 0 end as C语言
from tablename
) as a
group by 学号
你是在张瑞敏手下干吗?
羡慕透了我最欣赏的两个中国人:1.朱容基2.张瑞敏
transform sum(成绩) as 成绩 select 科目 from table group by 科目 pivot 学号
能告诉我张瑞敏是谁吗?恕我孤陋寡闻!朱容基倒听说过,还是一不小心看电视看到的。这个裸体狼头骆驼说话颠三倒四的,会是谁呢?!
以前没看到过。
TRANSFORM Sum(score.fscore) AS fscore
SELECT score.fstudentid, Sum(score.fscore) AS [总计: fscore]
FROM score
GROUP BY score.fstudentid
PIVOT score.fclassid;
"海阔天空" ,你很认真,谢谢.
我会尝试你的方法.
"我很迷茫 " 的方法也应该可行,
在 Access里面, 可直接可视化的建立交叉表(我刚尝试过,该方法应该最为快捷!).用语言
transform sum(成绩) as 成绩 select 科目 from table group by 科目 pivot 学号 和"按钮工厂"的, 我正准备试!
SELECT 表.学号,MAX(T1.成绩) AS 数学, MAX(T2.成绩) AS 语文, MAX(T3.成绩) AS 外语 ,MAX(T4.成绩) AS C语言
FROM 表 LEFT JOIN 表 AS T1 ON 表.学号 = T1.学号 AND T1.科目='数学'
LEFT JOIN 表 AS T2 ON 表.学号 = T2.学号 AND T2.科目='语文'
LEFT JOIN 表 AS T3 ON 表.学号 = T3.学号 AND T3.科目='外语'
LEFT JOIN 表 AS T4 ON 表.学号 = T4.学号 AND T4.科目='C语言'
GROUP BY 表.学号或:SELECT DISTINCT 表.学号,T1.成绩 AS 数学, T2.成绩 AS 语文, T3.成绩 AS 外语 ,T4.成绩 AS C语言
FROM 表 LEFT JOIN 表 AS T1 ON 表.学号 = T1.学号 AND T1.科目='数学'
LEFT JOIN 表 AS T2 ON 表.学号 = T2.学号 AND T2.科目='语文'
LEFT JOIN 表 AS T3 ON 表.学号 = T3.学号 AND T3.科目='外语'
LEFT JOIN 表 AS T4 ON 表.学号 = T4.学号 AND T4.科目='C语言'
简直成了留言板!
人气之旺,吃惊!
Thanks !
题目是:
关于vb (Access) 的两个打印问题.
敬请继续关注!!!
不过好像SQL数据库中没有这种功能
另外,SQL也可返回交叉表(要用到临时表),很多书上有专门介绍。