This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / SQL问题有来了, 前些天把问题的前一半贴出来(#3261474), 在坛子里SQL高手们的帮助下, 获得了我自己觉得比较漂亮的结果, 今天我自己把后一半解决了, 可是怎么看怎么难看, 觉得应该有改进的余地, 再请大侠们看看
-poohbear(毛毛熊);
2006-10-16
{1277}
(#3266538@0)
-
我忽然意识到, 不必用outer join, 应该用inner join.
-poohbear(毛毛熊);
2006-10-16
(#3266773@0)
-
是不是有些customer只有其中一年的数据？如果是，而且你要求这个顾客被输出，那么outer join没有错
-newkid(newkid);
2006-10-16
{741}
(#3266796@0)
-
对啊, 是这个问题, 我当初选用outer join的时候是这么想的, 后来光想着怎么美化, 头一晕竟把原则忘了. 多谢提醒. 您改的code, 好象是可以的. 虽然篇幅没有简化, 但在逻辑上省掉了join. 我明天拿到DB上试试.
-poohbear(毛毛熊);
2006-10-16
(#3266831@0)
-
如果建一个新的表建议你
select company, customer, year, ytd
into newtable
from
(
select company, customer, year
case @numMon
when 1 then m1
when 2 then m1 + m2
...
when 12 then m1 + m2 + .... + m12
end as ytd
from oldtable
where year between @cur_yr and @pre_yr
)
然后在新表上做你要做下一步. 另外不应该有SUM(cur_year_ytd)因为根据你的描述一个CUSTOMER只应该有一个cur_year_ytd和prev_year_ytd
-looi500(looi);
2006-10-16
{392}
(#3266956@0)
-
谢谢, 我的考虑是如果分两步走, 在第二步的时候要逐行search和update, 不划算.
-poohbear(毛毛熊);
2006-10-16
(#3266970@0)
-
似乎你没看懂我的pivoting方法。经过处理后，每个顾客会有两行记录（假如两年都有数据），所以SUM是必须的。
-newkid(newkid);
2006-10-17
(#3267354@0)
-
是的. 你改的code我今天试过了, 仅改了几个很小的syntax, 运行很好, 结果也验证过了. 比我原先的join整齐多了. 多谢! 我现在仍在考虑的是, 能不能把那一大篇求和写成个参数, 省得重复两遍, 狗了半天也没找到. 不知道是不是真没办法?
-poohbear(毛毛熊);
2006-10-17
(#3268484@0)
-
SQL Server 2000 +, 可用User Defined Function, 但performance 会有些影响
-sch2000(TonyBoy);
2006-10-17
(#3268693@0)
-
用函数的问题：如果把12个列全部作为参数传进去，也显得有点messy. 如果只传主键，在函数中重新读取数据，看起来也许整洁了，但效率又下降了。
-newkid(newkid);
2006-10-17
(#3268723@0)
-
看来只好这样了. 现在的code比我自己原先写的已经漂亮无数倍了. 再谢楼上的朋友们!
-poohbear(毛毛熊);
2006-10-17
(#3268767@0)
-
看看这个，是不是你想要的结果？ （已经经过测试）
-digitworm(digitworm);
2006-10-17
{1039}
(#3269009@0)
-
真的!! 我自己怎么就想不到哪! 谢谢! -- 到底省掉了一半重复code, 我现在想法是, 因为加和的时候没有having, where, case的限制, 把表里面所有的记录都处理了(比如2-5年前的), 是不是performance会差? 我明天试试.
-poohbear(毛毛熊);
2006-10-17
(#3269023@0)
-
感觉你的要求还是有一定难度的，如果允许使用临时表，临时表+Join就容易多了
-digitworm(digitworm);
2006-10-17
(#3269075@0)
-
我想起来了, 其实在你的code末尾加上个where就行了
-poohbear(毛毛熊);
2006-10-17
(#3269091@0)
-
在from t1后面加上 Where [year] <= @prev_year
-digitworm(digitworm);
2006-10-17
(#3269173@0)
-
yes! Thanks again!
-poohbear(毛毛熊);
2006-10-17
(#3269176@0)
-
至于临时表, 我倒是知道最新的SQL Server支持临时表参数, 但是总觉得这种新DD, 各种DB不通用. 现在学会了, 下个项目换了DB就不能用了, 不如用些通用的SQL范畴的DD或更通用的逻辑,算法之类的....
-poohbear(毛毛熊);
2006-10-17
(#3269112@0)
-
加几个列就可以计算2-5年前的。要提高性能，首先加上一个组合索引(company+customer_year), 然后在Select语句的最后加上"Order By company, customer", 最后使用Execution Plan评估性能。
-digitworm(digitworm);
2006-10-17
(#3269140@0)
-
对不起我没说清楚, 我是不想处理2-5年前的(这个表最老的数据是5年), 不过我已经想到了, 我可以在求和的那个select里面加上个where, 这样就算egg里挑bone也无可挑剔了. --------- 再谢!!!!
-poohbear(毛毛熊);
2006-10-17
(#3269162@0)
-
当然你可以按照你的要求随便修改, 本代码属于Open Source. ;-)
-digitworm(digitworm);
2006-10-17
(#3269188@0)
-
不错！这段代码可是千锤百炼啊。不过我认为组合索引并不能在这里起作用。如果YEAR有索引，在子查询的WHERE中加上两年的过滤条件。还有个提醒：当心 NULL + 非NULL 产生的后果。如果全是 NOT NULL 列就当我没说。(case when @numMon>=1 and m1 is not null then m1 else 0 end)
+(case when @numMon>=2 and m2 is not null then m2 else 0 end)
+(case when @numMon>=3 and m3 is not null then m3 else 0 end)
............
这样就把NULL转换为0了。
可能有语法错误，将就着看吧。
-newkid(newkid);
2006-10-18
{257}
(#3269437@0)
-
You are right. I noticed this NULL issue before posted. To resolve it, use IsNull function for SQL Server.Just change "sum(Case [year] When @curr_year Then ytd End)" to " IsNull(sum(Case [year] When @curr_year Then ytd End), 0) " for every year.
-digitworm(digitworm);
2006-10-18
{145}
(#3269502@0)
-
SUM对NULL反而不敏感了，我说的是 m1+m2+...+m12 这些地方。如果是 ORACLE, 其中任何一个 NULL 都会使整个和变成 NULL
-newkid(newkid);
2006-10-18
(#3269515@0)
-
sorry, I misundstood your question.
-digitworm(digitworm);
2006-10-18
(#3269737@0)
-
不错, 是千锤百炼. NULL的问题, 我查了原表, 没有NULL的值, 都是0.00, 可能是设了缺省值. 如果有NULL的话, SQL Server确实会出错(我记得Oracle是按0算的), 但可以用coalesce解决. digitworm的语法不象SQL Server的,在SQL SERVER上要稍作修改, 目前还有一个影响美观的小问题是, 我想去掉两年都是0的客户, 可是SQL Server非强迫写where sum(Case [year] When @curr_year Then ytd End) > 0 or sum(Case [year] When @curr_year Then ytd End) > 0, 真是刹风景.
-poohbear(毛毛熊);
2006-10-18
{226}
(#3270581@0)
-
这就叫集体智慧的结晶。跟高手在一起讨论问题的同时自己也受益匪浅。受newkid Pivoting（行转列）想法的影响，加上原来Case表达式，最后用子查询解决长Case语句重复的问题，才得有此方案。
BTW，我是在SQL Server 2005上调试的，其它环境可能需要一些修改也很正常。
关于Where语句，好像没有shortcut。
-digitworm(digitworm);
2006-10-18
{222}
(#3271007@0)
-
原来是你的SQL Server太新, 我们的是2003. 还好, 在MS的产品里算是向下兼容不错的了. Where语句只好由它去了, 怪怪的, Oracle好象是可以用别名取代的. 还有, group by 一定要写ytd.company, ytd.customer...
-poohbear(毛毛熊);
2006-10-18
(#3271063@0)
-
我写代码的时候是加上ytd别名前缀的，调试的时候发现去掉也行，当时也觉得有点奇怪，没想到是是2005的新特性. A surprise :-o
-digitworm(digitworm);
2006-10-18
(#3271099@0)
-
如果TABLE很大, 可以只SEARCH一次TABLE且只计算需要的RECORDS, 但有重复CODING.
-looi500(looi);
2006-10-19
{1732}
(#3271636@0)
-
这就是问题所在. 如果有可能避免最好不要用这种聪明也奇怪的CODING. 不然的话没有人会喜欢你的CODES.
-looi500(looi);
2006-10-18
{552}
(#3269680@0)
-
如果我没猜错，原始数据应该是NOMALIZED的，这张表已经是存放计算结果了。这种设计有它的考虑：我们经常被要求输出年度报表，每个月一栏，那么这时候就显得有优势了。不管怎么设计，总会有输出的旋转问题（纵->横，或横->纵）因此总是会用到一些技巧让程序不好读。
用UNION把一行分割成12行的办法，数据量一大就很不可取了，因为会多次扫描同一行数据。
-newkid(newkid);
2006-10-18
{119}
(#3269789@0)
-
根据POOLBEAR说的, 原始数据应该是已经在一个SQL SERVER的TABLE里了. 根据报表的格式设计数据库TABLE本身就不可取.只是想提醒POOLBEAR一下不能只在CODING上下工夫, APPLICATION, PROGRAMMING 和DATABASE DESIGN很重要. CODING要清晰明了, 这样别人也会喜欢. PROGRAM 做完了, 不是你个人的而是属于整个SUPPORT TEAM的.
除非是ONLINE SQL QUERY, 不然的话不要只想一个QUERY就做出一个REPORT.
-looi500(looi);
2006-10-18
{256}
(#3270415@0)
-
你说的不可取应该是针对OLTP的设计而言。在数据仓库或决策支持系统，针对输出格式设计是很常见的。目的是为了事先存放计算结果改善报表响应时间。以我的猜测小熊的这张表是从更原始的数据(比如客户订单表)统计来的,这样的设计自有它的道理。
-newkid(newkid);
2006-10-18
{87}
(#3270516@0)
-
这个表确实是为输出设计的, 但是做这个表原因绝不是为输出方便, 而是为了performance, 因为source在另一个DB中, 而输出是web form, 况且原始表中的数据是月更新的. -- 方案不是我设计的, 但是我个人认为非常非常好.
-poohbear(毛毛熊);
2006-10-18
(#3270690@0)
-
我4个sp出48个report. 都是跟几个表相关的, from 字句基本相同,where语句不太一样,select不太一样, 24个report 是detailed的,24个是summary的
-hard20(hard20);
2006-10-18
(#3271154@0)
-
有些分析师就喜欢直接看DB的表, 反正现在的DB GUI都不错, 这些人再会些简单的SQL, 会往EXCEL COPY/PASTE, 才不要看什么REPORT. 我们的VIEW啊, 次级表啊多是给这些人做的... 我做的这个表这也是个原因...
-poohbear(毛毛熊);
2006-10-19
(#3271203@0)
-
多谢楼上几位. 表不是我设计的, 我不能改. newkid猜得不错, 我的source其实是别人产生的一个view, 不过原始表更不符合DB设计的一般规律 --- 没办法, 这是现实, business提出一个要求, IT就得限时赶出来, 质量远不如时间重要, 特别是看不到的质量. 等business试用好了, 就会急着进生产. 一旦进了生产, 就没有人愿意动它了 -- 万一把原先挺"好"的东西弄出了问题, 造成损失, 谁敢负责?
至于程序方面, 我其实更喜欢在程序里面操纵数据, 毕竟任何一种语言都比SQL功能强. 其实这个DD我已经用程序写过一遍了, 但是现在这一个必须用SQL...
-poohbear(毛毛熊);
2006-10-18
{282}
(#3270556@0)