数据表行列互换的一个思考

最新推荐文章于 2021-12-10 17:45:31 发布

lylon

最新推荐文章于 2021-12-10 17:45:31 发布

阅读量1k

点赞数

文章标签：产品 join null 面试 sql table

本文链接：https://blog.csdn.net/lylon/article/details/1401292

版权

刚刚整理完一个Wap购物系统程序，准备去找几家公司面试。
为了了解一下行情，找了一些面试题做。找到两个这样的SQL题，

第一个网址为http://zhidao.baidu.com/question/13242062.html
具体内容如下：
一道凊华同方的SQL面试题（请高手帮忙解决）
悬赏分：0 - 解决时间：2006-10-3 23:49
已知一个表的结构为：
姓名科目成绩
张三语文 20
张三数学 30
张三英语 50
李四语文 70
李四数学 60
李四英语 90
怎样通过select语句把他变成以下结构：
姓名语文数学英语
张三 20 30 50
李四 70 60 90

网上解答如下：
设表名为 student

select A.姓名,A.成绩 as 语文,B.成绩 as 数学,C.成绩 as 英语
from student A,student B,student C
where A.姓名=B.姓名 and B.姓名=C.姓名
and A.科目='语文' and B.科目='数学'
and C.科目='英语'

看似合理，但是，却忘了考虑实际情况，如果有某个同学的成绩只有一门或两门（而且这种情况在实际应用中常见的情况），如

姓名科目成绩
张三语文 20
张三数学 30
张三英语 50
李四语文 70
李四数学 60
李四英语 90
王五数学 100

如果使用给出的解答方式，则会把王五的数学成绩漏掉。即结果仍然为
姓名语文数学英语
张三 20 30 50
李四 70 60 90

因此，之前应该用交叉连接生成一个所有同学所有成绩的笛卡儿积，这样，才不至于将王五同学的数学成绩漏掉。
SELECT s1a.姓名, s1b.科目 FROM
(
SELECT 姓名 FROM student GROUP BY 姓名
) AS s1a
CROSS JOIN
(
SELECT 科目 FROM student GROUP BY 科目
) AS s1b

此时将生成这样一个表
姓名科目
李四数学
李四英语
李四语文
王五数学
王五英语
王五语文
张三数学
张三英语
张三语文

然后使用外连接将成绩填入：

SELECT s2a.姓名, s2a.科目, s2b.成绩 FROM
(
   SELECT s1a.姓名, s1b.科目 FROM
   (
      SELECT 姓名 FROM student GROUP BY 姓名
   ) AS s1a
   CROSS JOIN
   (
      SELECT 科目 FROM student GROUP BY 科目
   ) AS s1b
) AS s2a
LEFT OUTER JOIN student AS s2b
ON (s2a.姓名 = s2b.姓名 AND s2a.科目=s2b.科目)

此时生成如下一个表：

姓名科目成绩
李四数学 60
王五数学 100
张三数学 30
李四英语 90
王五英语 <NULL>
张三英语 50
李四语文 70
王五语文 <NULL>
张三语文 20

然后再套用上面给出的思路即可，即：

SELECT s3a.姓名, s3a.成绩 AS 语文, s3b.成绩 AS 数学, s3c.成绩 AS 英语 FROM
(
    SELECT s2a.姓名, s2a.科目, s2b.成绩 FROM
    (
       SELECT s1a.姓名, s1b.科目 FROM
       (
          SELECT 姓名 FROM student GROUP BY 姓名
       ) AS s1a
       CROSS JOIN
       (
          SELECT 科目 FROM student GROUP BY 科目
       ) AS s1b
    ) AS s2a
    LEFT OUTER JOIN student AS s2b
    ON (s2a.姓名 = s2b.姓名 AND s2a.科目=s2b.科目)
) AS s3a,
(
    SELECT s2a.姓名, s2a.科目, s2b.成绩 FROM
    (
       SELECT s1a.姓名, s1b.科目 FROM
       (
          SELECT 姓名 FROM student GROUP BY 姓名
       ) AS s1a
       CROSS JOIN
       (
          SELECT 科目 FROM student GROUP BY 科目
       ) AS s1b
    ) AS s2a
    LEFT OUTER JOIN student AS s2b
    ON (s2a.姓名 = s2b.姓名 AND s2a.科目=s2b.科目)
) AS s3b,
(
    SELECT s2a.姓名, s2a.科目, s2b.成绩 FROM
    (
       SELECT s1a.姓名, s1b.科目 FROM
       (
          SELECT 姓名 FROM student GROUP BY 姓名
       ) AS s1a
       CROSS JOIN
       (
          SELECT 科目 FROM student GROUP BY 科目
       ) AS s1b
    ) AS s2a
    LEFT OUTER JOIN student AS s2b
    ON (s2a.姓名 = s2b.姓名 AND s2a.科目=s2b.科目)
) AS s3c
WHERE s3a.姓名=s3b.姓名
AND s3b.姓名=s3c.姓名
AND s3a.科目='语文'
AND s3b.科目='数学'
AND s3c.科目='英语'

