TERADATA SQL学习随笔<一>

此博客内容简介及目录

http://www.cnblogs.com/weibaar/p/6644261.html

最近在TERADATA环境学习SQL。在这里记录一下学习中查过的知识点，作为备案。

目录：

关于SQL学习及所用在线数据库
表联合（join）
SQL子查询
在select时创建新字段 (as, case when)
数据分组（group by + 聚合函数count, sum, avg等）
利用over (partition by)进行数据分组并创建新字段
样本选择

1、关于SQL学习及所用在线数据库

之前有看过一些SQL学习的书。但如果从学习效率来说，跟着书学习SQL，不如直接看生产环境的工作代码，遇到语句不懂时搜索引擎查找相应语句，效率会更高（例如本文就是此类的集中体现）。

当然，除了零星的知识点以外，网上还存在一些在线练习SQL取数的网站，在这里列举如下：

1）基础SQL测试： http://www.w3school.com.cn/quiz/quiz.asp?quiz=sql

　　　　　　　　 http://sqlzoo.net/

2）基本语法练习-CodeCamdy： https://www.codecademy.com/zh/learn/learn-sql

3）在线直连数据库练习 http://www.sqlcourse.com

　　　　　　　　　　　https://livesql.oracle.com/apex/livesql/file/index.html

　　　　　　　　　　　http://www.sql-ex.ru/

当然，我们也可以去下一些基础练习数据库，安装mySql等进行练习。不过一般入门，学语法比学怎么装数据库更重要。见仁见智吧。

另外，相比于简单易懂的SQL语法学习，如何优化SQL语句效率，如何理解数据库结构等等更加重要。这也是下一步我学习的重点。具体参考这个链接 https://www.zhihu.com/question/20116482

那么接下来，我选用oracle数据库来练习最近查阅的知识点。

