SQL查询语句 [英] SQL query statement

问题描述

我该如何更改

SID Name Math English French
1   Sam  16   17      19
2   Tom  18   14      12
3   Al   90   33      2

收件人:

SID subject   Mark
1   Math      16
1   English   17
1   French    19
2   Math      18
2   English   14
2   French    12
3   Math      90
3   English   33
3   French    2  

使用SQL(如果可能，则使用MYsql和MS Access)?

using SQL (MYsql and MS Access if possible) ?

谢谢.

推荐答案

SELECT sid, 'Math' as subject, math as mark
FROM your_table
UNION ALL
SELECT sid, 'English' as subject, english as mark
FROM your_table
UNION ALL
SELECT sid, 'French' as subject, french as mark
FROM your_table

但是，问题的根本原因是数据库设计错误.这些主题不应该放在首位，而应该像您期望的输出一样存储在表中.

But the root cause of your problem is a wrong database design. Those subjects shouldn't be columns in the first place and should be stored in a table very much like your desired output.

修改

那它是做什么的?

SELECT sid, 'Math' as subject, math as mark
FROM your_table

返回sid列，这是一个虚拟"列，其硬编码值'Math'的名称为subject.由于您尚未将值'Math'存储在某处，因此必须对其进行硬编码.然后，最后它还会使用名称mark选择列math.请注意math和'Math'之间的区别-由于单引号，一个是一列，另一个是字符串文字.

Returns the sid column, a "virtual" column with the hardcoded value 'Math' that is given the name subject. As you have not stored the value 'Math' somewhere, this had to be hardcoded. Then at last it also selects the column math using the name mark instead. Note the difference between math and 'Math' - one is a column the other one a string literal because of the single quotes.

这是针对所有三个主题完成的(如果您有四个主题，则在UNION中需要四个部分)

This is done for all three subjects (if you had four subjects, you'd need four parts in the UNION)

UNION ALL将所有三个SELECT组合到一个查询中.通过显式地将其放入派生表(或内联视图)中，andr解决方案(已被不了解它的人不屑一顾)使这一点更加清晰.

The UNION ALL combines all three SELECTs into one single query. andr solution (which has been downvoted by someone who didn't understand it) makes this even clearer by explicitely putting that into a derived table (or inline view).

单独运行每个SELECT，以查看各个部分的功能.

Run each SELECT on its own to see what the individual parts are doing.

部分as mark被称为列别名"，还可以用于从联接中的不同表中检索具有相同名称的列，但在结果集中仍具有唯一名称.

The part as mark is called a "column alias" and can also be used to retrieve columns with the same name from different tables in a join and still have unique names in the result set.

这篇关于SQL查询语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！