B*****g
发帖数: 34098 | 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*****e
发帖数: 364 | 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*****g
发帖数: 34098 | 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*****e
发帖数: 364 | 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*****g
发帖数: 34098 | 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*****e
发帖数: 364 | 6 Ban Men Nong Fu.
I just want to learn something from this board, and share what I know. | g*****a
发帖数: 47 | | c*****d
发帖数: 6045 | 8 其实就是先加条件,后sum
【在 g*****a 的大作中提到】
: sum 可以加条件啊, 学了一招
| B*****g
发帖数: 34098 | 9 顶。
【在 c*****d 的大作中提到】
: 其实就是先加条件,后sum
|
|