在这个网站注册　https://livesql.oracle.com/apex/livesql/file/index.html 后，在code library选择【EMP and DEPT 】数据源导入，会在我们自己账户里创建以下表： dept, emp。以下代码均以此为实例改写。下图为两个表概览

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAARMAAACbCAIAAADOX+juAAAP+klEQVR4nO2dzWscRxrG86fVYTBECBaBsmA0ElmBwl7kQ4igIwwCswh8MHPwMCDwwaxNQGxgEkEuDiSLsQghE1Y4p+xBJx88tzouungP/VXf3fOq+53W4/d3iqe7Pp6q5+muqjbOJ/8TBGF1Pll3BwThThJOzntBEN6/f//+PSU5GgIYIcwgeYCGJAdECDNIHqAhyQERwgySB2hIckCEMIPkARqSHBAhzCB5gIYkB0QIM0geoCHJARHCDJIHaEhyQIQwg+QBGpIcECHMIHmAhiQHRAgzSB6gIckBEcIMkgdoSHJAhDCD5AEakhwQIcwgeYBGh8m5nOyomnub97949OznP8ur50fK4+g8Uu6b35axMkoptTO5LK+NHry4NlvYmVxqrfXyjx8mDz77dJTX+PnDl7+8i+oP/ZzSkm5Za61fnW7lBbdOX5W/vT07UEopdfj8Wmutl+dHI1WXio/OQEkkJ9diDIdBcl6Wf/ww++rzzXtKKTX69LMHkx/+WPbT+y7oLTkFm0fnueHaJidndHD2e6vkGFNU+/fP86NNt8K9yevQNLRKjq0l1XJdeGtrS5nRKWscff2t1lpfnGwopZS6/+R1enQGCik5qXlZvn76t3uBeR4s3Scnn/B3//l+8vdNpZTafvyT1qnxdMv9Y8ewVE6gcO21jYff1W+oncnl8ruHG0qp0d6TH6+Xenn945O9kbLeEbb+0LCktMRbNspunPzz7MD7dWtrKw/T5WRHbW9vV02kntODhJCc5Lz8PttXSqnRX7589uZ6qfXy+s2/Hj96NuDx6Cs5Wmt9/fywzkDL5FR/Lp7NObHk3N/f36gyUfr3269HdWC11uXqyarR0B8alpSWeMta63JZ9uU3y7yOg7O3WmutXz+5r9TR6emW2nv669uzA7U/mx19VMlJzctPj7eVUmr79NWA12c2fSbH+iWwHikG17xreb14mb/RS8dprePJ2Zm8eLxtvQt2JudeN1LGbJsc45doy5dalxE7OHtbGqMQcp7H5OJkQx0+e/al2plcnrvJCY3OQFk9Of6Q1re+yp9LxrZw+AwnORabJxfmwyeenMvfzw5G+bqJOTl+y5da6+sXD0blfxenAPmZQBGT6+eHamN7e2Pr9FX+097TX5OjM1C6TU4xZ8OW7MCQnHyV1Lhayxl9+tkXj7yTsERyiuXz6MGL503JyU3q6Q8NS0pLtOXLMizlszN//4yOzpdVcorF3MbJRfFOykV9BKu1eHL2ns6f7sk7xxiaYteXL1fa73MCpJKji0Xy/v6+MvY55izky6aNk4ug/lCDKS3xlusjM4vR0fny7dlBccur060iTJeTnY8oOTo1L8W4mZugodNTcpbXi+9P86OTw+d/at1rckpbF/fkCya1cfDUOsMpD8I8/aEGU1riLdcfcmw2Ti6MlNiN5E76GJKTmpfi3E2N/vr1yzfXS63f/fffLx9+NRvwePT8Paf6nNP8PecWyakGvlhFvZ7sjaId8fSHfk5pibecv4Kss438Ybp1+jyWnOgu8I5/zwloSc5L4FvPsJ8kfSXn3uZ9e7/Sa3LKdVP1y7ufn5Vfo+9tfv7VLPo1uk1yHC3RlvMdlvUhqjzN3j48vP/RJ0c3zMu7X14++qLNX/wYBB0m504CI4QZJA/QkOSACGEGyQM0JDkgQphB8gANSQ6IEGaQPEBDkgMihBkkD9CQ5IAIYQbJAzQkOSBCmEHyAA1JDogQZpA8QEOSAyKEGSQP0JDkgAhhBskDNCQ5IEKYQfIADUkOiBBmkDxAg54cQRAkOYJAgZKcGwhghDCD5AEakhwQIcwgeYCGJAdECDNIHqAhyQERwgySB2hIckCEMIPkARqSHBAhzCB5gIYkB0QIM0geoCHJARHCDJIHaEhyQIQwg+QBGpIcECHMIHmAhiQHRAgzSB6gIckBEcIMkgdo9JGcxXSslFLZ3L0wz6p/LnU8XfQpqzVN01902ddSqlSh64ZQW6lZqL6wmI6dAVlMx3ml/liWN1vtK7e9wNWihbxvZnPzzBZglY1MVNQDyeoDvapEekPsj4p/3S82z4zmw8Ntz499yelKXkGwFx0nZzEdKzWeTrOwmyxFgwhPQ3LmWaHGFuNN2jyr1FhTl99b3GiOgDUETckZj8f2ZPo3xwbTTUXRi/HYKGDek1JmkkxOtPpwREK9LG4O3OvcEQi98YgIDrfTnp81azBiI9tpcuZZ0c7cS44lw+3hGkknpxhhZ2JTA+orN6/YhapfmpKTTadjywO3Ts50mtnWyf/QflbSyYlUn0yOpyAYp0BBo6RRT2K43arNXhn/3TAY/exzPP+4kxtd0HGTFFINsDXSqRENWiB6pay2MTnzRR2dbpKzmNfetmS2XAk0JCdYfXzoggraJMeymll7arhbJafxIcKUHO/1mM1XmKYeSQkxOm32P9Hx+HAHr5Q1NSfn5qayYkfJuanDaAa47cOsKTmB6mOjYCkwtFQrmDTVCsDbDsSGO7U+LQq2MCdPcmoh9cgMPjne8CYeZzf+bS2umJv9puRU0ekqObW3y3vMWTP21oR9TrB6u1b/PKN67cWWsDHKvaBz5hIdbveEIHg60vgEYX3nBN48LQamV+JC7IAYU7Ged071DO4sOdUWPPHOiVbdnByv+tT41OEdT6e1zrb+yJMQfFQHu2av2wJna4N55wRO9wZyRBAV4hxcGg+izvY5Vjyak1M8xbtLTvkeS+xzbpMcr/rm5FQFCp2t/eHJTA23v/gObGLjRz0FTMm5c2drYRMZ0bn92ZpToTdc/qXFdDyezjtMTm5V+wns9ZGeHLf6+Kzn7ZQncvPwt4AEDTLdtv0Tn8AGqeHTCVdyAv1b/ybnJpqckGMMUd7qoP7q4b1dF+X3HFu0bSJ7niPnRXk7WdZhcm4W03GWuRsRe5F6i+QEq48mZzw2nR3/CttKZuNwO2drofG2Dw8cuk1OaInj7BUCu7J1EhYSnmH7eWBpDTwlQhts64JTv1lffKub39WcHH8vHvnQ4X8fcIqG7dsyOU71gV5ZWbE+Vd4yOW5z0aPeG3Nmw+Mdcms/75y7A4wQZpA8QEOSAyKEGSQP0JDkgAhhBskDNCQ5IEKYQfIADUkOiBBmkDxAQ5IDIoQZJA/QkOSACGEGyQM0JDkgQphB8gANSQ6IEGaQPEBDkgMihBkkD9CQ5IAIYQbJAzQkOSBCmEHyAA1JDogQZpA8QIOeHEEQKMn5AAGMEGaQPEBDkgMihBkkD9CQ5IAIYQbJAzQkOSBCmEHyAA1JDogQZpA8QEOSAyKEGSQP0JDkgAhhBskDNCQ5IEKYQfIADUkOiBBmkDxAQ5IDIoQZJA/QkOSACGEGyQM0JDkgQphB8gANSQ6IEGaQPECj6+RczXbrf8r3+MK+eHFcXdqdXfUvrgXx6Tc663bYEulLNUuaxZwa/VEobrCHLdEPvzNl0YvjUDUdDnrUA65I1wMfIjI/XM12ox305XhFg56KzUQXdJscU+HVbNfqrjl17rX1kU5ONVsXx+bYX812YzPpKrua7VqVpERfHKvd2cw1SaIfhWWM2y+O84vrTU7dstfBDzGZt0jO1WxXHc/c4qmZ6II+V2vmZLkTlzAfKy2TY/c33vmkrAbzFg26Lon3I/H8GUpyih/scAdl0pNTFHTK926wvpNTdN4dluL9uv7o9JGc1PzHzVu159gk2o+UNwaUHCc6MZnk5FTl7Ap6X9b0mByz796S4/ii46kk0i45zqKjybOJ9X1UsdGc7ZNoP1LVDSk5loejMqnJMYo5NSRmogt6S441ybXVanV3ITmRja5/QmALMYrGTg7cq14+7K1NqL6m5PisPzlxmcTkWKX8EYnMRBf0lJy8x64xAm+ebtWsTpt3jrMtb72Gdo98Ela3L1n1R/txp945tYKITFpynJmIiYwevt2CPpKTx8Y/6BjiEUG71Zqzy12h72bJuHmDr4jAosbqx13a59TLjJhMUnICnweSD6cuDdd5cvyMVP0eYHDanhDY+82uk+NfcJ7T4X7cibM1o5NJmZTkBOah4ek03OR4C5uKtnPOzEpna/62zS+U+KaQWku4v9fznPpA4i6Kh/Y9x3qMpmVSkhP4uRrx5Ex0QafJCb6N3e92/ezXqLRNjjfJkTWHey19QlCdpvrjUTWX6Idfq785Mm9bw98haFri1mpSgxoeuaCkOiKJmeiCPr/n3AVghDCD5AEakhwQIcwgeYCGJAdECDNIHqAhyQERwgySB2hIckCEMIPkARqSHBAhzCB5gIYkB0QIM0geoCHJARHCDJIHaEhyQIQwg+QBGpIcECHMIHmAhiQHRAgzSB6gIckBEcIMkgdo0JMjCIIkRxAoUJKz7v8ffTfACGEGyQM0JDkgQphB8gANSQ6IEGaQPEBDkgMihBkkD9CQ5IAIYQbJAzQkOSBCmEHyAA1JDogQZpA8QEOSAyKEGSQP0JDkgAhhBskDNCQ5IEKYQfIADUkOiBBmkDxAQ5IDIoQZJA/Q6DY5i+nY+PdIx9OFfXmexa+tieT0W3KyuXO1UOP+Ps8C95oFIiOUbmxoNCXH1FqqXEzHzrwvpuNSqz84ziCEajSrCXvKqtaq0LxCGe7+3jmL6dgSM8/sMRxGeOJC5pk9pPPM7vA8U+Pp1I9JOjkR0U2NDY6UB+aZbePFdJzNbxqSY/ziS4/VWJfJpn7BeCm7XXfw29Hnas10iusaf8zWQ3L6U+4tAuLnhJKcpsaGR9wDcR9Sk9Pg7KKIWzI+puFWV3Vj38kxQh5Ymqw/OukHZ7x/lTYvKNTkDGAwViDqgcRDgJichsdKVcIqmshC6FL4VZekv+RYgi075X8YxnM2LqTcdwT7aOhxk0JarSUbGyIxD6Se3rTkNLwPjAJm2YaVsXuJ8NLpPDnGRrfuXt2xWt3Qk3NzExFzY6fDHfRVTgj8JUNwBzxAqMkJcKvkWPfXlrI20sa4Z/OI8eKzFqPH1ZrZ+/o147x5VulrH7Q7WnWO0eyxd6aW9M5JNDZMBvLOCYx99LlcTsxQ3znh/uSPmwEeEbT+KGE8BwIHqK2eCO3fssM5e4wxjH1O8C1WbXoiRzch5xEWQL0nx3jpDDA4lOT4o2yJ+ciTw3m2FolHJDrVxIQvrTrcPSbHFmyZYTjOiAmxvxiYYprmlpCceGODJeEBb31x6+85sRoDQ+0tDYLLaHuAaTvuTpPjvDzduTcvD8UXiUeAvSizQh/++wSxj+GBnapzJdbYcGl4elpO6OTvEARqDDrefig7tToHObca7l5Xa3cAGCHMIHmAhiQHRAgzSB6gIckBEcIMkgdoSHJAhDCD5AEakhwQIcwgeYCGJAdECDNIHqAhyQERwgySB2hIckCEMIPkARqSHBAhzCB5gIYkB0QIM0geoCHJARHCDJIHaEhyQIQwg+QBGpIcECHMIHmABj05giBEk6MFQVidTz4IgrA6/wdCLXCZYqg1iwAAAABJRU5ErkJggg==" alt="" />aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAh0AAADhCAIAAADjzZD7AAAgAElEQVR4nO1834tcR5bm/Gn3oRC40GLEVM+irSzhESszyyCxGBekc8QIjBHjGUQ9KMlBi1g0Y2Oobc9kO8HMjHvpbUyLxnQZF/aT+6Ge9LD1lo9NvWge7q/4cc65cSMjMr8MzvdkZeSN+50TX5wvfmT5z/70pz/9f4VCoVAoNsCfDPxZ5yvr/cF+sZVRUizJgalMQEo4wEyOssoN9RUslBRLcmAqE5ASDjCTo6xyQ30FCyXFkhyYygSkhAPM5Cir3FBfwUJJsSQHpjIBKeEAMznKKjfUV7BQUizJgalMQEo4wEyOssoN9RUslBRLcmAqE5ASDjCTo6xyQ30FCyXFkhyYygSkhAPM5Cir3FBfwUJJsSQHpjIBKeEAMznKKjfUV7BQUizJgalMQEo4wEyOssoN9RUslBRLcmAqE5ASDjCTo6xyQ30FCyXFkhyYygSkhAPM5Cir3FBfwUJJsSQHpjIBKeEAMznKKjfUV7BQUizJgalMQEo4wEyOssqNUb5yflp5OD1fr9erJ4dVVVV3nn6zXq/X69dnx31b+48at27fff/jL/5wbX7t6NPfNC+oPzk9X6/X6/X1T1+fPfrFOwf1Y+89/vz3b9gY6Ab71TWOz173kRw8+uzKjK1uXK/X66tXD5sHHr5qv7L+8cUD86Pr89ODvkv+ZWMwIhaj8ze///zxe7dvVVVVHbzzi0dnX/90TT106/bd9//uX5q2PQSnTEl+6/X6+qf/+/Lxe+/WSvJFUOPgnXff+3Dxbz9HsaI+bli0JKwPKK2QMjKni8u4wek5GcrZv/7BGudvnt6pW9ssee8S+zQbEiRnvV6vr//wxcfv361Ve+v2ex8uvjaVSU7AjtX4iRXKar1+8/vPP36fqjvcLBPkN6DMEFby0PKDxKubeqZJaqqSnMhX2oZ7z79b9+W3+uCLa1q8Bw9efG+0HJ6eX7lB/Hx+ett96t7Zt1RNjPcVQ6C2r9SWcefOncrUddvfwUdfrtedaKq7z74VXzYGEb5y/e3ZvQOn7fbp+c8MJ0Mz+wZOmZL8rr99/pe3BkRgSOzBi+8jWFEfJ/GVZjSfrK6tQE2wHmBNl9dnx62ee2PZpa98/+KBK1qzd3oCrrP7ys/nH73r8jo9X4uzTJCfpMxAVvLQBvpKS/jJ6jrIVzYtyTG+Qozp+WmjgYevrtbX56cHB0dHd5pvWvPrzQ9ffXJcVXUtNmR9f/G9FcT1rx4fVlV1cO/Zr6+u19dXv35276Cy9heD47EmpjYxWIePf3XdfdKV6vPTg6p68OKfnxxW1cHp+bXR3507d+qp+frsuDo6OqrsfdnomRcYix9XQ/b7xf2qqqrbp5//8Ga9fvPD5/XgH336G4fT9U+/nN1JQHF3kHyFkV9Tvg7+4qPPf3d17T/WprHLW10BRrKiPpZ8hWJAfKvXfb2QkSdg23B99bvPP/oLe7q8PjuuqsMn//ziAfk8qd2sFfy75/eqqjp4MK9H5c0ff/vLT//6ky/bZmYC5mbVlJ3q1l9++tUPb9br9Zsf/uPlR//wpTzLBPlJhTGQlTy0fDqc5/7Xw8OqchzNfzhVSU7jK9dffFBVx0+fPqwOPvpyvXpyePh48eyY8pWO6MFHXzb/df/+/XaA+iC+/OigslfW9T6+nWBuDBTbQV+5e//+YZcYy1dWTw6r6u6zb2t5Hz5Z1U99++xuVZ0+fXqnuvf8ux9fPKjuLxanO/aVZoIao9ucINx7/p3r6f/0wWEvmD0Eo0xBfk0C6JAdOcdWrHy+4v0zyFfW63V37NWUkXqh/MEX13V3D178OMBVfNkYcHpu3nj8ydfksSwzATOzuvrs0UFVVQcPX7nHodIsE+QnFsZAVvLQhvlK/29r2cT5yuYleeNzsN46js9WLx5Ud56+fHb34PT82zPKV66vLhqjf/Dix5byq88eHTRj1gZB6ZzP4KhzMGOTeHz22adHlbHoaHv/5umdRsy1elpdn9cmsnpyWD18+fKD6vjs9bnrK8TLxmC0r4ilyef0X/7nP/2Bmsh7AUaZvPy+/eKDVmz2ABkHPcdnr9fr66vfvXx0u+q3r+NYUR9zh0wjfOXND189vXdQ9b7ITUCiI/OTWsUPXvzY1gLXWARfIV6WIDnr9evnzbHSwTu/eP/jl//xg3lSz01AOmXjwbBqKrZ9ocO9tcuZUP3EwhjISh5afpCs3fgf/73eXZgOzfvK5iU5qa+8/ubpnero6Kh6+Orq9dlxY2X8gXFL+Xr15HY9ebbtK6/rY5LDx7+6NnzlN58edZvvuovmSLoxkatXD6vDo6PDO0+/qT+69/y7ffGVqrr9Vy83mpM7hOwrhPyemzpifMXArT9/fD7+4j6Lr/Q4eOe/P2/PsON8pV6GNyci9W9NnNK5A19Zr3/+t7NHf95ffR28+8GrZsnDTkA6ZeMh1zf23Fz0Far6iYUxkNVmvmJLybk9ZH1l45Kc5hysTV9zk/3gxY/NOsO7ozx45xfvf9z+iqC/EqpPL48+/WwwCOr4O/Yc7PjsdXNqePDos1edr9RPtTOvXsMYg3h63hwrHD5ZNc3keV8c0vlKf9LY3q+0h6zWefU+YcBXfPm9qC9KzSpa54vylcMHC/oOcpgV9fFm52Adjh7/6mfp+1xDP12aK/CmMteZcSSw/XOwFtdXv/vl2Yf/7ZZZEvkJmJlVfdQzar9imAdR/cTCGMhKGtrhc7AG/u/t6K5TleQ0vrJ6cli/8erVQ7cGC/XW+KlBvaQ6vH//bmUc5hk/iWzkZe/j+hgotkG+0iyOmiPF47PX/Y82LHQtx2evazL11OyUs01fMadaffJrzoXm5Lc/aTQ4JZmVOwOjTEF+9XzuftyyJnzl+Oz19dWv//64ivs1WL77lfY+rPs12AhfaS5ZD5+s+p8tWrCNZXe+0uDnl/+jahnwEzA3q+bNxFW0NMuG5McVxkBW0tCOuV8J6DpdSU7jK1Sqzk+bcQjyle6XGM1Xmxu0wwfPrR8f0Kffm/lK+1OPJrT+d/M2Hrz4sfcQ5xXdz8M4xYSDjeW7//3J2VfWb1GaQ/LaF6ujx//Har2/+H7t7lecXxftHRhlCvL7ud7PV7f/qk5efcfv3690s9VwoBGsqI9T3Ns30hxerNuH6b+t74qqo6ffXPd/uGLDKgfb95Uv//bk8T+ufvvHN+v1ev3mj//+98ctKWECZmfVloJWLtdXF1+d/fUnX4qzTJDfK6kwBrIShnad01c2Kslp/n6Fzezx2etAXzHL++m5+HNxKgY6b9QRt7FoaBPaJ/D47Jvapc1J92N7mPIN5yvs3ySkvOd0uu9/tCL8rJzidPBfP/1mP4/BRvvK8dlr8g8SCF/xa/gYVtTHkq+Qdy/mIVD7mGN33AQkD9NrDdQF0bqorzcw5soz9H5l/KqJ0zPV+cG956/bZTI5Aa+YB9P9VQ1Vdob+eEOQ35mkzEBW/NAyreYV8ia+sklJTuIr9RpwU19pDbH75M3/e/nhe9yf4zox0A2BvtKtRqrjZ2enB5X7d0vN7+kePf3k7u585fqnr5trzoN33n3v8Rfmr7quf/qXv2v+Qti6wHI42W17CFqZsvzW9h9QV7du333/b+q/PHam1dX56WHF/iBfZEV9nMRXun/Xv+8M8pX6b6ub2VI/Xv/lbotmR2D8ZHT7vnL909eLD9v/B4LxV+TN7wroCfjZVW5f6ZgRf1bPzDJBfs+eDSgzhBU7tF6rnY4EvhJfkvX/DwaGkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrnB+opCoVAoFHFQX1EoFApFStC+crM/2C+2MkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlxghfuZhPKh+T+QXV3n98c7Ocmg9Ml3a3Rqv50LgY6Ibl1OqzflPzfpuUwawJw6B5MZ/Y1Kxn3Xg2gxgLnXdvYExGxKA5kQ1+br3PpWFFz75MyCkxDmxa99NX6gh5AQUraVCZZv6bjy/mE2peOTNj4I1WjxIfH3xyfLFYHQllwXzSebnQFMZqlxhgRejIbKDHaFfYZL9yMZ90o2f+N6vmmyYH9vfaf/EPDcdAN1izx36zGYP9zprGZNJ/1frOclq5RT2lswR6ZD1/jNx3TVaLOy5ej2bTcuolfzl1H7Y/CX3ZQE65l7nYO1+pUzwfFnZQnZezaHXRzyVyVoW/jpnSzb/m5JwyIfsKx8Hk5wRgsRKqCT3dB1ntEjwrQUdCqnaJDXzFjcgMiC9nZotbSYQiOBDDIENuJjG+Mp3PJ9YkGugmGUL3XmaunBj8AkOmNCSUIV+5seev7CtcTvmXudgvX7mYT+p4Bqd7oO6lLPqj2X5CvD3YVhjBLafGW9P7yoDUrRf23xWaRrDaJRhWko6EVO0W0b5CrGastS83poac/CTEle3hWhxExw7lop++3Xe2MGrBvuLMqDhfGQomwFfMfgZ8hcyp1LWL/fKVDkO+Eqp6IYtUF20+vdeHvS9kSmfxFdLQmte4GjHOhoSmEax2iSFWvo6kVO0Wsb7iKcoYRud0xIR/8OUd0Cf3laXkBqyv3Nwsp+5+IPtuJcF+xTmnE6ywPYseqHhDvmKofchXqJxKXbso0lfCDy/4LJKJ7xTjvCFYxcNTelNfIS9XLB3U/zD3Xs5mrZ3dQtMYVrvEeF8RUrVjRPoKzb67QKIF6LVRR6epfWWgcgq+0k1fa/aax9nDF4SjEegrlnvYM5SIxoK34BGu/GJ8hXoZn1OxayI5gBVhM18ZsQceUKbbSZ9iT7jhc4yf0l1rpK/4HbmLE/+Ur+feiaX9utAUzWrLGO0rUqp2jChfoQbMUIa0A3WlaJaieoUBs1/pzpGl/cpwMRyH0N+DufIyC3vElVXTO7XkDfKVsHOwpgNv5cl17SLSV5zUpT4l2MhXyDLAEOazKO9XLAbkeSqdneEpncxXbjxfIJbi5j7Eu2ISmjZgZcFZNiWv3rH7FTJVO0aMrzDl1XPSMVOpb8pwby+wEX3lpj7Klu5XtuorTNbMGNxQw5fDVJJC71fYCzOChZNTqWsXxe1Xxl3YCVkk1EEfOYcvZ0OmdGJfMbYZzLq89jfydl5oime1VcTdr2Be3I/3FUpffjQx5hF3uxL+ezDqrbKv1NO3XwTRcSL5ihtpbl+xngryFTenwstclOYrIT+dcPthsujJwxmL5p/hthI0pdP5ivm6gatExjuEpmhW28V4X5FStVuM9hVaSs4BzMAKjZ1jcVvLEX+/4r1gwFdubpbTyXRq/5UAe8mYAJv7CnH3wpR6r2qMPQdzl0thvuLllH2Zi7J8JfzC3nyAy6Ldmz8S3SFO6BtDpnQqX6F+bMLswK1X2u8XmqJYbRsRviKmapcY6SsDJ0rk4aN9gMtdG29QnUNrsVUGnWPlnoE7Jf2j5Zzn9Ql8xRok/yrdmYfkwBBP0WkjrlricnpToK8Q6eCqYBCGsmi9j1n6jTcySiDE5Bl94mT1wbElG40nSaOT+Ayx2iWkP4TidZSoiCZG9N+vAGG/2MooKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeUG6ysKhUKhUMSB9pW3+4P9YiujpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUbI3zlcnFS+ThZXBrfWc3oBu5zu6mqZqu4GOgGq2v31ZeLE48L8bDNaDWzeqm/03zFfZsf6gaxrGZ2f/VgmC9ohseL0Y7Bi5oM02pyRkYI02mKCF9GrDKtdpOVTdjNgSBahxVP2Xy1n+P6DaF5qvsyerHkZ77ODZ2MLOC1bOLMVnHOSsmxend6CRwyl5ac7iBWu0NgHfPiCtTpVrHJfuVycWJGuZrRg2l97XJxYkZv12muh+EY6Aax9wFfWc2qk8Vi5vCxenQ6dDwnDqKv9N37JnK5OKlmCyemOtsnJ3zUdJikjxn2yYS5mrn+k1bnUcq0Veroz4A9mpJofVYBNCi11AM20lc8h3dWDa4IfGeRw6HZOw81/1oMTlg+OaZYHEbSm1e+VknCYi3ZM18x4cZlTsfAgd0GNvAVq8A4HuN8zVO1UaPMtrjSHOgrhFz5dzXMBIIe15y+0haMpv/LxclstZo5U/FkcXlJ1BTjOS/q4TA9hPqKIIlYxCjTHWaelSRMKRaOkvdMT+tycVK3jKoE7Rqg43iyWLUdmbESawd+LcdCSFynvOGFYGgFDx4y1leEdEez2i4iVvNujOnnXCSifSW0SPuR8rU5MitjfMWrxPyTs9VbT8Zdj4Ros/vK6rIxiNXMNYMuGD9G4zkvajpMeRjgfYVY7TLrO/+53reljarHiuRBmbXbyXhfuawH/209ptZwMiJ4a9X/4FEJSVxaX6F3mMQhQdD6VTpj3HdfER0YwlhifYXY1c9W1EGfr2M3MZVR26IKc5ivuFNY8hVDo7Zc6x5X5NzM7ytNMWkqi8HMKYl+8lczo6xQc9QMUw4k0Ffog/3NEKNMq8AIpPyDL1a0PivqY6Lk+7qJ8JV6LJulAlNkPLbde8aIdDhxyXyFOB5n3mzfNTDLRC7dI1ltGSGsbGV6B891DQY4Cov0FWqjwRyJUmenlMnGpyP43j60Unj10bu0ph70L7QjqioTS8vocnFyMpudOHsMzyws226/3B28tK1MmNaAGUEZDsSEOXC/uDEilOkyky49nYsKUbQ2K5lIl2farkJ13w3dalbNZjN3Z8KKwHzTifxbFToKfjQT+QrpWvKbveeC0j2K1dYhsSJl2gfYD/4++4o3YAPnBp1zVO1q396v9CpIWYvdHDvq5X2FeMxeym9/v9JbgP1f/ZLO3VO55znNYteImg2TOeMzfGXQj6P3niIilGlzkc5G3CLJi9ZnxVFxnvMlR/uKY93eptT+L7KIMmMoG6vTPpy4JL5C8AodMq+yDqR7BCvuZT1yyDvgW2bO+y0KvYXZHWJ8hVkM+QsxOj57ne0fWYzOSuj9in/WQUrDX5BXXlGl6sF2fKVHG4Ajd1P0ZsDugTwfJjF2Y30ldihlRCiT1oDAP+jqyGXFkhm6SY7ar1C8BBGExMDQH0rc5r5C2caIITOaQtIdymo3GP8bB+bwZy99hRxl8qaQHFP3IMY/Bs7rK4OrGdE1jUbyHD+Tr5Bcmw8ZF/B3MpeLE2NBJ4VJbz/H+cq4q4MwRChzYCdtgy280l1czO/BRMocSHbNh5II5A44BCVuQ1+hjxfHDBm9Nx96qgBfYSYjiq2M9xVGSvYE4fTmHo84/45c4445B2Mu+uxA3I97XlaPBPsd+ApRLbpIvcORk9ms3+TwYXqLvwhfyXBzH6NM4vSTX/Dw92bCwMp/oiFv4FL5iiQCuQMWIYnbyFfYvIYOGXmlH7Rf3mNfoW7+rFsFgMuVt6N9RWJuH0hfkp/7A20fx0RVofh7e/8UiDb8TqROUbUWXP7BUsQQj/SVajajynw7TG403anDQJjGl73kCWG6hSv1WVikMu2Rdpc19GjJonVZ8Y3GK7g5Efoe1lfqv09kRSB3ICEwcZLYg+em3Qf7ZqvBfyWT7mBWO4V8oMrGNUKq20P0368AYb/YyigpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG6yvKBQKhUIRB/UVhUKhUKQE7Ss3+4P9YiujpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6M8JWL+aTyMZlfiE01llPj8+nS6NVsMR4YGYPUzLzhYj4RX9g8ZpH1AjVal1MiYvvpDWJZTn32dsIaalZIzWcGCy9qMkyryYnU+tROqtMUOZw8IpTpt5usbMJuDoxWKRRRfuarnf4DuxcDtUbF7r/TY/0eN+wgZbKJM1vFnqTkWL2TvRCa9uRHCZ7vcZjV7hBUxwipRAgpOzbZr1zMJ9zoWU12xV1O+zG3qpzZMDYGrokox+0bBnxlOa0m87k7Aa2HahGTUSaPxRGVP+Eu5pNqOndiuphPqmoymfRM3KjpMKXEOf7pPmT7T1qdRynTVmmdEYqVPWTWU/xDDasAGnb/I7rnYMc14CuTycRZ8wyLU0pc86/5oMz55JgkmAy0BTPQE/l0h7PaJeRc1bPby5I5HeOElAUb+MpAgbFqrDnA7HOCTw3FwDNkO5R9pSHtcnceMkMx/lvIzBCYWFrXaHq9mE+my+XUWZJP5hcOvzqj/XNkAESYUgChvhI9nDxilOkOM8/KbKHGnYuFo+Q909Ma0z2Hcb4yn0+lLzP9M4nrlDe8fAqt4ISq6o+W/kJoePi4Lsey2i6kub+8IW3DjTH9nItEtK+Ebla82AcmdkQ9ltb4fHeir3TqdWQc4isbuMqAti4ag1hOXTPoeNkEm1xf9MZit9NhyuKE9xVinc0fVzrP9b5NHNeOLFKUWdedjOqew0hfuVj2xhLsK0OJS+srpNUSG2z6jXy641ltF0Os/PpIej+EscT6iqAoWiJGAZPKUsL9ykBdk3zF0KhfK+3DLutf9fJqk3GV1yy1QTQVwmDmlET7uKL5bv1VLwAiTLnIBfqKfUiYBnHKNAqMQMo/meIPQD1W1MfeQPTiGNU9h7G+ctOvLgLvV4YTl8xX3JLZB0f5CnW5IqQ7mtWWMd5XvIPn6XKjVW06RPpK6BlYh+5CTdwixGUkua949dFe/dK3ZNRh8HgwsbSMLuaTyXQ6cfYYnllYB67tl71pyoRpydeYxoYD2bCvVELuTeOTE6nMjhldGr02ZwpfeL+SsFnJRLo8d0ke0z2H0b7SG0ugr7S98KOZyFc81zIGUXJc8zkp3XGsto7RvkK57z77SvgZWP+ZMdTEs1Ezy4hhJMu2mX6hPTTeQrZuYuZCpv1KXwns/+oXk+S5o38mbkTNhsmcdRu+MujH8YsECXHKNLhIZyNukTSXEP4xv82Ko+I8Z0pO6t6x7pCZFuIr3Um9t8ylXzacuCS+UvfNnbjJOzm3srLpHs2KexmzqkyB2P0KsWtJSywCMb4ybrNC3qex52SRMYzleSOozl+Q9+S8ymAXg+7wPPX9CqWWlosjd1P0Zu7r1aq9sKHDJIrzWF+JP9SUEKNM9yNpeIKvjlxWLJnQm+SIahDhK9TNHIuQxG3uK75hOeoL8BVTz2HpLmS/QizHUS7ux/uKMDHJJm90ieX1ZqlI+3swX43OGZGzriRuJTaoqXQsJNfmQ8YF/J3MxXxiLOikMOl75XG+sqG/kohQ5qgrcrbeisUt4vdgY7rn4PsKc6VrNS2n7gI3qHuG/oa+Qp1SkKskdn9A781ZvkGsdobxvkKbP4CtjPcVQUpMk3MmYn0rye3uwB8QOPeB/pGW9whljeQtohmaO33jiup4XyGKRCdAz8In02m/yeHD9Mw+wlcy3NzHKNNhwXZSWqYAABBdSURBVLsd2zI0lkN/djCwgRsjleWUNRLiHpA775xOQ/YrIYnbyFfCAhc81xVYULqHWO0QEb5if5ZhKReLkb4ydrPSgLnQpdcmoyuRPB7WS2yLId5MG34nUlflzj7c6370II/0lWo6pcp8S8uNpjt1GAjT+LI3LP75meWm4onnhthEmZQE7FCIE4YgQYryM17h6SZG731/fqxWnOR9m/GtkLcGJo7jc3MzdJYQ0IcjfW42+71KU2+/fIUsk/RFIMJW5ebmRv//YGgoKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeWG+goWSoolOTCVCUgJB5jJUVa5ob6ChZJiSQ5MZQJSwgFmcpRVbqivYKGkWJIDU5mAlHCAmRxllRvqK1goKZbkwFQmICUcYCZHWeUG6ysKhUKhUMSB9pW3+4P9YiujpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUbI3zlcnFS+ThZXNKNsxX/uNMmNAXGwLSsZiRZqrl7b0PG4HG5OOmedHu0+zRbnXdtGMtqZndZszTf0fC2XivHYlKm0m7F2n1ByIDTFJcBAVHKdNu58fJzYLRKoYjlgJd2vFbIIROk7qYnfIqxiTNbxe6k5Fi9h5UEX3zGF7xGNq2YFVxgJYolUKdbxSb7lcvFSTemXr3yv0k3C02hCGO7mvmTzS6W9b9qQicnvZpdXwkL5HJxktAja+103fsmcrk4qWYLZxTkWJp+TxaLmTevCR8zZi8zXquZm9K0Oo9WprNqIFkZEnCfkjXKU7I6sfq3hsF+8yDoIbOHpdYHNTetFhFS4pp/LQaZy2s+JsFWLHJ2zK+ufBmPZrVLcKxEsZgJSFBLU2EDX7FGX/KVgUq0cSIifMWeM9Yn9X8sFidkbCxhv8coMLG0rtHvDmer1cwpJCeLS2cY5FjedlPRnZHSsIT6SqqMGIhRpitMnpXZ4iZEikUqB2FZHZWpwCEz+iTGPGDOCYnrlDfsiKEV3CBFbqmFtZy1kinRV2x4i5UwdW8Z0b7iRCD4ijDc4UqQMN5XqPS3ETRtl30xDveVzdcKkq90nFYzt7J0FO1xkGMx8m8NhCxOeF8h1tnM+s5/zjzVFbYDLiuSB1X56U7GaIceMoJg/0G8rwwlLq2v9K5FnOZyB7X+IikAe+8rjAWHnExuB7G+4irKOcP1TvRX1Hmp0DQyhoBvuTtmd4606u1UXFfvt4G+0h9zbjSqoq+8rQ2iIWZMI6ck+tsyMhazB3NOyoUn0FfCz1vCEaVMs54LpPyDL287O85XvIFolE68fcRBGDNk4fuVEceTw4lL5itmsfQFRhuG58b2/Ur0ldjOEF7HSAnU/0hyArQ5In1luPQ4N932GtlYmTBNI2MI+NaQr7SD1E/IthjL9/besnYTi+RiaQV0uTg5mc1OnAWrVzj8fRkXC1FJrfnqByRkQL4G3xzxyuyY0aWRXAx51xXjfKUn0uWZXniPuYhihuytG7fVp73mi/qNADOaiXzFcS2n15qC9xZ5Oywva/bXV/yBdab1PvvK8AmHEZ2wrQ3f8Q7FEPCt0fuV7hw5cL/i9hU1vgO+4v5Xv5gkV65iLHYo9ldd6s4yeXC/MqZWhiNOmQYX/ijqrVfOzGpcbzVi9ivWc8Smh3Qsx7q5TYn/swDaPfrXUtWWfVlA4pL4CuUbJql6De6+ZXAixlyJyXDOZHLIO4SAd/JH7FrSEotAjK8E1FbpuLT/RGgaGUPAt+h1vN9uttXnTuN9pf1mMo+k1GJdCLkw6ggTi7/vaPkS2RnrK9F7TxExyiTPhwT+4y8J5Z/wuSdymxyH80Mmcjf1G36VE5K4zX1FdHrjzZQdy3mTIt3H/QolFn9VgnJxP95XQpTpraTMSJ2VMdM0LoaAb1nzRLiYdQ/a7TXnbnyFPN1vPmRcYCgWPxB7V+1nZ5yvpPohg4kIZY66gWfNQ7hdif89GHO+w0MaslBfCdZlUOI29BXxeNF6CbUFHYhBmqn75yuMWEjzB7CV8b4SMKTuIYj1iD35haZRMdANl4sZayR2HPTZUd1yMpsF+Mpq5p/oJYtF8hVi2nUv52OhevRuvuwDmJG+kuHmPkaZDgt+XNiWoTM9+U80mA1cRHLkIQv2ldAzypDEbeQrITzINAXMrYH07pmvDP3cZPxmNDtG+oo8+ZytOdNMrT3YBwNj4JqMMyKZFHNq3X2Lv7e390B5YmF9pZrNqHrSDhMbi3CDzNzCcwf5lbNK4L17c0QpU7i3tkPx1wVhIykWKeMVRv/k0WXIuQ4/ZOG+El5+AhNHpK8DmxzyTM+4+eN75VRlsZXD2ytfGRLLCKluD9F/vwKE/WIro6RYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVuqK9goaRYkgNTmYCUcICZHGWVG+orWCgpluTAVCYgJRxgJkdZ5Yb6ChZKiiU5MJUJSAkHmMlRVrmhvoKFkmJJDkxlAlLCAWZylFVusL6iUCgUCkUc1FcUCoVCkRK0r9zsD/aLrYySYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuTHCVy7mk8rHZH4hNtVYTo3Pp0uzW6EpPAa+0ezeIESEZL3bfKxrsKjarIWmdLF0hK1A6lebHy2ndDQOmYv5xE9J8wqX93JqvIPMKa0BIunxiFAm1ejGZn6DSNHQUA6VA398zM8jckSOpitAo0s3A+GatJ4cGGk6Bj45di/e00J2NhwvkdUuIbASq1i0kDJik/3KxXzCjZ7VtJyaAS+nVVDTqBgEgvYE63v36udyalYi20yoMssQFZoCIGf+Yj6ppnPHDpbTqppMJo6LmxSW02oyn3u8aF+hnMVIpJhTud+NEaVMmY0bTliTy4prqrUz9zuyJBb6or5TYjTtmWQNoZUBetlAwZ4GEkmhEIwYL0fA7T+dNq+yOBTppihW2wbHyho/Ny4hVbvEBr5iK5lvIqpc2yg0jYuB/FyQuzAE/lNEP7vxlUZgTqWskzaf2lboSK/ZUnlx0dm2difWsEg5He53Q0QpU2ITKuEhVuTnF/NJnSlfa+5QhGTVeXZo6hhdknoZjMzNG89R6jC0gpt9DITChC00RbPaLiJW80KqdotoXwndrHixm61C07gYeIrCWpR8E9Xil6ad+EpHw+bTJHE5tdRm53+6JJgNVVxqLocsiXbsK84ICmzSjOIgJS9nvsbCXYwbTa8Hu+LG+Yr4AjYYE2N8hdlgNmdbpISNM0ahKZrVdjF+byelareI9RVhm+k1ucLw9utk06gYJJYUUX5uUS10Hdi2rxgi8nbGk/nFzcW8PQuzKBj/cKiJ9b+Tr5sQNqdh/W6CKGU6twFe/EvqSF5ooljJjAJ8JThl7GiG7ldGzDKjMvOnZwNn12G1kj/fqP9hSNzdiC6bOIWmWFbbRniuSAnYqdoxIn1l9AFCN7tHNQXHMEDVqw3jfIW+CN+yr5BeYv13ZywmBfO/w1fy3bcnE+o7ZE5ppimxoTJvyJ8fkNd7QhPFSqbk7/GoO4OglPGjSdxUWsfukVe73UgLapd6HDzX9Vj1YfU6at/Sv61LRPt1oWkkq50hhJU/sEyqdowoXwk/A+s/M4bavWqjm0bFMPylpntjRb9f+xX+cNE+8JguvUUtc+k5XP/roiJvTIhKtUNfGT5JJRPnPS00Uaxk2uTZoVns66W14wrUFksYTecZd+ti3EEQV4X0fs6oYcyh0nC6Ayu4maJ+3e3tuft9iHd9JjRFs6JIxhp0AMKE5B5pMqnaMWJ8ZdxmhbxPYxYUA5tqPoawLwpv5uneUFFt3VccTZvKdvYu1p2uUzTs0jFc/wNiIYZsd74SsFhzNiX+sHaH9UwTxUqmHXAnFSYaaTSF0M3xCLkf61/mHayF5qRD8Ny0197cmZ6/0rGGLPTifh/3K9S6W0rVbjHeV4Zuw92mRKtCOYawLxo1hY8i6FJ1275CJIaugcupuYDxmZsdleYrob8pIM8InUahiWAl0x7kFZgwcTQDfSV47TY8DYIsapSvMPtI56qI8Q6hKZ7VViGxYo4OhFTtFqN9RZAl0+TcFLr3TuMlT8TAUJUOtb2R6r5u0wi7cAlqCgATC9Epc7F+MZ9Mp9axgW/z1H1uYCxyTvsPd+ArAepx9GY/YsciNPmsZNpyAXY5id3woxnsK6EvdM7MvCDCJmtgBbc7s95FTVvr8M+aqUxTHKstQz4DF45Xxm9Gs2Okr4zdrDSwN/DeKohpGhED08L/Foh4uXcgZ502+Q9u0VfIotEm3Gns98v04sU5DKSPyIRYhnK6I19h5ceOsd9MRB8gzIFyQGbYbAoUy8BohvtKePkRLvxD+whNjqQyt80YF9Jn+Wk7yGqnkM4qGB257QhblZubG/3/g6GhpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUb6itYKCmW5MBUJiAlHGAmR1nlhvoKFkqKJTkwlQlICQeYyVFWuaG+goWSYkkOTGUCUsIBZnKUVW6or2ChpFiSA1OZgJRwgJkcZZUbjq/8J/D+D0ChB03YAAAAAElFTkSuQmCC" alt="" width="362" height="150" />