生成结果如下：
姓名语文数学英语
李四 70 60 90
王五 <NULL> 100 <NULL>
张三 20 30 50

另外一个题目的网址是：http://www.zhanso.com/html/200691/10907.htm
内容如下：

一个简单的表TABLE 有100条以上的信息，其中包括：
产品颜色数量
产品1 红色 123
产品1 蓝色 126
产品2 蓝色 103
产品2 红色 NULL
产品2 红色 89
产品1 红色 203
。。。。。。。。。。。。
请用SQL语句完成以下问题：

1。按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：

2。按产品分类，将数据按下列方式进行统计显示
产品红色蓝色

第一小题的答案已经有好几个了，有兴趣的可以去看一下，不过觉得还不是很好

我考虑的答案如下：

SELECT t.产品, SUM(t.数量) AS 合计 FROM
(
SELECT 产品, 颜色, 数量=CASE WHEN 颜色='蓝色' THEN 数量*(-1) ELSE 数量 END FROM tTable
) AS t GROUP BY t.产品 HAVING SUM(t.数量)> 0

对表(tTable)
        产品            颜色            数量
产品1     红色      123
产品2     蓝色      103
产品1     蓝色      126
产品2     红色      NULL
产品2     红色      89
产品1     红色      203
产品3     红色      102
产品4     蓝色      102
的操作显示结果为：
产品            合计
产品1     200
产品3     102

第二小题的答案，用第一大题的思路就很好整理出来了，细节就不说了，结果如下：

SELECT a.产品, a.合计 AS 红色, b.合计 AS 蓝色 FROM
(
SELECT t3a.产品, t3a.颜色, t1a.合计 FROM
(
    SELECT t2a.产品, t2b.颜色 FROM
    (
      SELECT 产品 FROM tTable GROUP BY 产品
    ) AS t2a
    CROSS JOIN
    (
      SELECT 颜色 FROM tTable GROUP BY 颜色
    ) AS t2b
) AS t3a
LEFT OUTER JOIN
(
    SELECT 产品, 颜色, SUM(数量) AS 合计 FROM tTable GROUP BY 产品, 颜色
) AS t1a
ON (t3a.产品 = t1a.产品 and t3a.颜色 = t1a.颜色)
) AS a,
(
SELECT t3a.产品, t3a.颜色, t1a.合计 FROM
(
    SELECT t2a.产品, t2b.颜色 FROM
    (
      SELECT 产品 FROM tTable GROUP BY 产品
    ) AS t2a
    CROSS JOIN
    (
      SELECT 颜色 FROM tTable GROUP BY 颜色
    ) AS t2b
) AS t3a
LEFT OUTER JOIN
(
    SELECT 产品, 颜色, SUM(数量) AS 合计 FROM tTable GROUP BY 产品, 颜色
) AS t1a
ON (t3a.产品 = t1a.产品 AND t3a.颜色 = t1a.颜色)
) AS b
WHERE a.产品=b.产品
AND a.颜色 = '红色'
AND b.颜色='蓝色'

对表(tTable)
        产品            颜色            数量
产品1     红色      123
产品2     蓝色      103
产品1     蓝色      126
产品2     红色      NULL
产品2     红色      89
产品1     红色      203
产品3     红色      102
产品4     蓝色      102
的操作显示结果为：
产品            红色    蓝色
产品1     326 126
产品2     89 103
产品3     102 NULL
产品4     NULL 102