This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / Require write one SQL only,how?Question
You have table
Product Salesdate Sales Amount
Toothpaste 1/12/2014 $100,000
Tooth Brush 1/14/2014 $8,0000
etc
You have to create report
Product Jun Sales Feb Sales ... Dec Sales
Toothpaste $15,000 $12,000 ....
Toothbrush $10,000 etc
-outlink(outlink);
2014-8-25
{412}
(#8948334@0)
-
Such a mind-twister! I can't do it. :)
-verano(Summer);
2014-8-25
(#8948363@0)
-
Try belowselect product,SUM(case when month(salesdate)=1 then amount else 0 end) as 'Jan sales',
SUM(case when month(salesdate)=2 then amount else 0 end) as 'Feb sales',
......
from table1
group by product
-budwiser(budwiser);
2014-8-25
{206}
(#8948503@0)
-
遗憾，not too many Rolians can appreciate this clean, simple, and standard complaint solution.
-mingyangli(Life is a journey);
2014-9-3
(#8963355@0)
-
谢谢
-budwiser(budwiser);
2014-9-3
(#8963426@0)
-
You are right. I missed it. It is simpler and better.
-ultrasonic(ultrasonic);
2014-9-4
(#8966526@0)
-
pivot, completed code is here
-xbhydx(IT 南郭);
2014-8-25
{4080}
(#8948510@0)
-
不让用pivot
-outlink(outlink);
2014-8-26
(#8950751@0)
-
这种？
-ultrasonic(ultrasonic);
2014-8-26
{4503}
(#8950794@0)
-
不让用pivot？大概是出题目的人不懂吧。实际开发中，只要能解决问题、不影响performance，为什么不让用？
-xbhydx(IT 南郭);
2014-8-27
(#8951543@0)
-
+1 with the pivot solution
-kakaka(小胖子);
2014-8-27
(#8951563@0)
-
用Pivot 短点好看。只pivot 一次的话对performance没影响吧
-ultrasonic(ultrasonic);
2014-8-27
(#8952062@0)
-
是个面试题。 哈哈。。。
-outlink(outlink);
2014-8-28
(#8956164@0)
-
Reminder: Please do not comment on posts regarding mistakes or errors, unless you are invited. 8.13 13:32 (#8928347@0)
-geekcode(文心雕码);
2014-8-28
(#8956320@0)
-
Pivot
-ultrasonic(ultrasonic);
2014-8-25
{611}
(#8948637@0)
-
Learning R. This can be easily done using reshape.
-renjl0810(Virtual Void);
2014-8-27
(#8953557@0)
-
My solution without using pivot
-deep_blue(BLUE);
2014-8-29
{626}
(#8956727@0)
-
cool
-outlink(outlink);
2014-8-31
(#8959588@0)
-
No pivot but temp table is allowed?
-ultrasonic(ultrasonic);
2014-8-31
(#8959723@0)
-
Using table variable makes code simpler and more readable. It’s no problem to use sub-query instead.
-deep_blue(BLUE);
2014-9-2
(#8961531@0)
-
That is not what I asked. I found that it is odd that it doesn't allow pivot but temp table is ok. Use of temp table makes the question pointless to me.
-ultrasonic(ultrasonic);
2014-9-2
(#8961992@0)