2、表联合

select job,loc,dname from emp, dept where emp.deptno=dept.deptno

select job,loc,dname from emp join dept on dept.deptno = emp.deptno

如上，上面两个表都是用deptno联合的，但一个是选择了所有表，再筛选，另一个是做一个join

最常用的还是join

3、SQL的子查询

select  loc, dname from

 (select job,loc,dname from emp

        join dept on dept.deptno = emp.deptno

        where loc = 'DALLAS' OR loc = 'New York'    )

查询套查询，在实际工作环境中挺常见的。不是很难，就是用（）把子查询括起来。

可以把代码拷贝到如notepad++等代码编辑器里，看到具体查询嵌套关系，再一层一层反推取数逻辑。

4、在select里创建新字段

select创建新字段，可以直接用...as 创建字段

select ename, loc, 'DALLAS & NY' as city from emp

        join dept on dept.deptno = emp.deptno

        where loc = 'DALLAS' OR loc = 'New York'

同理，也可以用case when创建有条件判断的字段，只需要加上括号即可。这个貌似还挺常见的

select ename, job,

(case when sal >2000 then 'high'

                        when sal <1000 then 'low'

                        else 'middle'

                    End) as salary_level

    from emp

当然，case when还可以用于分组统计时创建字段，与group by联合使用。

