看了上面，我也来说说俺当年没答出的面试题。 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

看了上面，我也来说说俺当年没答出的面试题。

看了上面，我也来说说俺当年没答出的面试题。# Database - 数据库

B*g2008-10-16 07:10

1 楼

和sql３差不多, 除了month还有year，sql没答出来，用plsql作的，结果工资少了
一万.
(***应该可以假设（year，month，product＿id） unique***, 不过最好别这样假设)
YEAR, MONTH, PRODUCT_ID, PRODUCT_QTY
2007, 1, 1, 2
2007, 1, 2, 3
2007, 2, 1, 2
2007, 2, 2, 3
2007, 3, 1, 2
2007, 3, 2, 3
......
Need:
YEAR, PRODUCT_ID, QUATER1_QTY,QUATER2_QTY,QUATER3_QTY, QUATER4_QTY
......
NND, 第一次竟然发错了, 现在应该改好了

b*e2008-10-16 07:10

2 楼

SELECT [YEAR], PRODUCT_ID,
SUM(
Case
WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY
ELSE 0
END
) as QUATER1_QTY,
SUM(
Case
WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY
ELSE 0
END
) as QUATER1_QTY,
SUM(
Case
WHEN MONTH IN (4,5,6) THEN PRODUCT_QTY
ELSE 0
END
) as QUATER2_QTY,
SUM(
Case
WHEN MONTH IN (7,8,9) THEN PRODUCT_QTY
ELSE 0

B*g2008-10-16 07:10

3 楼

zan. 想想俺当年太面了。
再延伸一步，每个P_ID，每年都要有显示。当年没有，要求显示year, id,０,０,０,０。

【在 b*****e 的大作中提到】

: SELECT [YEAR], PRODUCT_ID,
: SUM(
: Case
: WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY
: ELSE 0
: END
: ) as QUATER1_QTY,
: SUM(
: Case
: WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY

b*e2008-10-16 07:10

4 楼

In SQL server, we can use cross join.
SELECT b.[YEAR], b.PRODUCT_ID,
SUM(
Case
WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY
ELSE 0
END
) as QUATER1_QTY,
...
SUM(
Case
WHEN MONTH IN (10,11,12) THEN PRODUCT_QTY
ELSE 0
END
) as QUATER4_QTY
FROM Table a
RIGHT JOIN (
SELECT DISTINCT t2.[Year],t1.PRODUCT_ID FROM Table t1
Cross JOIN (SELECT DISTINCT [Year] FROM table) t2
) b
ON a.[Year]=b.[Year]
a.

B*g2008-10-16 07:10

5 楼

以后你就在这儿站岗答疑吧。

【在 b*****e 的大作中提到】

: In SQL server, we can use cross join.
: SELECT b.[YEAR], b.PRODUCT_ID,
: SUM(
: Case
: WHEN MONTH IN (1,2,3) THEN PRODUCT_QTY
: ELSE 0
: END
: ) as QUATER1_QTY,
: ...
: SUM(

b*e2008-10-16 07:10

6 楼

Ban Men Nong Fu.
I just want to learn something from this board, and share what I know.