---按job统计薪水大于1500的有多少人

select job,

sum(case when sal >1500 then 1

                        else 0

                    End) as salary_gt1500,

count(*) as people_vol

    from emp

    group by job

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAWEAAACXCAIAAABLIX0wAAAQHklEQVR4nO2dz2scRx7F86fVYRBrMWDEKgGvNcLxrsxepIORYDSrRSCCNl7wDouGXhx00K4VgzYOkzQYb5zgBGFhjMdYRCflMKc5aG4NIcTo4j1U93RVdVV1qzzf8XeU9zlJU9Nd3e9Vva4fLfTBmzdv3rx58/PPP//yyy+//vrrGzdnZ2dnZ2cJsAFx+AAjwjg7O7N2/A+QEWMB4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoSBjKAF4vABRoRBmhEHa8Jg7SArG548aq98eKUmhBAz9Rsb+88HtoNqV67eWN39fjAZNcaPLyNOH0erN65eqQkhalc+vLU1kiBJkiTp7y2nGizv9fOPj9rXdSF17EdpmtauXL2xGj0+TZKkf3+lJoSY3fhqmF/V3nJNiNry3mnJrQ1PvtvdSK9fcr19ZDNdKRz89EP8r0+X06Out4+U81kOzO/y9HG0eqM+U2gt/iIdlxEudRwXlV/V4Pn+Rlpz7cqHK+1HJ1LG1KOUmfq1W1sPXmYKewz0VmYlPZku5JPtOSHE7GaceLpZWUPSdXsfGXF6sFY3imqL7cOhQ6jayv2+rQL+OMUZxpumAprVw4O1mhBzc3NC7+5+ax1HWTStLd17lSTJ0zvz+S/5Zc1vPxlaKxhVdLjz8Yz1+n0ZoXeeyhkxjLeu1vSS2uLOUUmRzQvr57Yrrm/GQ1eZvKrhYXuxZh61dnCamBmRXlUm8VgzIjvb0r0fjY9mN2NvN+OSEcX7T+X9amNWCFFbvPttf5gM+9/eXayJLAnkF9PmM3j+z5vKr9OHU5yH6zUhxPxfv3g9SJJk2O99s7uxGo3uUnb2pXv/2ZwVorZ2MOqxXmtdR6maDl7vy4azuPMiGQ0l0lB4dW+p0iBCfk/UPlrff9Z3pIlmZHb1O39qbOx+d/LAUnaw5jBa5pior+2/HuTXX1u53/cVFfFnhKx52H/22fKsEELcfjBM7DchFYhuWmoW83eeGh4NXn/9yXUhhLh29zBJKmTExdq6HDQoD4Qf7y0Jab63m3HPiLR/3Hlq3Glt/aHp2Ld/vy6MwfBU4RRH3ubs8mf2oXG8OSvEtbuHstfLcWOSJCXWuo4yGp/+azqUWLnfrzqIyC7iZvTK8yVfi7eVuTJCVpb1++HJl1t/kMP79YeeIkulVTIi/11GqOsmXuwsGmGUzvIWd14UPEovU17UmDMi6zrZAyGPCH83Y54RtqvL5SkMuH63+LdHZXNjtjjF6T9cT4eBM/Ubq+0v9Mfxk+25tI/Lppd3d5+1zqOUxjfsP9tdqWu5mw4lbt5tr1RciXhwW4hsgKsOrB2WFnFmhMg0yefweZfNQ0DIw9vuIkutVTJi8NP/5NM21c4y/FdmVFo1uTOqR8N+Lx1ipPOBi801KgRGvDmbX2+aVMt7/ZJudrkyQojaR1vxZVuPSJLhywcbDWXFb+bjO9lS2dM786NHg1RrbvuJLPJY6z6qoOnM7zcOTo0jJeWDCOMaSDJCUl9/OJp83v4sDYHa1dt7ndvy8La76MIZobe4bPXgXTJCv5d0gWP8GZFOKea2n2STTTnzuKQZsbjzwliPyCZz07ogUSrO4PU3u1t/TNfdZtcO8qliNsWUA8RMALe1nqOMxje7FB3qQZAOJaoMIpJsPGvZOXmnjFA0+emHdFFgea+vXnzt6u29l8NsHLO4s+MuelE8baWMmKnfWI2yDQr3hTozorb+UM8Ic8tq3HONUTBcbx+lYwptgOfoZgwzIh0DqesRo0djom3XmEJVvxeOVHw/Yvj4k7nRbadd0CBVxCmH76iRptkCT76RkXGh9pkOb2WiKYePKSP0b6SrcmkIJKMH5+xm7CmynLLqekSVMrkeocZk2sKX7v3ob7Ljz4gs4ZfuHWzPiXyTw9vNuGRE9x8b+8/UJdVs0pRuyy/taAuucoZsjCO0RfjpwyXOUbSycufzb3r9YZIkw37v37dnsxaWv+Kg4346JEniP0pbB067kdLBk+SC7TPbuK3/uf213JeRj+/xZMSw3/t6W2kvj7bqSms5ebSdtxZPUZFxZkQ2PZvf+K+2r3EzelXS/QgyIjN/fn5ePd7bzbhkhDm/Gs11PXvL1k3imZX7U7pq6cwIy5RVzr+ls9qTUB3b2w683j7yH6U3vnTbzrYaULl9nh6sm+8lVMkI68sT8ivFG8tahG2bPyvzFNm8sH5enhG2u/S8e1AlIywKhLwfkaIMIpX78HUz33UUdaPLiMHz/XRVbqZ+baX9WLVu8P2u8nacMgXUhTKmh1OHU5zB8/2tW9ekAHIZf/f702Q0uZS78xnpntXK/b7D2kP/UUYv6B+szQp95y7gGTZ4vr91K32FT8zUr936S6Q5HJ4RM/Vrxlunw5cPsrq0Nxr9RQbjzYgkGZ58+Wles3LFk8+IPCTUqUXi6WZcMgLg7zUYASPCQEbQMrXiWCdD3scNe6bTCPfb7RcYZrwTyAhaplYcZAQbkBGXG4jDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRBjKCFojDBxgRxtgyAgBwWUFGAAB8jCcjzoENiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGMoIWiMMHGBEGfUb0Ogv5vwNodtWPFzo92xHdpvlfBLIvaufSTieL9NMLVwUTxC+Oej/KvQASyjMi9eP9NxtW0GaE1Fxp/d2m2t09GWEv0g7ST65lRK+zwMTpMnGyK+42ERPUlGZE9nBi0XL4QJkR3p767hmhf1HpccaQ4r3iF0e9SPddg/FQkhHSgK67Xf5WIcwIf1clywhOCeHJiG6zGBEICVK8GZE1G0+7/K1CmBH+B+O7Z0S3qXaq1GNuT2NfgGrXLp9gfMLtElK2MNTsnnvb5W8Vrhmho60zFFYy9SJevaxcnNGAgtcA6BLizgilqSIjCnDNiLJxRGE1dCrHEd3iaILPpV86XBmhtRm4UGB61yOMFdFRbbx2CLzrEVi0nCiVFo+REQWmeV9DSwNzX4OJz9jX4IP7SWYFZqTQvh9ReFiO8/0IfdWy+H4Ei8FEmTh4P2JyVHrPEuOIAvTvWWorkOaLkNaVSf97lqqByojBfC4Xx/LvBb84yp2iXZKDjAgDf69BC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIAxlBC8ThA4wIY2wZAQC4rIwnI94CGxCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDGQELRCHDzAiDOqMOI4a+b/tbMVGadyyfh63LN9VD1BoRMcllclPlfPJk6gVpAeq50q/pX7kviw3pRlRrAYQUZ4RtnYASDMibpmdU1c/bolGFBV7nj8jHA46K0uNHxUVg+k4aohWFDUKidBoNJSPxp0R8oqjqIFWORFKMyJ7AsENDcqM8HToUXkrtnS9kIzwljSiqJWdMW41ojhqKBUcy3Q41kNCnjA/bswZcZxdwjEyYkKUZIQ0PDYeFYA8IzydatTlCn0vNCPsB8Ut0YiOY9nZj6OGGQejX/SQSKuK85AgmWsgIyaG14jjqCFEKzYaAXhLPNfI1gesmis9zux8QXMNZ2UyI97GLdGKZURoDUH5WWsfWVXpId7LcoOM4IPHiCwhzAcFeDuBfQ1lHbGwJJh1uNyhYplOcc1S+56tspHpcUu0Wi1zxGDNBe3nUUggI6YbtxGK78iIAhPb+zRWCvUBgRESQeMIV2W56fpPymqAXrMyasi/3YqREdOOywitUSEjCkzy/QilNxTHA+bOwztlhFqZ1fSsBm23VB+CGGMK6+JqOcgIPrgXj4sL2BO8LPa8n4wodnXNqbFmhPVk6YfmJEetQasqbolWjIyYbqxGWJ8S2AFVIcyI46hR2K1wrwup+xIBGeGuzJcRljItW7TZUKuFjJhqKj3JMI4oQDuO0KcU6k6C451L+an7bUrfe5b2ytwZIV/fKrSHrM8acWS+iVVd35JNH/t0C4wfZEQY+HsNWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGMgIWiAOH2BEGGPLCADAZQUZAQDwMZ6MOAc2IA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYlBnR6yzo/6mu2R2VdZtayUKnd+4qM4rVUtfnWn3mZWh1uSuTBWYNza7lvqwnHunrb5rFagARJRmR2qo0UnB+fj6BjMgl7zaVzpD1t2KJ/NXeaXqdBa2o11kwLe11FoyDjU+qVtZtCrGwsGAcqVdXrMzElxHdphCi2THuCVDhzoi0XXWayAgLE8wIrT/q/U3/prPbFk7oqNSbEUbS+DOi02nq1zW2jOh1FuTJzNwDVLgyottM9e8iI2xMNCOUD8IzoqQ7VcgI7TwlGdHr5iEx7nFE9ZsC46B0PQIZYYXDOEJOBPVJiaPTZKsHbiOrZITaGMoy4rzXWbCOfRynNkBG8AEZEcbkMkJPAn2pUPeluIyolSvFFj9DMsJa2Sg8RiGBjJhukBFhTHJfwxxSKJ20sK9R3mnSs5tfrJwRFeca5/nSATJiukFGhDHRuYaC2t9Ma6plhO3I8wusR5jDBdu5lYXNZhcZMeUgI8LgkBFmN6HOCO2oShkhQwIZMd0gI8JgkRGWtQpHt3XvYaof+jJCrj9UWCDVC3qdhWYTGTHVICPC4JERuj2e9yyNJY7CGWyrj8YxtuVLW2VGeFjfwnuXjLC+rYkGSoj/ZTZXkwP4ew1aIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYyAhaIA4fYEQYY8sIAMBlxZ4RCQAAuPngLQAAuEFGAAB8/B8SnX2qfs3V+wAAAABJRU5ErkJggg==" alt="" width="239" height="102" />

如要具体了解case when其他用法，还可再查看以下博文：

http://blog.sina.com.cn/s/blog_4c538f6c01012mzt.html

http://www.cnblogs.com/cyrix/articles/1750184.html

5、数据分组并统计

一般数据分组用group by分组，基本与聚合函数配合使用。

原则是select后面的所有列，如果没有用聚合函数，那么必须在group by里重写一遍。

举例如下面两段代码，第一段会报错

---错误：使用聚合函数count但没有group by，或只group by一列

select mgr, job, count(*) as people_vol from emp

group by mgr

---正确：group by后引用完整列

select mgr, job, count(*) as people_vol from emp

group by mgr, job

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQEAAAC/CAIAAABxOf4bAAAPiUlEQVR4nO2dz2sbZxrH+6e9B2Go8RJM3V2CRiFbcOjFYQkVKFqDoRTTLAQdIgRewuLSENBui9qBsku69GBqSqlCRHLKHsQefFjd5mh88R7mHc37WzPPSPL40fdzaubVzDzznfcz7/vOhPSD/wKw2XxweXl5eXn5PwA2iUsFzYGkZtSwpPqAcGjAAT4gHBpwgA8IhwYc4APCoQEH+IBwaMABPiAcGnCADwiHBhzgA8KhAQf4gHBowAE+IBwacIAPCIcGHOADwqFR2oH4aFsIIXaPXyVJkiTnvaYQQoj2MG2evfv388P7dz5siIxm7zxJkmFb5DQ+vHP/s8E/35PKdTdkdQjjvEmSJMnFLy8O7+9spaf++GHvh3cz105bO3cffPmtbLuFeMMJha+1SbKbWTC4rZ27Dz5/+VuWm2ycH8RXiHkyJ/Jgys1MkuTV8a4QYvsoDpQYrMPKrZwD2WXce/ZrkiRvT/bTPz56OUuS2dmzP26Z1+hwQN6M/ZPXi8pzlOtu8DswO+vdaxhtO+3he+dOQoi9Jz+VraomFHMgi+Aonvna0m5TMrj57VyqA9nR9k/eGpu2j+JQiat2YHd3V4iD02kyG7Ybjb293bTHvT7ZbwghGr9//OLn6czeLeuXF29etHeEyDwqQ6HhPr146cDrwSdpMi/eXCin3nvyk5HS7N0/urvFQqspCxxIA5lNf/7rwbYQ8rGltWkUDu7izfdfNIUQ4u7TsyQp4IDjZAHSh744OJ3KDelzt9EezkIlrtCB2ctHQjSPjw9E4/E3SXy0vX04eNoUotk7l+f8ZOB6uhtXTwlDlrv4R6oDvz67J4RoPPw6SzCZnh5k/hm38qtH2976bwOFHMj/nD6BfHeieHBJlnjj8Tf5H5bmgJSg0R6mj9VcgWCJK3TgvNcUotmLT/bF7vHzp3cb7eFZuuns5aN80FIHy/ZQv/rZ9OfnD3eEENuH35Wde5d2wJF6no09pP/uT1/9xnU9IAfh//zr6b2GyKbTrulJs3deLLj2MEmS2XQsn79yvlJuLlRAiHQJKuuV3fzgdBoucfUOnL863hV7e3vi4HR63msK0Xj8TD2lxwGFrY8Oh+UXxat2QIidT5+XHZzqQpn1wHz2XsUBPTi5wFi+A7PvDreFfA0zG7YbUoEbd0DKuX/yVprZPkmXx/m8LSvS5cD2/uCM8rxdngONx98Y64FsojwfdW8bxRzY2rn/2SB/e+KbnhQJTur04ccPPn/xy4X+s+XNheYdX+92C0pcoQPx0XZ62OnpgdbP2kM5ZLWHcwksB5q989n0x780Be2tUEEH0gmkuh5QzZSD6f7JWzsl6jKlHhReDxRpKxecyvIdSKZfP2wIIfZPhse7cwXCJa7QAdnf7W0Hp+/jox0hhNj5tPf9m4tErp/t9UA2tqm2FMV7m3/92xfpWedvB2RQPz3ZE0KIvcO/a62fDF6b74WmP6YTZbm0u30s1YHiwZmswIGsc+/t7an7h0q8CQeavfPk/fDxHfN1rcOB+Xu3Jb4XMmeojYNTudp4P0yTURvv9c5mjp2EEKLxhyevbudUqIoDjjtWILigAxry3ITvA5L5hyjtOgIlBuuwcivjQPpo9zmQJMnFLy8+f/Cx/Ey8tXP3wZ/TT5LGbZgO29tCf69VCO9tnr37offwoy2Rfgc9fKm+3Zm9+/ZLWZM+d9VTMue1t44lO7A4uDU6kEuQ/Q2FBSWuzoGbpoYl1QeEQwMO8OHWhuMaHkoOE1WAA3y4teHAgTLUsKT6gHBowAE+IBwacIAPCIcGHOADwqEBB/iAcGjAAT4gHBpwgA8IhwYc4APCoQEH+IBwaMABPiAcGgscAGBD8DpwVTNqWFJ9QDg04AAfEA4NOMAHhEMDDvAB4dCAA3xAODTgAB8QDg04wAeEQwMO8AHh0IADfEA4NOAAHxAODTjAB4RDAw7wAeHQgAN8QDg0Sjsw7keOfwkp6o+V34w67gbfdr1JiM4oUK7/WtRjmMfP6ja3j/uR/Vvfhap1uU826ujnSA/iCkE9ljyVsslfVoACDnhS2GyqjwPjfqTevlHH3Ym1n437kdlzjH7k0cBbknHEq3E/0g4x7kei07d71gIH3GX4T5Z28HmTo8uNOiLq9ztWjxdRpFS8IgcyceGASmUHtP7r7zejjuPRp3Qc8xHruUu+kgIddt4e9cd216I4EG7p9PuRPOK4H3VGo456Anmt+iWnB8z3W5EDaa4jyqFZU9EBvTuU6VBKRzf6fKg/Bx3w39l5YVaFZAecO437keiMxrIzjzpWd5//QdsqTzXOJViBA9n1kA7NmmoOGNOW9Ma6Zv12h1JvhTofNmfUVrmhUjyTKOVcZg8gzYW8J0sduEo7c6qA1tuV/1Y3z08ld1mBA/nVwAGTSg64HuDm403pe8pv7bXifAkavD/hkhT7zHnX/KiOmouuib0Lf+sxP+5HUacTmU98Z7/X/juTYNkOKFcNB0wqOGA9KIMTHr1T6dPStGV+8wNvhgotUYyVqGPRrstYfhzwnSzv5Pp/5a3GYyB/6ue/jvrjJTtg3QY4oEJ3wF65uic8nn6UdxLrR4EXQ4VfgefHcL7MLdAjCjqgncxc3Wtn0F7/akOIMSY4F+8F8IRjXAgcMKE64FwXWvF63/Cov7R+FOh95R1wHEw94VIdcB4s2xh4ZpjvjYkvb9zhuD/o4A1pDtEBz6NaN8P3PDeXvcafKd8HRh3bInN64qyS4oD3ZCEHXG2qO9psLep0VvON7ArjgA3JgdCbSH3W71jzOju4Pk+gfCc2Hnfqmxj3N+N0q/9rsL/FezKvA+nnOfu6pASmbsaXtqLAARr4+0J8QDg04AAfEA4NOMAHhEMDDvAB4dCAA3xAODTgAB8QDg04wAeEQwMO8AHh0IADfEA4NOAAHxAODTjAB4RDAw7wAeHQWOAAABsCHACbjteB65pRw5LqA8KhAQf4gHBowAE+IBwacIAPCIcGHOADwqEBB/iAcGjAAT4gHBpwgA8IhwYc4APCoQEH+IBwaMABPiAcGnCADwiHRmkHJoOW459vbQ0mjvZu7N9Xb4u7rmM5yw1djecwcdeuxXs12Q/VYxlVxV3r8L4TrJNFDsiLvflCa0b1cWAyaM1z1TpH3NUCV3+nt00GLe9edrm+OuKu3lMng5Z6toAD7iZtJ/3gocu8QQLhTAYtIVqDQW1qrROVHVD6g9aXHW1a+sazNCfQLQO3OdgTqztgGyxr917GDRAKJ62xPr7WiYoO+LqG1Wp2w3T+4Oo98pHl7la+ksJdcWUO1MmAIusBOOCimgN6pnaPmPcjbYhI/xB7ul/wPnlKCo4dS3AgnUkbF+q7gBsDDtCo5IDZ6Y2IlbVi/ktNC/t+mLN6u1zX5moOuNe++ppY2z1rqs8QcH19DQeoVHDA2fHUntONjXEgtkeDiXVEynuh1Y4DlpcYB1hBd6DAXFjp5vb7Q3P/Qm/ubmg94BjgWoNJeOmyfuAADaoDRW6/NaMOKFDwHftNvRfSL9d4L1SXXgUHaBAdWBimsYo0d9H3t38cKDd4wlV9H9BLtL8P1GIwgAM0SA54BwFlNVC82f3l2X2nwrdZO5I1yLhWvgu+E3scNsawxcuY9eAPxw6gDvXWBfx9IT4gHBpwgA8IhwYc4APCoQEH+IBwaMABPiAcGnCADwiHBhzgA8KhAQf4gHBowAE+IBwacIAPCIcGHOADwqEBB/iAcGjAAT4gHBoLHABgQ/A6cNP/61iTGpZUHxAODTjAB4RDAw7wAeHQgAN8QDg04AAfEA4NOMAHhEMDDvAB4dCAA3xAODTgAB8QDg04wAeEQwMO8AHh0IADfEA4NOAAHxAOjdIOjPuR418HjfrjYFPKqKNs74zUwwaajHJDV6MexTiv+5iOkrMfahVph7sadRyX5S96XSxyQF7szRdaM6qPA+N+FOxhap/KO86oIwo12eX66hh19J467kfq8QtUaB9v3qIfXKs3VO56CYQz7kdCRP1+bWqtE5UdMJ6J3iazHyqNgSZHuf4yvHe3ugNenQO1rp1QOGmN9fG1TlR0oOggYHUWtTXQ5CjXuT3cFVfmQJ0MKLIegAMuqjkQyNRqUmejxrwl0GSX69oc0uZqCQ6kBWozo6g/Cp90/cABGpUcKDoPmjNfgpZq0st1ba7mgHvtq6+Jtd2zpvoMAVdXV3CASgUHis+D8m3pRvMFRaDJLrdkKVdXlccBa2jCOMAKugPlBgGrs+W3I9DkKrdsMVdLmAsZFWVnk29b6jIYwAEaVAcCt9/ZZHXSvPcFmlzluutZ8Xsh/ZqM90J16VVwgAbRgTKLYWWz+yNAoMlRrqeidBK1qu8D+qrY/j5Qi8EADtAgOVB2EJAUWGU6msxyAxejrXB936e1xgXfiT0zNNfL3Bpo4A/HDqAO9dYF/H0hPiAcGnCADwiHBhzgA8KhAQf4gHBowAE+IBwacIAPCIcGHOADwqEBB/iAcGjAAT4gHBpwgA8IhwYc4APCoQEH+IBwaCxwAIANAQ6ATcfrwE3/r2NNalhSfUA4NOAAHxAODTjAB4RDAw7wAeHQgAN8QDg04AAfEA4NOMAHhEMDDvAB4dCAA3xAODTgAB8QDg04wAeEQwMO8AHh0ODiQNzV/yXB1mCSNU0GLa2pG+e7mW16s9bq266dzyxDPZf/ZLLBOENrMHHtYl5egXD082s1AV4O5B0jvdvyZmf9yW6Rf3R3irir95e4a3W8uGvurG8perLJoCVEq9XS99RPZ2+xCNyv9AyDQRcO2PB0QOtvRu/Rful3wDyg76QhB651k8IOdAeDllbX8hyIu3JX02twfc3XAWUD3YFF3aWAA+pxFjgQT3IJlj0O2MWAOVwd8I0DcVdYkyZ3p8im4oGOV8QBOQvJZAw5cH0dd91jl3OLBRygwdMBrafr60q7ZwVWm0qzq/9RHHCdLJcjkwAOrJFlO2C8GFl23kXfC5lDgtoJrfdCi4uURzd/WNiBYnMheYDsfRAcWBM8xwENtfcoT+X8z4U6hblndtIi6wHrce86dr5wbg0mcGCNbJgDZjdYtQPaXoUcSCWI4cD62DgHHGsFT7f0vuPUNgYcSKdeBRbgRkPcbXW7cGBtbJ4D2rM59J1YX2JYR3CuboPLofB3Yu1znL0Kr+SA+fnaPvwmw8UBgHCowAE+IBwacIAPCIcGHOADwqEBB/iAcGjAAT4gHBpwgA8IhwYc4APCoQEH+IBwaMABPiAcGnCADwiHBhzgA8KhAQf4gHBoLHAAgA3B68BN/69jTWpYUn1AODTgAB8QDg3bgf8De3zDJYmhNoMAAAAASUVORK5CYII=" alt="" width="131" height="97" />

具体可参阅以下博文： http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

6、利用over (partition by)分组并算相应值

之前的group by主要用于分组统计，而如sum, count等则是与group by组合输出同组的一行数据。

如果我们要对每一行数据都输出统计量，我们可以用over (partition by)进行分组并输出。

这个可以用于：分组排序，或分组聚合等等。

举例可看以下代码

---按job分组并给每个emp排序

select job, ename, sal,

row_number() over (Partition by job order by sal desc) as ranking

from emp

---分组并按job求各组平均值

select job, ename, sal,

avg(sal) over (Partition by job) as average_salary

from emp

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAZ8AAADeCAIAAABt85q+AAAeEUlEQVR4nO2bz4scR5bH50+LQyHWTYMR2zMgukpotLTZPagPRg2pWi0CM2jRgrZZXBT0ooN2pDU0YyhPgTGWFxuEGzNsGzfySZc+6aC65XHpi/aQkZnx40Xkq6yqrhfh7+ckVVRlfN83I74ZGZn9u/8zeP/+/fv370tAkZM5MmsRKAkkze+QbkxyMkdmLQIlgaRBunHJyRyZtQiUBJIG6cYlJ3Nk1iJQEkgapBuXnMyRWYtASSBpkG5ccjJHZi0CJYGkQbpxyckcmbUIlASSBunGJSdzZNYiUBJIGma6nR4ph6PTum3x69fHh7//aKCUUjd27zx8+dM76keDjz6+c//ZD++up671Ezbn7HjfdUftH5+VjQODwxeX+runR01jWZbl5fN7+gf3ntdfKeePdpRS6ubjV9bhK8fDna2llrJ8+830/p2PPxoopQYf/f6Tz5rTWZZlWb56fLPqs1ZnWdAOij7E0823anF6NFBKDY5OF97XDk7eEIO2Vmi1DD76+M796Tdvzc4CZZbl4tf/efZQ22OZH+4sTtxucnx0203KDxV9+eJwoJTaefhVa+Pb5/cGSg3uPX9LHD0dVk63t6dHu07T4Pbx6wX5I2uiJ8YK6Wbkj51u1fy8efOmNXzrX93+/G9lWb45Oaj+++kXi2hn66hlMX/knk3r6GfH+7Vea95vPt0oq3S87TyaO19TBydvYoFDtAwOTn7uKnPx+vM/3gjY0yvduuymx0en3bT8cNHfP9mzHNCy9h6/WtAdpEKPO9PKpcrZxVcPd5RSg9tPv7tclIvL757eHqg6w6ov6rP17qf/uNtrJkqhM92IsdYOqPrKaKVbNXgPTv78aMdcgpwe6bF57/lluTg9Ggz29m7Wv1pXjtC1fPlgoJTa+5e//PKuLMvF5fm3zx7enzZn7Ox4X6mdR38+OaBSb5PpRlullzbGFK7Wvea1hRpwZsu7X15W1+fqYhIp8+eTg4FSavCHBy9/vHTnfbizCB12B8ZHl90B+ZGi9fJNx1lVZ/ILt3LldNOn58n3TWu1KB48+NK2c3H53b/tK2f9mxS90+3W3bs7zaLVSrf5ox2lbj19XY3iegmy+OJTpfYfP76nBg++LOePdnYeTp/uX0+6Vads595//kTtIFTLyE+/WFQiDk7edFuwnKpgG21VHW/NFDbDjZlu/n8DZeoa705/9o4X7SxC3O5Q0R12h85StGi9fDt8cZnNwq1cNd0ol1vXvKXw393+16+TvR4sd2dq3AHtH794sldfA8x0e/X4ph6z1STVw/fseF+p/eP5yYG6+fjZ01uDo9PXx266EZ2to5bLLx/oW6Ubu3fuH//FWqNUKg9O3tTXsDbeNp1uAaua+3atpJJVL+WIWzE/9xaXPz473DWvu4EyF1982vZknoXgzW531EXtDhcdtTt4lqJF18u3u0+PDzNZuJXXnG5KDf7w2fw3se9mpdtZtdbfefjVwki375/sNfcb1SGqaanT7ezV45tqb29P3Xt+eXa8r9fDG063slz87xcPR8am+Y0/PtEb7tX410vxanur2QvacLqFrCrL8ufpXVUrcZq60s3gxt8/PNXTOVSmVeKa0i1md6zoiN3hsxQruumvIouFW7m5dLv9+d+cfbdf/vqnfe45l0jvO9P94zO9Pzk4fPG8SbfqV3U+VJfZtmH/+EzfZh2cvNHX46Dla6xF8+6Xb5999g8fV7Nu5+j0stnh1hOsUtTsBW023YJWtY2Do9NFtY5rVzhdd6YNOwfT1/VsDpapV4nm5r65R9PrzrSGsDtadNjuyFmKFF2hl2+5LNzKPummtzrMfTfzuVJ1GnYezf0TvpYpsDVWSrf6ynj37l39SfMo1KIJtaNT7bSxQX6N6Vax+OZPN5tTqN9TsanjbaPpFraqLMva2Z1Hp87tG2ffrd4Obp8XhsusWnT2GIdZQ7pVWHZHiw7bHTlLkaK7HUsTZrrN/v3hyx/Np6J6ENXvyhx8bj0zrW7nnbWb92gqLVZMt/oWSlvSvshkc3Dypk0y50D+DdK6azmbHh4++e9vzy8XZVkuLs//69MdLap9hcrG3CvcULpFrKq+oHfb9vYG9pNF3lMF/dhfp1akzPr1jd1/PP5r9Yyz2onrn24Ru+NFB+2OnaVw0aQvOdDzfbfmxnzx+vj2wPFy96i6nadeAbpx+CLRVW/v992a9z+qEaWU2j9+9eLQfgpWtnc+z0PpZtzQUp2toRbq4Gr3wZeXeoFkPCWtlwnGXuEaVBGfXkasujSVKOW82dv1vlstUF939o/POsp8e/rgY3ewR/bdOsM+bHdH0SG7o/KDRbf8RtPt3U8v9ebnjd1bh8fWi93vfnh2/87uDaWqJz/Tr3813ttqsdvSY+V0a/dt958eHw1U/X5ujX6X5t69f/InxjWlW/nup5effXKrOpnqxu6tTz579sPbpsRbT18b39XLi70n328y3fTOOGlV82Z4E2/WG/3cdCsvT492lFKDwyeP42Vqg/Qf5qgbu7c++Wf9Vw693uYN2d1VdMDu+FkKFf3C3Uj8zaUbEPq3mf2QWYtASSBpkG5ccjJHZi0CJa0MtaRjL+3AiiDduORkjsxaBEpaGaTbNkG6ccnJHJm1CJQEkgbpxiUnc2TWIlASSBqkG5eczJFZi0BJIGmQblxyMkdmLQIlgaRBunHJyRyZtQiUBJIG6cYlJ3Nk1iJQEkgapBuXnMyRWYtASSBpkG5ccjJHZi0CJYGkQbpxyckcmbUIlASSBunGJSdzZNYiUBJIGiLdAAAgA4h0uwIUOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmDdOOSkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSLJVus0IppVQxaz86nwydj84nQzWcnHtfsT7TBzOPZHThfrU6gv6yPpyypWhpHn4PfYmY43duVWA2G3qcX7U/sSok7LjGWlz7TGlum/FL6lx3FxMee7Yj3iH69RyrBWTBMuk2K9RwMim8LFPD4bD9yE2388lQFRM38kLpRuXbrGjGn/FP3UQO9U0M1o5ECMza88nQzeD6f5ZQs2rHQTLwV4O5dnPctmpx2kwLqjHRCI40eaoY2t1D9Os5VgvIhSXSTc9GOz2qUTKZDJuB48xN/V9vRRdOIWulZo/DSIx0H3dF+qSbnW3OJ5SRs/qf/up3nfnGvTM19Xu1GFW7BjhVBpoIVSz15iH79RyrBeQDP92ayWjNSj1Oztt4s+dm8z9vykZSyJzN1rjjXGQFpRs1k1sf+Om29nhbJt3MHHEDQdHn1tiuiDRRqljqDSU9e47UAjKCnW7GgDDHRjMpZwU1N43/uKMtmkJ1jLn5UO+VRAaioHSjPm8r8mwMTdmrtd878dLNMp84rTPj3Nv32MWsLj3SRKliaPcud8v3HKsFZAQ33chEu3ImazG7sqeBNYycUd2RQrNCqeGQGnLGbnAoUraQbtRzAHIeW2tgcv9+2+lmCKM21poC6nPfjgF7Yao3JAJNpKqwZPKs9+w5UgvICWa62SOS3sGYFe6F0Rky9kG6Uqgazh2LtMCju8TWbs5zg0C6beHO1AjVdonjL8zblZGzWIo2Uao44t19i+V7jtUCMoKXbuT7Ft41r9p8a8eJ+2aDfeHtTCHGgKP2owSlG7UgINYNV87aLLDvtsai+O+72XIDW/H+hagRHGmiVPHk2+uyHj1HagEZwUo3/9TbY8iYlsYlkRjG7tOuzNONsMB50Gg/nQnsUK7/jYXl0q37OeP1PjM1DcIzUxCGk25UzAS2/WfFsCis+xXvSOTkpiCXMF5USL4zvXIlhnfEzecKduH+OmN1mOlGvD5Grzbtb9o/izT5qjji19JzpBaQDYx0o6+2ekS4je2zdXLKG2PMv9t1vk2u3exfCXqqEL7ym5JjD42bOebc0W9gTRHfYI10bTSTG4rmpgWryVFFNziGuIfo2XOkFpAH+DtTLjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmDdOOSkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNES6AQBABiDdAAB5QqTbB0CRkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmzVLrNx0oppcbz9qOL6cj56GI6UqPphfcV6zN9MPNIRhfuV6sj6C/rwylbipbm4ffQl7g5Zv+u/IBk92d1g/Vtg9H0Yl1lRmqxe3CPa2pz24xfUuc6ZI6lKtBiW+Idol/PsVpAFiyTbvOxGk2nYy/L1Gg0aj9y0+1iOlLjqRt5oXSj8m0+bsaf8U/dRA71TQzWsDmVB62Oi6ljBy35Yjpy49nR7V0oDFYqk7l2czRZgp22+bi1wPEj0uSpYmh3D9Gv51gtIBeWSDc9n+xpVY2S6XTUDBxnSur/+hM1OD2dPDDHoTlcO2SunaA5dkoRbaRi/1f0J9tMN0uSJ884Ge55Mb4baSJUsdSbh+zXc6wWkA/8dGumkzWv9Di5aOPNnpLN/7yZGpmeZiZY445zkd1OutFTIzyVqRbynn7r6WbmiBsIij63xnZFpIlSxVJvKOnZc6QWkBHsdDMGhDk2mlk6HxvXRWoh54626PSsY8wNgXqvJDIQr//OtNnc8boNLwmoFi/xtpxulh7itM6Nc2/fYo/ndX2RJkoVQ7t3uVu+51gtICO46UYmmvXvOt7MoWMNI2dUd0zP+Vip0YgacsZuMDVJtpBudbfeFvVy6eZJ31K6dZTS9Fyf+3YMtIL11yNNpKqwZPKs9+w5UgvICWa62SOS3sGYj90LozNk7IN0Tc9qOHcs0gKP7raQbhr7AXHaazdrJ6Bd4vgL83Zl5CyWok2UKo54d99i+Z5jtYCM4KUb+SKCd82rNt/acUK+2kDfVFAwBhy15bXddKsVdK0IqBYv8baebu6SLbAV71+ImvIiTZQqnnx7Xdaj50gtICNY6eafensMGbesxiWRGMbu067c022pZ6a0ywLSrfs54/U+MzV9wTNTEIaTbtQkC2z7z8ej8di6X/GOZN9URKT53V6YL5J9sGLE/vBa0815545458oS1HzdVh+6Vd1mulGl0O+IWd+0fxZp8lVxxK+l50gtIBsY6UZfbfWIcBvbZ+vk5dAYY/7drvNtcm7bvxLyVMG5Bae9olrNBqKWLaRbh79GM7lrSDsQaXJU0Q0d/vbsOVILyAP8nSmXnMyRWYtASSBpkG5ccjJHZi0CJYGkQbpxyckcmbUIlASSBunGJSdzZNYiUBJIGqQbl5zMkVmLQEkgaZBuXHIyR2YtAiWBpEG6ccnJHJm1CJQEkgbpxiUnc2TWIlASSBqkG5eczJFZi0BJIGmQblxyMkdmLQIlgaRBunHJyRyZtQiUBJIG6cYlJ3Nk1iJQEkgapBuXnMyRWYtASSBpiHQDAIAMINLtClDkZI7MWgRKAkmDdOOSkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmzTLqdT4aqoZiZHw8n59QvZoVyqL9oHcs6XNVkH16FOrhG4uaYlbZaK+1GbdXXqg98c6oG1xrrsLPCOp4+zLLu8GtxOjO12S3Wz/qdra500527/QZ7to00WwjryQMHFCz1G7A9uOlGzFMzqCLpRjdZP7IPbqXb+WQoIdquYongajyfDLV8dz7qWWVNiYh9RNPG080+3KwInRirxf5Z33MWSTd9yEnhBwqv5w5NTBMj5woIhJdu0bGxeroRQ7Qaws4ybqvEzaFFzopqSurWWTGczCZDyenmHr89ulem0bGrod95C6bbrNBHn3npxu854hNbLtItLVjpFj/7G0s3SdnWlW50ldW0nBV1OcPJueeWvHSzl6HmjbQbbvq7rs7gLWSnqo5veOm2RM++c2YLz0GkW1qw0i1++ldPt1lh7ovoGdVn1m6Sjrs5clLpRcesUMWsCjffLVnpZuWDeV68rYRqGaqKmSOq+k9PVR3f8NKN3XN8JHKlOvtuYq68gGbz6UbvwoZ3fNsmWYOn45GLUWqju3FmVqiiKMh1To9081nvU4X2DJCr6yZR6oVdu8BrBV9PunX2bAyzVe9KaTGSrr/AZftrN+95RXprNwM9ndrSqH9Fb66uok3XtHYzH962W6DVcs1ZwZ2biyZnHbWUqP5rN07PgSXaSqNM2hAFDhL23ZyB1/Tm7yFvE/47Yo1u0hl39olKN/LRgXGbSj9W8F/a6bUi6rnvxu2ZaFlxZ1fWAAUeMp6Zhl48kPM+SK90I5cRotPNO5h977elZ6ZWl32fmRJurphOsp56AR/u+27elXud77v5+9fO/pyEIRRJBD8PnB0q++uC083dTLLmf/g/9mVoA++7UXKW69n/aeclO76thm03+SzztwqBd9idB0nKnQLk/jf5KJ/emfJvibZC93NGz5pgujW7VQHfjONe976bq8vvLLBLb5rQ72RxJbn9B3rueMAZX7jpHmPPdLY+IkEX+DtTLjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmDdOOSkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNES6AQBABiDdAAB5QqTbB0CRkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmzTLpdTEeqYTw3Px5NL6hfzMfKof6idSzrcFWTfXgV6uAaiZjjlumJ1V8witRlGZ9U36k+cN1xfmv218uY+Ik2Dm8d3Tubdt+hny2jKtquXTGdiPZsu2i2+OOSPnBAwVK/AduDm27EZDSDKpJudJP1I/vgVrpdTEcSou0De71DlTwfq9F0OvYz3PjECkAn4E1cQy6mo+VnWLiWmPnzcXg2m2X3PWcRe/Uhp2M/UHg9d2gKD1TvKCIGI2DBS7fo2Fg93Ygh2jnLr53+6aZTwQ6HOvH0J/PxaDqfjjrrXo8jwVrcALO6C6ebW3Q/lUF752N99LmXbvyeIwHGlot0SwtWusXP/sbSTVK2rZBuTShY6VDN2Pm4rnQ0vWg96Ui3lScY/0Sb5QTTzR0BwVvITlUd3/DSbYme+dkcBumWFqx0i5/+1dNtPjb3RfQU44+566Fvuhmzypxgej0yH6vxvAq3D5x0a/eMVol9frqZ58nerXKuTdYtdr+z1yPd2D3HRyJXqrPvJubKC2g2n270Lmx4x7dtkjV4eqabOf+ce+7R9OLDh/lYjcfVHqaTbhbBDfxeJnG3IMJPdMyt0rastoLrSbfOng0jV70rpcVIuv4Cl+2v3bznFVmt3ez/UrORWrExp5yeu8u7FKvFTNZqLzCy5qk0tosmZx21tKqObwTWbpyeA0u0lUaZtCEKHCTsu1ELhvGc2kPeJr3SjXz1oN5TJF8cWSbdmh565Ajz5bJwShlnzV/i9VoR9dx34/ZMtKy4sytrgAIPGc9MrWHiPjMVcnXsk25+/XVxZGqIS7fYLrr7vGGDz0ytPvs+MyWKWTGdZD31Aj7c993Md031B+t7383ew/BfuRIxhGLviI2dJyIf2v8R7755N1NmW0e6tba3/a33ztRWGtutcl+53uD7bqYkYvnF6Nn/aeclO37Tj203+SzztwrWbZb7xwTks4P43yp411z6gaEXrNuBuVcV/qMLTTXNxqF0a54vBCxdy3O7jrd56UNbHfvTOvJLvqpAC3WD7z77oO0PGxVfuOkeiTcXVywSXCP4O1MuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmDdOOSkzkyaxEoCSQN0o1LTubIrEWgJJA0SDcuOZkjsxaBkkDSEOkGAAAZQKTbFaDIyRyZtQiUBJIG6cYlJ3Nk1iJQEkgapBuXnMyRWYtASSBpkG5ccjJHZi0CJYGkQbpxyckcmbUIlASSBunGJSdzZNYiUBJIGqQbl5zMkVmLQEkgaZBuXHIyR2YtAiWBpEG6ccnJHJm1CJQEkgbpxiUnc2TWIlASSBqkG5eczJFZi0BJIGmQblxyMkdmLQIlgaRBunHJyRyZtQiUBJJmmXQ7nwxVQzEzPx5OzqlfzArlUH/ROpZ1uKrJPrwKdXCNdCWCrsgS6lZJVKEtMurXvzI+qb7TfmDa2suYznSreiCPHWwyVLmtkSZbVVS1dtO0Knp4232zxR+X9IEDCpb6Ddge3HQjZpwZVJF0o5usH9kHt9LtfDKUEG1XnYlwPhmqYuJY4VgzK7zpPSvUcDIp/Hg3PrEC0DXkfDJcfobFapkVSlWV0CEVbrKuXO0XIk2eqpBg/btJ4QcK7/Ad4yg8UL2jiBiMgAUv3aJjY/V0I4aoMZWFXB7j6abrccryrHGNnFW5NiucpdlwMinqT2bFcDKbDKv/rseR6Ilu+/GkB5q882yojDQRqmi5s0IfYualG//wkQBjm4p0SwtWusXP/sbSTVK2daRbU45dl2+NHQ1NqlnxVk3mWVGbYKbmetay3ftukX7o4CNuCetzGGiiVHXo9tJticPbVxC3hWcp0i0tWOkWP/2rp5t9x6ZDjT/mrodYIhjVeLfcTg3mBDUmnDn39FJlVqhiVoWbeaB6z2iV2F9zurnZbJy9SBOlqkO3l27sw8dHInegOftuYq68gGbz6UbvwoZ3fNsmWYOnY1PSiurIlqQxQc2p6dyODyfnV1ezQhVFQSwJTV97mbTedGu1tzK1C5EmUlWHbjfdOg9vDLNV70ppMZKuv8Bl+2s373lFcms3Z4IYwgPpRs1yaqI6JnlzkHpMu2It1qGXW7sR66hoE6WqQ3dg7cY5fKCglUaZtCEKHCTsuzkDr+nN30PeJvFHLi5uPFlfbrbaPKitqqurq0gk9DJpA/tugeVcpIlS1aGb3HfjHp5oWXFnV9YABR4ynplaw8R9Zirk6rhE9Ddle9a0hfrW1Acig0x0um36manVT99npsQ4XTGdZD31Aj7c993cF0rX+r6bvYdhDRrvvnVrxG7bHX3N/Ccy3N1cc47k3me5vbS22z2tpxbWgakm6zP7C5EmX1WHbiKOuIf3f9p5yY7f9GPbTT7L/K2CdS/l/jEB+ewg/rcK3jWX3mPygnU7BMwhA7yuxrGG2mVzj6WKIpRu5j4lcR5Wr8U/utlHpMltptK+W2947FF38Y6Z/uE7jIov3HSP1COhlZwH1wn+zpRLTubIrEWgJJA0SDcuOZkjsxaBkkDSIN245GSOzFoESgJJg3TjkpM5MmsRKAkkDdKNS07myKxFoCSQNEg3LjmZI7MWgZJA0iDduORkjsxaBEoCSYN045KTOTJrESgJJA3SjUtO5sisRaAkkDRINy45mSOzFoGSQNIg3bjkZI7MWgRKAkmDdOOSkzkyaxEoCSQNkW4AAJABSDcAQJ4Q6fYBUORkjsxaBEoCSfP/pCFpbTl2ihoAAAAASUVORK5CYII=" alt="" width="225" height="120" />

具体用法，建议参阅以下博客：

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

7、数据分组并筛选：where与having

用where进行数据筛选在SQL里最常见。其工作环境中，主要嵌套子查询、或者多条件（and or联合）使用。注意结构就可以。

另外还有一种数据筛选是在分组以后进行，即group by .... having.... 之所以引用having，是因为where语句无法对聚合函数进行筛选

典型示例如下：

SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

——引用自 http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

我们可以用以下示例尝试

---按mgr, job分组，并选出职工数>1的组 
---（由于select是sql运行最后一步，比group by晚执行。所以having里不能直接用empl_vol）

select mgr, job, count(*) as empl_vol

from emp

group by mgr, job

having count(*)  > 1

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAPoAAACYCAIAAABCsmM7AAAOMklEQVR4nO2dz4scxxmG/afVYVjwsMEsXieY6VkUw4pcVgThgd7JwoIJixUQc1AzsEGENRaCSWTaHhAJcnBAeDEiLbRIJ+Uwpz1kbg3GOOzFOVR1d/34qqa7tdPdO/W+p52u6q6qt57+6qtqI7/3448//vTTTz///PP/7PovBHVYDnQ1vQfcoZuuteCedkwd7FLDggPA3SPBAeDukeAAcPdIcAC4eyQ4ANw9EhwA7h4JDgB3jwQHgLtHggPA3SPBAeDukeAAcPdIcKBR3OfHfcYY2zl5lqZpmp5PBowxxkYzXrx888+HR7c+eL/HMg0m52mazkasUO/9D259Ov3721pDpQuyfjCt3TRN0/Tyh0dHt7a3eNMf3Zk8fbOkbtra/vj251+Jsq6qggOZB64pc84LL5SMXKXlbNRjjPVGM8nExdkBY4ztn75O0+Wbp5M7H3E6trZvHT364bJyY43invmz9+BFmqavT/f5z7uPl2m6fP7gt1u05Yqtwtz905dlfZSGShfYcV8+n+z1tLLt0ewteRNjbPfed1V71aRq4O6aMmJe2PbxnNNaGfeM9/7xXL+0f/o6fTsbbesQ7E2eV2yscdx3dnYYOzhbpMvZqNfb3d3h3Xx5ut9jjPV+ffjo+8XSvC0bzOWrR3zY3P8qKrWU84kXxr2cfsIBf/TqUmp69953OSF8ZVq++dt4p/jZVa3Cnei9Y8qUeVkuvv/zQT9/E+rgnoXybCVJs7VlMDlffn3UZ4z19u5/u1imy8W39/d6jLHenS8XlRprEvfl47uMDU5ODljv8Ek6P+73j6b3B4wNJufC70+mVMzWBlPDyGyoqyvJuL94sCdZmqb5hOw9eKEScvnqi7t9a/87o+q4O6bMnAj+W8ShOrMk7M1vymlPnxz2tMXz2ckOY6x3+KRSY03ifj4ZMDaYzE/32c7Jw/sf90az5/zS88d3mVi01JVVyhEHk/M0XS6+f3hnmzHWP/q6ap5cGXfCxIILc/3/1e+/+PcG5e6jWeqaMg33y//8gwfcLBepFZREssQ5EETvnDwjX0e5gS7jfv7sZIft7u6yg7PF+WTAWO/wgTwcC+6Stj48mlXfq64bd8a2f/ew6pLTqGrjTkzZkxV7qnprsEgfD84WWad2Tp7Rq8+NwV0sUvunr8XyNTrlb/XBWZ42iBFQuPf3p8/rRNHrw713+ETL3bPMVT1X6JqqJzP2KZtp87K1fevT6dPiaKpmyikMHs2WPNLzxcKOO0+dOon7/LjPu7w4O1CQGs24m/3RLOfdwH0wOV8uvv3TgNU7lymJO19A5dxdfgmlczFjBupuKRpUddwdU7ZixHXt+O7eLmOsfzw7O2BFasRzd2kTKyZKlHcS99wn/drB2dv58TZPBybfvLpMxR7JzN1TsUWXX4yysk72i7/8kbean72I5JFbz3aP/qqUfjJ9qZ/M5EcFfOvUUVXH3TFli/XgnmXsu7s9abFcfHmnxxjr7z9QTmayHdzNwn0wOU/fzg4/0I+4Cdzz9O4aT2b0zLV3cCZ2Bo7DXird7f3m3rMO5zI1zt2dU1YCd2I2V0p82WJKauj4AlKlsQZx5wHb5l2appc/PPrstvhsxra2P779B/6VTrN1MRv1mXpAWErWyV6+eTq58+EW458Gjx7L5yvLN199LvrUe/+j25/ln/JUQtSyrqoy7u4pWxPuBe/yXi5NL//18FPxfZveKHQL97bVwS41LDgA3D1S+w5QYbhi7H8nAXeP1L4DwL0xdbBLDQsOAHePBAeAu0eCA8DdI8EB4O6R4ABw90hwALh7JDgA3D0SHADuHgkOAHePBAfWiDsEdVNrwf2qY+pglxoWHADuHgkOAHePBAeAu0eCA8DdI8EB4O6R4ABw90hwALh7JDgA3D0SHADuHgkOAHePBAeAu0eCA8DdI8GBRnFPooD453SCKJHqxCFdYLuuFjEWxo6h2n2Qn6E/P+u3fj2JArOubaByv+jG4lBtgz+EMkF+lmhKumTvVgnciRY2Se1G9yQKZGvjkHZaqZZEgQ6Jhoxlrqxd0p54lUSB8ogkClgYmRCtwJ3uhr0xTlpeRLxjcciCKAoNuFkQSD1+B9zz1xS4rwF3BVU7InFIBDSJET1w0nNtnWwHm3l5ECUmRXVwd5eEURSIJyZREMZxKDcgxqoOmT+wuO8dcBd90y3dJLWIuzrzVdiRmNbwdqHrxN3yisgdM3pYG3fypiQKWBgngts4NMjOfyhXRVNJwXtd3DMrgfs6cNfyDm4ylaGb7MgTKueuevZrDNXVFcsSLrWlc1QrmbE2xnG/4txy2hXypL/ly3lT4pa6uBdxA7ivAXcqLOtBS8JMqmtu4fKU0x6jr8rENmJXqbBD9LnsVtW6HzeCdxIFQRgGehwnEVf+znivg7srP9wktYS7Ef6cGYvKTxDFRnTP59mxzSq1ndA2iMReWn3vqkd3W2MFZepfRan2xhexvKgdREkN3NXuAvfrxt3cUNIZi8X2YkKMSo6jmdKnzsUzyJPTEnvCkrgrjVGU5S0oZ63KwqBFenJPvcIBbTaA+/XiTm7XjEmynrHINY1KDtCq4048TG7wWnEnH5ZddIQH/ZBWXftU0Q6Qr9KGHke2gLslAKsvgS1K67tR7Xedc/c4NF8Y66ZN31NUxd3amAt3qkx+TZR0KwjDd/jMdIXofq24u4791Ayd2IqSLKvRqc5XVS1nkc9C6C+s/Kr926nrq6qlMSvu/AuXOS7Bu/5maR+rSjqgPxi4XxPu7amDXWpYcAC4eyQ4ANw9EhwA7h4JDgB3jwQHgLtHggPA3SPBAeDukeAAcPdIcAC4eyQ4ANw9EhwA7h4JDqwRdwjqpoA75JHWgvsvHVMHu9Sw4ABw90hwALh7JDgA3D0SHADuHgkOAHePBAeAu0eCA8DdI8EB4O6R4ABw90hwALh7JDgA3D0SHGgU94vpkPiHN4fTC7pwPLffrpU5iuShupwQjxC94ZqP9UvzsdYAr0L1R3mS1ITev/lYakM8jint0r4xogW3XA6QDW+c2o3uF9NhPv00IUpNuthRpA91RVfGU60P8zFjw+FQuqTjPh+z4XSqvwPWwZjAS73XB3IxHVJvfH0YrQ4oj52PHTHjhqtV3OdjLYTZZlKpWLrIHKq9ULSudYI/fDouZl/DXfw0Yr59MEosV7ovv/sruumsY1fJZKZMR26oWsRdc9UxkwZNpYqIoTr7Mpxe6L0QMM4L3tX28l96N1a9u6KyYkGZVao53DczoWkPd33N1BJUrWQ4nVPpuaOIGqqtSKLIWNeH04tfLqZZQqNwLf3QeHdimeOkr0xZ/mwfQyO4b3A20xru7iSE2L6pMVdMh6OIGqqlNRJx5e+cdxlr+e/yS1Veezik6khbRmIcDeCuZVubpZZwX50eStQZb0Zxt6OIGmqpzpAtX0yHbDw3AnrRtvqQlVhyqt1hnMBu3bjzZjcV9rZwL7G/1EK4Wju/4iiihkq2RB7zFbFcivTKplQ+uTMC8mosS2w6iKVqrbgLIzYzjeFqA/eyezLL/lApdBQRQ13ZUtGe+TbNx2w8Vzan6hjKn6pSHbdUaQ73VevNRqgF3EvshPT8UblFfVscReZQbW1RH63MveTFdDgei9oUd9qRS1Xc52MzLWsqmaE/gG2eGsfdSqSSHJjGy8VaqaNIGypxlUx+sk5qhcVqTy8iBe9EgkR02vXZuMmtKv3VdhPhx38z45HgAHD3SHAAuHskOADcPRIcAO4eCQ4Ad48EB4C7R4IDwN0jwQHg7pHgAHD3SHAAuHskOADcPRIcAO4eCQ6sEXcI6qbWgnvb/w9NXR3sUsOCA8DdI8EB4O6R4ABw90hwALh7JDgA3D0SHADuHgkOAHePBAeAu0eCA8DdI8EB4O6R4ABw90hwALh7JDjQKO5JFBD/FmEQJVR5cfnqKg7lG8JYfaxUKt9EDJW8HofqjbwX8pNEv5SHi2tSX5IoUKuIfundNQaUV1CHqTSpFbnGaZcdd7fBm6N2o3sSBbm58t/iFzmpccj0etkv+01iqOR1PtX5bSbbSRSwMNJg5m0FQYGGjnscsiCKQoMe4vUSNeLQ2v041F+LGsSXmxTV4M1Sq7jL06vDotIvSy6JNZrsd1knO4NZNJ5EQRjHobbmBFGidZC3VNxnjEB0Te+hA+nSuDuH6VDJZKbm02+CWsSdCOeSx/apl7AyJ8ZBjAv3OBHcxqHOaN6cirNoOil4V8vzRyiguknqEO71sqXOqz3cjTVTSip4QrEythPT7pgqJ+5XnFtOuwyX9nKpOY+oy6sqxdITZFJdsb007g533CqF+wbH9hZxp6c23zIZfkubKW17aOx1q+GegZREQRCGgRaRDYblWJ6H7zDWqhp0Fk9T6qgjMreqll1sTRqdk0IbvGFqCXcqhEgbMG33eGXW0xb2/MAirhzdCzLVv4plpmhL4l0qEZm+hLv6LqtV9e5p68DK6F53n2p3gGprQ5FvB3fLrJsnH/a5L5Xj6kMte0dGLnlyylvWDpWUvawZpTN+iNe8Ku7vgGPpc3d3znWD1QbuFMmVNp1Wpp2bLLpL5C3SYQwBpxn3kyiQFhaz50VlepwVDyLdB652VcJ9I8N7C7jTwUnbf9ln1FqyapWvjjsx63nrRpoThGGgJzXUmHnoVxOkirjX3axW+BiykbG9BdwdZqqpg3aETRao96yY/oq4szCk6Mv6Tx2ckgVSse3zqZKlEInTlRlvayU01klxGLxZwn8z45HgAHD3SHAAuHskOADcPRIcAO4eCQ4Ad48EB4C7R4IDwN0jwQHg7pHgAHD3SHAAuHskOADcPRIcWCPuENRNAXfII5XH/f80NqCpAMKaZwAAAABJRU5ErkJggg==" alt="" width="157" height="95" />

8、数据分组并排序并筛选选出最近一批的数据

Group BY XXXX

HAVING

SUM(filteraaa) > 3

QUALIFY RANK () OVER(PARTITION BY aa_ID ORDER BY MONTH_ID) =1;

Qualify rank 优化。报表执行顺序，确保每一步过滤掉足够多的信息

http://blog.sina.com.cn/s/blog_4d281a0301016jw2.html

http://community.teradata.com/t5/Database/qualify-rank-over-partition-question/td-p/47965

http://blog.sina.com.cn/s/blog_62d120530101h7vi.html

关于QUALIFY RANK() over组合应用

查阅搜索引擎发现，qualify rank() over用法似乎是teradata独特的用法之一。

类似于之前查阅的row_number() over….只不过这里qualify …可以直接=1或=2获得首位排序账户

sql 语句执行顺序

http://www.cnblogs.com/summer_adai/archive/2011/10/28/2227605.html

补充

show select * from TABLEAAA

可以批量看有哪些字段

秒客网

TERADATA SQL学习随笔<一>

相关文章