SQL学习之汇总数据之聚集函数

时间:2023-03-08 21:04:28
SQL学习之汇总数据之聚集函数

一、

1、我们经常需要汇总数据而不用把他们实际检索出来,为此SQL提供了专门的函数,以便于分析数据和报表生成,这些函数的功能有:

(1)确定表中行数(或者满足单个条件或多个条件或包含某个特定值的行数)。

(2)获得表中某些行的和

(3)找出表列(或所有行或某些特定的行)的最大值、最小值、平均值。

上述功能都需要汇总表中的数据,而不需要实际数据本身。因此返回实际表数据纯属浪费时间和处理资源(更不用说带宽了)。

2、下面是SQL提供的5个常用的聚集函数

(1)AVG()      ---返回某列的平均值

(2)COUNT()      ---返回某列的行数

(3)MAX()      ---返回某列的最大值

(4)MIN()      ---返回某列的最小值

(5)SUM()      ---范回某列之和

如下代码:

select * from dbo.tb_order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXoAAADpCAIAAAClR2ueAAAgAElEQVR4nO1dy47juJLVT01Bnf8yi2sn6kNm104XkJv7C7OZmSpnD+DfaNx81MbA3PdjFoNb3V357CzELOyUKZIRCoYoMTIZB0IjywqKJ0jqmKLF083369/CK8T369/+qBi//PLL9+vffjEYDA6a1ys3dthhx+s6XqvcGAyGV4ee3PzHf/23HXbYYcdEhy83k2rbzz//POn1xZiZmNp2SILmLDRzqxkmNwAmNyJozkIzt5oxq9z89NNPk15fjJmJqW2HJGjOQjO3mmFyA2ByI4LmLDRzqxkK5Gb7vjng5HyHF96dn5Dn4zGcUq9Fbni5zAbNt/QcLaysO14FZpWbL1+++B9t3zfN++3+7935CaE4U8pNhBi/inQkVJe76owVCbPg177/Q5T1HC2MBZsM4ZhVbv75z3/2Pwh6Zvu+Ex8fU8pNQGxaCKtTJjdTNZpXuyjrOVrY5CYdvtz8+uvz/cPTFMfz87e//+N/e9e/+dA2pxs3bP/JzYe2PV22TbP/++VZq2k/XB9i9vju7Obp3gv2Y15KpRI7XLNp9gzdTw4X/GH5UsPyoiPvEONX1yvrVZfYAhSr7sovDbL/++ZD2344W3alPp/10pRl0TQvV+7V9fC06fjtGRK199v8bNk0yx/2FW2WTbv+XKaFg+B+Rv3W4w2Jeg5fbp5+fb67f5zieH7+9te//b13/et1266vemEXi+Z0c71um3Z1/Xh3/3nVNovN4939493mtGnXV/efV+3+1EvxY3B3wbBUOrGXa16dtc3y4vhJWMX9xaJpV9cBMX51ztX86tJaIMoquHLH7Vj8pVT3iaDRXGJ7qmFd7kUGaw//2ZxuukGSzC13C4dDy2897pCo55hPbr59+/aXv/4tuKv742b/iTfgvL5sXLSrTSyGGOVMYu4VQkrRm8cjdi2tLrzlmC2A3dJ049CfiBsN6wiXP117cJHNsllsHu82p83yIhs3cQsPZsQeEvUcgdw8/Xp79zDF8fzt25//8rf+9W9Wbbu6csI2y2ZxcXu1btv15d3D8Y/u76t12yw/uVeOxnifpBIjrhlW0YV5xDJWx2yBKKvBxqE/yZLF8ezFonnp8cHaw4DNsllcfFr0x0ypFh7MiD0k6jnKys3D7WbZdF1ytW73PXfs1JtV2yw2D7d3D5ertmnXl84nh+6MjICwVCIxZ6BcrtqYAjo0DgMuIJbQDn3CbnVpLUCxOlz57mLRcdssm4xy4954+yuHdSXVHiFzsWgaSYdO0MIvwURG3CFRz+HLzePTr19v76c4np+//fFPf4lc/1O31NZ+f3n/9fb+6+XZu/bs8vbl78PJ9uXDzcKN94L3f0dKpRC7PHvXti9XeLmg98eRRrP4tC/YJ5bUDkfCXi2JLRCy8q58e3+5epnj7y8YacD9RZYfU3uz68oXqn5d7id7SkTtMTIfF8271bVkpOVq4SDYz6hHmDUk6jl8uXl4fPrl690Ux/Pztz/88c/TXT8nscvVu3er31XWDjmzmKoBN79pFh/raOE3eQRy8/D08y+3UxzPz8+//8Ofprt+TmK/W717t/qxsnbImcUUDfhx0TTNu+8vK2nhN3n4cnP/8PjTz1+nOH59fv6f3/9xuuu/FmJq2+HNZKGZW+WHLzc3BoPBMA3Ct4onxNPT06TXF2NmYmrbIQmas9DMrWb4cjO5FXvTqP2vwWCYFBG5Ye62EuDLly//+L+fFR6TZh1thzmrmwias9DMrWbMLTdN0xQXl/Bomma6rKPtMGd1E0FzFpq51Qyb3djsRgjNWWjmVjNsdmOzGyE0Z6GZW83gyw3hfMXF0OzmP/+1+c2/a5vddN6mYfbEqaF2IM/3mvrorTq2+TODyALnfDwz4BU7IzeErdsLFG1G8WzBxGAoG8wcpRy52Z2f5Bnv+Ozmx3/7l30VZeQGn90czU2375vAeRA7NdwOyJmgqY/WcOpM4tAsKM4ZvrTGcNudn7zU//JnhG044HHarOKZgomGLRvMHqU2uyFnN4SQpGpMvx3I805Tx/9UAarRMM5zaSbnYerABWXr/IugzSmeK5ho2LLB7FEakRvEP/SHpWf0mX4Mrd0UkxtsduN8GSacGgRfbtxxrm16Q8wgUM7dd/mkj1IsuTl0IM62LzcIbVbxTMGx4oeCEwQn0OCP0ojcIIZ+A46NnOPVzW525ycn799HZ7vEqUHUKzfHKeEYuZZzc0k23nMAIQE47dJyoyJYr9y8vtkNPs7Ed069coOEzcatq7qbpSRJQBdzWBl9v+UUTwr2KnL/fqNyEzf0c1zLpMerm93A9j3xRS0WgnrXbhyUkpud938zS1pPic7XbO1mzNrNpHLzumY3TuuFUxji1AAS5Ia95j8/BL9MOS01YqV9DDf3f6PYcYqz7c0xUdqM4tmCicFQNpg9SiNyg/iHbhakmyTneH2zGwhfrukr+cRrN24luuY2ae+2BE8WhZaK3fdnOg6cd2EI2kXfu4k0bIlg7iiNyM10rov2VvEeb+OdV81ZaOZWM2JyM5nrou2Z2uNt3Ayas9DMrWZE5GY610Wb3ezxNm4GzVlo5lYzInIznXWgzW72eBs3g+YsNHOrGdG3iqeCzW72eBs3g+YsNHOrGXObh34p7RCK/ddgMEyNuf1uprv4GMxMTG07JEFzFpq51QyTGwCTGxE0Z6GZW80wuQEwuRFBcxaaudUMkxsAkxsRNGehmVvNYMlNrvfoiUFQ9lV9DjHqJXPbxNAHztndRVBiz1TCC/huL1C0i25i0BIs38TgRexCv0UpsEGQsQoZ0NF53JEXDojjbr/U7Yb4zbAz89BJuUVGWoRt0AsEbVbxTMFvcosmGjt6yHOmuEXuKgaxgNeILc0Js5s3ZkAxV+8mjDSOKQRB2wwoRhpQIOahT/cPn8/aZnkxyjwUJXKAstlNh4DXGKJ8uXlr9lo7Mw/Na6+1favmoY9XZ23Trq/GuflhPDqu0w5DBINzunCyuzs381AzDy0uNyqC88vNeK0ZlJtSWgMpX4a9f0vvnHrlBgmbjVtXtZmHZgweJzeBm9/lqm3a9eU4K7+9mx/Go6DWgGxRycxDzTw0tbit3QzLzWbZjLYNHZCb4488ZSD6yaxr2AlnN/w1//kh+GVqR7hwzsMtHGko294cE6XNKJ4tmBgMZYPZo3TYPPTjonHxbnU9xjw0SsJ9q2HqRcQoOK9pvLDqK/nEazduJbrmNmYeGl4TL54rmNOwJYJHvHczqXkoQaUg7K1iATRnoZlbzZjbPLRgqgRMbgTQnIVmbjVjbvPQgqkSMLkRQHMWmrnVjLnNQwumSsDkRgDNWWjmVjPmNg8tmCoBkxsBNGehmVvNKGAeajAY6oT53QDY7EYEzVlo5lYzTG4ATG5E0JyFZm41w+QGwORGBM1ZaOZWM0xuAExuRNCchWZuNSPNPHTka/TEIMhVhQxCYraJAQHO2d1FUGLPVMIL+MEmSqQXwuK7F3ucwR0Pu6ORTpzBMLHg+v1KucHEWEZO9feU4Vm4YMjNljDQTAM6CPJVIYOI2HG3X+p2Q/xm2HlWkuQe0cJAs6A27M3Uvxi3SHtG2Pq9QPV1dL8iHkv15u78xPkoFhwSC9NzrTXYwcQ4j5wir9zPwkPSw9TYTcmMKW6Zfc8SYiO2NCfNbnAGhUFpNGZHMFcOnIepAxeOKQTR12FxJ8ck/w3H0YQOxq662zu+8WY30eAoL+wUtq00cUc4ah5686FtTjdS51CWeWihr3ABsTFMRXLzamY3lNmSM+0usiPcwY5vHko0fax49xmtNnwh9oLdy/pqF/tW5AcPpdo7RZtmxJHi5tc0i83Ebn761m4AIbabxTy0x0HV3KYm81Cir+PFD6pK6kfgKkMpE+boFXxIys1AMHEDxk7FRyndpwlexXf3n1ftKMXhf+fMDAExZxBMaa/l16UIErlBwmbj1lWdZB4a9vWW8AMNVnO64LD2sC6CKhHsLjsNyg0eDORgJiZZA1l0YJmHui6i7epmCvPQsC3mhITYPOahWrUGqjIPJfo6KE6rbbQ3saZAuj5sVP+nIXKhhwimyASnJOtyw3JzuWqbxcXt3cPt3c2qbRab/OahxX9/ERGbxTw0NLtUA8EvU05LvSbzUKqvw+LEA2O8N5H7FO16ahLBe5jyg4lxjp/yrsz6+hg2D3X9Q8c4hxLmoTCXpySGFGKeFky7dhM1u1SClHdbIhm9LvPQpPdusNeO4r2JCALe9cjqLwAMyA0VTIxzpMvQuSABMw8FsLeKRdCchWZuNcPMQwFMbkTQnIVmbjXDzEMBTG5E0JyFZm41w8xDAUxuRNCchWZuNcPMQwFMbkTQnIVmbjXDzEMNBsNMML8bAJvdiKA5C83caobJDYDJjQias9DMrWaY3ACY3IigOQvN3GqGyQ2AyY0ImrPQzK1mJMjN+F0ug4Ng2o00OOis0Y0K029i6L/LrmoPQ1XmoRTtJIvPkcF4wxYOJoq7YMtNz5dQiIHbLEcVMqDECFNFZwtdqkri7RDaMvL2opQANVTeoHnooL8WUTxTMNGwZYOpHu+BKTc7xGowDaTc5KlCBsbcO7b3Tco1YXZTxI+DB0qjMQOKudLhPEwd3WgGDSgI2pziuYKJhi0bzLMcAa556MVps/zhev1du/4sdg4dMA/dIm6Gs2B4dMaMB8Tf02lyo/NRqibzUII2q3im4FjxbejLlyk4gQZt8eOC4+Z3sWhON/ePV2dte/Z5GvPQSEPMCXp07gqahxLmKaUhkZu50hmUm865iiUBOO3ScqMiOKfcdCozndy4D7IK5QYA4rZD0jsnZam4V6OqCY5EbpCw2bh1VSeZh3plT853W8I8NCieFOxV5P79RuWm5+Z3s2obFxOYhzpT1UJPDQmP+h3mMg9FCZSGZO3GQSm5cbUGIHE9JTpfs7WbMWs3mFfxSKNijlexttnNDjdVdBp2wtmNc+lSLwmgEPwyNVs6lBR6fYWy5fUCo3i2YLxhCwcTxftgmYfuj8tVO515qJ/avOC8pvFCrK/kU6/dRAhoQcq7LcGTxWszDyVoF33vJtKwJYJHvHdj5qFvrLqJoDkLzdxqhpmHApjciKA5C83caoaZhwKY3IigOQvN3GqGmYcCmNyIoDkLzdxqhpmHApjciKA5C83caoaZhxoMhplgfjcANrsRQXMWmrnVDJMbAJMbETRnoZlbzTC5ATC5EUFzFpq51QyTGwCTGxE0Z6GZW83gyI375veoV8/xQZCtChmGRmf8ffbI+9q8bQ1J1XUbWF/zJobImVdlHkr1bFic6DIv2N+dHDAYJhZcv18pN5gYucip/p4yPAsXTLnJ40xCyk1JLxec2M73dqR2ox33/9EbENOqY12yANAsBppojo7GuEX23EbYBr1A9Gx0vyIei+/4BdidnzgfxYJDYmF6rrUGO3iLm+RGTpFX7mfhgSE3+fZNMgZoGSRMN+it+rwsEqpzWqawJAdAsyCaaK6O5jxMHbhwTCGIng2L87ospsRo03CMePZRgf1uUnCUF3YK21aauCPcNw+9+dA53kxlHurMxrTtCAcAb95IGBEx9SDlYaqrRJvaVGQeSvRsrDiny4JLUkLsBbuX9dUuuExS8FCqvVO0aUYcDPPQzWnTrq/uH+/uP6/aZrGZwDx0W9giM5fcMO1Ek9ZuXm5RXU9SUJN5KNGz8eJDXeZbfJGPIEFwVMXCqVVy8C5mkoufilyZfJAC4HkVR4xEc3sV9xjrc/Njz25495LoYar0A2cAidwgYbNx66pOMg8Ne3ZL+IEGXdYFh7WHdRFUiWB32WlQbvBgIIcuMckayKJDgpvfeEO/NyA39NoN5zZLqo556xaBZO3GQSm58W9g3toNNV/rF6e7LCYfaFNEg2ON6v80RC70EMEUmeCUZF1uWG4uV22zuLi9e7i9u1g07eoqv3moI5tlfn/JNN3oLd3nmd2UfswkIPhlaraOpqQQfRwilkLxng2LE10W1h6plw4GehLBe5jyg2O/gh1jkFPelVlfHyzz0I+LgxBOZx7a/bavfKkYSJvFKd67Ydoyzo+Ud1siTpRFlord136aFPPQpPdusC6L1o4JQjzYLzA4xeAGB/0y2GXoXJCAmYcC2FvFImjOQjO3mmHmoQAmNyJozkIzt5ph5qEAJjciaM5CM7eaYeahACY3ImjOQjO3mmHmoQAmNyJozkIzt5ph5qEGg2EmmN8NgM1uRNCchWZuNcPkBsDkRgTNWWjmVjNMbgBMbkTQnIVmbjXD5AbA5EYEzVlo5lYzeHLDezd/ENQgyFSFDEOjk72rYIJNDN2OOlX7M6Eu81CKNmsPRKZgYuyVDWZuteF6Fe8vMnJfHT4IslUhA06su9mZWzRZWaRVV7JhKKBZsHaxluEW2XAYYRv0AkGbVTxTMNGwZYPZNikMuck3ztEBWvpWSphukAYUZh4KQDbRXCYanIepoxvNoAEFQZtTPFcwPfYKBvMsR4BjHnq9/q5Z/jDGM3TQPLS4twL//h+w1+JlkfIw1VWiTW0qMg8laLOKZwqOFd+GvnyZghNo8F2Zht38rs7adnl6sCteXoit/Ag3vx3bdnMi5JIbMw8FWm7msu8ZlJvOuYolATjt0nKjIjiz3EztVbw759puToRssxteFqKHqbmeQ9iQyA0SNhu3ruok81Cv7Mn5bkuYhwbFk4K9ity/36jceOahm2VnGDqVeSjbdnMi5Fq7YWaRRd2KQ7J246CU3LhaA5C4nhKdr9nazZi1m8Cr+GLRLD/dPdze3azaZrHJbx7qkNQ+uyGnG9ws0tSt6LyPgOCXKSeHaX8doKTQa0WUbU/0UdqM4tmCibFXNpg9B2eZh379tDwslC02E5mHHn+41712A5FXDPrqkHvthvlGw/xIebcleLJ4beahBG1O8VzBnIYtEcwdpWYeCmBvFYugOQvN3GqGmYcCmNyIoDkLzdxqhpmHApjciKA5C83caoaZhwKY3IigOQvN3GqGmYcCmNyIoDkLzdxqhpmHGgyGmWB+NwA2uxFBcxaaudUMkxsAkxsRNGehmVvNMLkBMLkRQXMWmrnVDJMbAJMbETRnoZlbzRiWm93R62Psu/TYIMhYRV5iL2DvKphgE8POzEMn5sbaWID3bFic6DIvmB75STseunP9SrnBxMhFTvX3lPHu38TZze78ZIQUsL5zxlUhA05s53s7DmzRPMTRGxDTqmNdsgDQLFi7WMtwczZbvvwZYRv0AtGz0f2KeGxk16d72rM58oNDYmF6rrUGO/jINOigyCnyyuT9myY3jsWCBBy5GVmFDAnTDXqrPo97QnXODTvTncoGmgXRRHOZaHBG2oELxxSC6NmwOK/LYkqMNg3HiGcf9f79CW92Ew2O8sJOYdtKE3eEe+ahx+PmQ9t+uJ7APJSV7JTg3/8D9lo8PUh5mOoq0aY2FZmHEj0bK87psuCS1Mj3gt3L+moXXCYpeCjV3inaNCOOYTc/19ZvjJUf4ebnsi1yS+WSGzMPBVpu5rLv4Yw0vnko0bPx4kNd5lt8kSM/CI6qWDi1Sg7es8bmQsTaDSeLPfhyc7FoTjcjtIYhN8W+wLPNbnj3kuhhqtDED4dEbpCw2bh1VSeZh4Y9uyX8QIMu64LD2sO6CKpEsLvsNCg3eDCQQ5eYZA1k0YHj5pfBNnTIza/XCvMj19oN5zZLqo556xaBZO3GQSm58W9g3toNNV/rF6e7LCYfaFNEg2ON6v80RC70EMEUmeCUZF2OKTc3q7ZdXY3SmiG5KXk3ZZpu9Jbu88xu5nr6EEDwy5STw7Q/tFFSiD4OEUuheM+GxYkuC2uP1EsHAz2J4D1M+cHET2b4Ke/KrPuXbR46zjaUZR5a7mYa995NXx1yr90wbRnnR8q7LREnyiJLxe5rP02KeWjSezdYl0Vrx0Z+PNgvMDjF4AYH/TLYZehckICZhwLYW8UiaM5CM7eaYeahACY3ImjOQjO3mmHmoQAmNyJozkIzt5ph5qEAJjciaM5CM7eaYeahACY3ImjOQjO3mmHmoQaDYSaY3w2AzW5E0JyFZm41w+QGwORGBM1ZaOZWM0xuAExuRNCchWZuNcPkBsDkRgTNWWjmVjNYctNt6hr54jkxCHJVIcPQ6GTvKphgE0PZliFAZIE3kftqfok9Uwl7DuSbGIgu84L97ZLhFqtBYsH1+XumthrNQ487s8Zuq0MHQb4qZMBHZ9eSzC2arDTSqivaMgSo3hzexVqGW2TDYYRt0AtEz4bF8S4jdkIePvNsjvzgkFiY3ms3D3XGzMjBwhigZbZqJkw3SAMKMw8FIJtorm3/nIepoxsNw4DCzEPJHfOsLIBnHvr5rP3u7Obp/uGHZXO6mcQ8tMukzD3Fv/8H7LV43FMepgq3DAFiBoEavjjT7iI7wh3szDw0UiffXSua3dDXCc/N73rdNk3TtKvrydz8diUtMnPJjZmHAi03c9n3DMpN51zFlBszDyXWbjhZ7MGQm+t1266v7h/v7j+v2lGKI3ranwPZZje8e0n0MFXSfiwKidwgYbNx66o289BYMJBDl7bXorLoMOzm59qGjrQQHTlAp0OutRtmFlnUrTgkazcOSsmNfwPz1m6o+Vq/ON1lMflAmyIaHGtU/6chcqGHCKbIBKck63IM89DNsmnXlwcL0WaxmcA8dK45NoZM043e0n2e2U3pliEgmKs6OUz7Qxs10tDHIWIpFO/ZsDjRZWHtkXrpYKAnEbyHKT/YIepzxk95V2Z9fbDMQz8uXpRwnIWo6E2NOTDuvZu+OuReuynbMgRSenPQiXImbtuYI2f2926wLovWjglCPNgvMDjF4AYH/TLYZehckICZhwLYW8UiaM5CM7eaYeahACY3ImjOQjO3mmHmoQAmNyJozkIzt5ph5qEAJjciaM5CM7eaYeahACY3ImjOQjO3mmHmoQaDYSaY3w2AzW5E0JyFZm41w+QGwORGBM1ZaOZWM0xuAExuRNCchWZuNcPkBsDkRgTNWWjmVjNYcsN7NX8YxCDIVYUMQ6OTvatggk0M3Y46VfszoS7zUIo2aw9EpmBi7JUNZm61YcjNFjcWTAQ6CPJVIQM+OrubnblF8xBHb0BMq451yQJAs2DtYi3DLbLhMMI26AWCNqt4pmCiYcsGs21ShuXG21A/ZrAQgyBXFTIkTDdIAwozDwUgm2guEw3Ow9TRjWbQgIKgzSmeK5geewWDeZYjwDIPvTht2g/XBxfRpl1/zm8e2t+zr9nvZsBei6cHKQ9TXSXa1KYi81CCNqt4puBY8W3oy5cpOIEG35WJZR66We6bul0s2/bs8xTmoS/PflG75smRS27MPBRouZnLvmdQbrqvNZYE4LRLy42K4Mxy4+rORHLTocgSRbbZDe9eEj1MzfUcwoZEbpCw2bh1VSeZh3plT853W8I8NCieFOxV5P79RuXGc/M7HpO5+fUaucAzQ661G2ajZ1G34pCs3TgoJTf+43rSekp0vmZrN2PWbkKv4mZxcXARbZafpFpDexUff37R9cvUHszpRheWb3Yz19OHAIJfppwcpp3FUlLotSLKtif6KG1G8WzBxNgrG8yeg6eZhy4+yZ1DmeahRe6oce/d9NUh99oN842G+ZHybkvwZPHazEMJ2pziuYI5DVsimDtKzTwUwN4qFkFzFpq51QwzDwUwuRFBcxaaudUMMw8FMLkRQXMWmrnVDDMPBTC5EUFzFpq51QwzDwUwuRFBcxaaudUMMw81GAwzwfxuAGx2I4LmLDRzqxkmNwAmNyJozkIzt5phcgNgciOC5iw0c6sZJjcAJjciaM5CM7eawZebDG/WDw2CYi/vZyM2wSaGnZmHTsyNtbEA79mwONFlXvDuaKQTZzBMLLh+v1JuMDFykVP9PWV4Fi44crNLMdCkgA+CbFXIkInYcf8fvQExrTrWJQsAzYK1i7UMN2ez5cufEbZBLxA9G92viMdGdn26pz2bIz84JBam51prsIO3uHtv5BR55R1lxyma3bD3m3tI+FaXViFDHmJsQUiozrlhZ7pT2UCzIJpoLhMNzsPUgQvHFILo2bA4r8tiSow2DceIZx8VuNMlBUd5YaewbaWJO8J989DD8cOyOd08PN0/PF2vv+ssRN2/5eahQQIz+7xkIcZ3iEh5mOoq0aY2FZmHEj0bK87psuCS1Cj3gt3L+moXXCYpeCjV3inaNCMOvpvfxaI53dw/3t0/Xp0dLUTdv0e7+b16uTHzUKD7bi77nkG56Uy2mHKD9Wy8+FCX+RZf5CNIEBxVsXBqlRy8Z43NhYi1G04We5jcAJh5qAgSuUHCZuPWVZ1kHhr27JbwAw26rAsOaw/rIqgSwe6y06Dc4MFADl1ikjWQRQe+eejForPy2ywP/n53D58WCXaib37thimRamU3CZK1Gwel5Ma/gXlrN9R8rV+c7rKYfKBNEQ2ONarzjOo/qCYFU2SCU5J1OZHcXK3bdn15cC9uV1cTyM283+qZiPWW7vPMbuZ6+hBA8MuUk8O0P7RRUog+DhFLoXjPhsWJLgtrj9RLBwP9/ct7mPKDY7+CHWOQU96VWfcpyzz06+3919vNoll+DOxEm8Umi3lomMDree+mrw65127mbIckpLzbcsyoO1Vkqdh97adJMQ8lejYsjnVZtHZMEOLBfoHBKQY3OOiXwS5D54IEzDwUwN4qFkFzFpq51QwzDwUwuRFBcxaaudUMMw8FMLkRQXMWmrnVDDMPBTC5EUFzFpq51QwzDwUwuRFBcxaaudUMMw81GAwzwfxuAGx2I4LmLDRzqxkmNwAmNyJozkIzt5phcgNgciOC5iw0c6sZJjcAJjciaM5CM7eaITQPRT4ZwNAgyFCFDEnEqF0FE2xi6HbUqdqfCWYeil+Q6DIv2N8uGW6xGiQWXJ+/Z2r7OsxDI5+wgA+CbFXIkEBsYIvmIY7egJhWHeuSBYBmwZjVoVQAAAHMSURBVNrFWoZbZMNhhG049vCeDYvjXUbshDx85tkc+cFDN8XuTZqHop8M4C3MbkgDCjMPBSCbaC4TDc7D1NGNhmFAYeah5I55VhYgMw/FP8lmHkqnlB18YgP2WjyyKe3QVaJNbcw81C/iTG8Gu4wwshgMRjZ5b9+oeSj+STY3PyqlCZBLbsw8FGi5mcu+Z1BuOucqptyYeSixdsPJYg+TGwAzDxVBIjdI2GzcuqrNPDQWDOTQpe21qCw6iNz80E/yufmxE8iCXGs3nNssqTrmrVsEkrUbB6Xkxr+BeWs31HytX5zusph8oE0RDY41qv/TELnQQwRTZIJTknU5kxsAMw8VQfDLlJPDtD+0UVKIPg4RS6F4z4bFiS4La4/USwcDfVPwHqb8YOInM/yUd2XW14fQPBT5JKd56GBjZcS492766pB77YZ6zacoUt5tOWa0jTtRzsRtG3PkzP7eDdZl0dqxMR4P9gsMTjG4wUG/DHYZOhckYOahAPZWsQias9DMrWaYeSiAyY0ImrPQzK1mmHkogMmNCJqz0MytZph5KIDJjQias9DMrWaYeSiAyY0ImrPQzK1mmHmowWCYCeZ3A2CzGxE0Z6GZW834f+DqeKUvCHvcAAAAAElFTkSuQmCC" alt="" />

现在有个需求,需要求出所有订单的单价和,下面是解决代码:

select SUM(price) from dbo.tb_order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAGMAAAAmCAIAAABfzLIdAAADFUlEQVRoge2Y3U7iQBTHz0MpIb6LMbTGBzEh8ePGpzAhy2pxE5+BCxOyH+6NySqKyl5sLFAKtNPUi9JSZuYMg+VUYDmZNDNnTs+c/vxb64Ewti9fv4WU1ul0SPMv3LiCIZmtNCkAmOmZ15aN1PUBHFyHIeA2LhTxJ7tcXoTU+LgPFJwfKdu2Refd2Y5YuEIOGCDRj5LVZsUVnB+pt7c3wSevOiMp6e0pp/SnM7vgKVK+zwZDj2IwFry2//L5a/tgXonBAIDlSWskHclNuAzc0jLBrGkVjJLyfOYORhSDseD55ZXL//O0WDz97Q5GipdUZFF8Mknm0i0uQNxKzp1Z8CeQCoKg9fyCkZI+odqvQ0p06pOKCsZJeX7fHVIMFgRPrRcu/4+TYvHklosEgOQqHemAJCwdr9Cm4lxpwctCqm+ZYNRECmpS0rAEnAJ04rw0wLC0CkZJjTzf6Q8oBmNB87El5LcMMC/iJQA4s+bicv6YX8fFyaHqglFSw5HXc1yKwVjw0HwS838/LpYu3J7jAkDan15icyw+Wkqt57i9CwMMS7NgnNTQ6/b6FIMxdv/wKMt/WQIDAMRb0k+YeBRhnFMaGR1X1S4YJTUYjjpdh2L4jP25b9LlJyoYJXW7sWlDSfmU5nkeaf6Fm+d5KCmb2ABgha7htPGkQjKzbbv9r7sqA8Q2Tp6kAODTEWiOjaY2mloaTc3RVFWQUhZX3YXSebw834s/KPeqmKeNB3+Kpu7OdqIqFkAK0VTjcDs6IiZ1c7S1fVQfbxUOb7r1ciGm0Djcht3K5HbF1n+gqUopLSXu4evlwla5IU2i2Fq8ppAG8ZUJ+1a2BvGs99SEVPqBhYdXCCdfTSFtz5oB+1a2tqe+phSk6uUCjH8xJYLCtkg0RUoqo6Zyw6SnKXnbs2aAeZmt7ZnxPZUnJj1NUZLS1JT4t69dKUHqG2JqKLZINYU0iKfauB8bWb6nJksAANgqN5J42VYumqJrEK/bNzpdg3jd/u+ja+Cum6boOq3rpim6Zuu6aYralqHtq3lVkQo3htuGlK69A/7NtczuoryAAAAAAElFTkSuQmCC" alt="" />

ok,完成需求,拿到所有的单价之和!

3、下面是当我们使用上面5个聚集函数需要注意的地方

(1)MAX()和MIN()不仅可以找出最大/最小 的数值和日期值,许多DBMS(不是所有)允许将它们用来返回任意列(这里只的是任意数据类型)的最大/最小 值,包括返回文本的最大/最小值,在用于文本数据时,MAX()/MIN()返回目标列排序后的最后一行/最前面的行。

(2)MAX()和MIN()函数忽略列值为null的行

(3)当我们使用上面的聚集函数计算表中的列时,需要去掉一些重复的数据是可以使用DISTINCT,代码如下:

select * from dbo.tb_order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXEAAAD2CAIAAACvJyAnAAAgAElEQVR4nO1dS5IjuZHFebTTpiw6b9A30HbItNIhtNNOzCyzPEDrClXMWlAb3WFM+dvQbPRrSTMrsVuV38qymAXJIAKAOxwgEPAE/BlNls1whD+HI7wQIPCk+pz4+eefs94/GhMTY9sPQeAcBWdurUFlvTvbTEtNiQDnKDhzaw15a8pPP/2U9f7RmJgY234IAucoOHNrDVJTKnSXCZyj4MytNZSoKav3aoeTizXceH1xgl5321BavZWaQotlMnB+bqfoYWbpYIu8NWWz2Zhfrd4r9X61/Xt9cYKUlZw1xUGM7iIcAe5Su07oKDIKuvftH1FRT9HDkLHUmjHy1pR///vf4y+s7l+9HyqMiZw1xSKWF5HumNWUXJ1meI+KeooelppCg/r69fXx6SXH5/X127/+9/9G97/90KnTpW62/eb2Q9edzjultn/vX41U9+FmZ7PFd2e3L4+GsWmzbxVKbHdPpbYM9W92N/w833uYXw7kNWJ0d6O2hrvAHsBYDXfed8j279sPXffhbD60ujsbhRkXhVL7O498Pb0sB35bhoj3cZ+fzZWaf946Ws5Vd35Xpoct43FE496jDYm6P+rl6+vD43OOz+vrt3/881+j+9+cd9359cjscqZOlzfnneoWN88Pj3eLTs2Wzw+Pzw/LU9WdXz/eLbrtpX3zg/FwQ7tVOLH9Pa/POjW/PHxju3i8nKlucWMRo7vT7ma6C+sBJyvrzgO3Q/N9q+GbiE7TiW2p2r70m3i92/+pTpfDIAnmlrqH7aFl9h51SNT9yVhTvn379uM//mk9uuPBsf3GGFVGwpSObrF02SBDmUhMv4NNyfmEGMRuYt3ZzxWxB6DnFu8c/JvoToMSofPHvVs3Wc7VbPn8sDxV88tk3KJ72BsReUjU/VEvL1/vH55yfF6/ffv7j/8c3/920XWLa81sOVezy/vr8647v3p4Ovwx/H193qn5J/3OThvjm1BiyD1tF4OZQSyhO2IPOFl5Owf/JkkUh6uXM7XPuNe7bbCcq9nlp9l4zJTqYW9E5CFR92fimvJ0v5yrod+vz7tteg6Zu110arZ8un94ulp0qju/0r7Z5cyRZrtVIDFtNFwtOleZ02jsRpVFLKAfxoR1d2E9gLHa3fnhcjZwW85VwpqiP13bO9u+grw7yFzOlIpJaIYe3hsjEVGHRN0f9fzy9cv9Y47P6+u3v/7tR8f9Pw1rXN3vrh6/3D9+uTp7151d3e//3l3s9l8uZ7q9Ybz929EqhNjV2buu299hf0PjjwMNNfu0bTgmFtQPB8KGl8AesFkZd75/vFrsp+TbGzo6cHuT+cfQbA6p3FM1fenfbCkh3l1kPs7Uu8VNzEhL1cOWsRnRiDBpSNT9UU/PL//58pDj8/r67S9//Xu++6ckdrV4927xp8b6IWUUuTpw+V9q9rGNHq7mo56eXn7+z32Oz+vr65//8rd8909J7E+Ld+8W/91YP6SMIkcHfpwppd797qqRHq7mox6fnn/6+UuOz9fX1//581/z3f+tEGPbD9VEwZlbgx91KxAIBOmgvubEy8tL1vtHY2JibPshCJyj4MytNaiNQCAQpIPaZDps2vd9vpOsR2NiYmz7IQico+DMrTVITanQXSZwjoIzt9YgNaVCd5nAOQrO3FqD1JQK3WUC5yg4c2sNSE1B5JKo8GU6gYs4YMQGaUubGnIp2t3uvoc7HqQ1y/QNCCQKmPPhikcqdEJuAFs9CxhtQvNkxshgKGuMNHfWlPXFSZpBDWc6mYs4oJV0x2n1XlmadNClaHdWPxxEw9jJh4FRYJwn+mcD4ra+ONn73//pYGuPRpg2qXkiY6Rjyxqjo1TmKWMg1SK0kFDcHW6t1X/HnyyAdRrEearCSHn32XEB2Wr/hdCmNE9ljHRsWWN0lKrNZgPIR36eGzqP4Z83V1O0f9YCLkW72+PQD/pg5jZRQToN5Dz8q5z1zYdUU3YJhNmOawpAm9Q8kbGr+a5hBuMAGvgoVZvNBpB68wj2UT5vsaacvH/vnJwil6Ld7VFvTTlM7o6pyfHcdJLKmLYjzzlMu3RNYWEsNcUBbJ4CD6box6PdmgKYTcZtcD3MN4Ke88FmtyT5fkVpHmRsONL/fss1xS31pulZxX7eXE3pV++Rf3Kjn/Z211M0lKopa+P/RSpojcM585L1FO96itQUDQMlezKCXIp2Z9y5tt99tJ46Yon7GG76/0fdwMnNdjRbBGkTmiczRgZDWWN0lKrNZgPIRy5nqJgg5fMGa4r2y/thVFk/ymdbT9Gd8JqlhO0BsV4ECq3RrrR9JgMHyp4RhDaleSpjSseWMMZGqdpsNvlE99jubpR9tBHgHAVnbq1BbTabfKJ7bDMtNSUCnKPgzK01qM1mk090j22mpaZEgHMUnLm1BrXZbPKpyLHNtNSUCHCOgjO31rDdR5sLbDMtNSUCnKPgzK01iHakQCBICdFPqdBdJnCOgjO31iA1pUJ3mcA5Cs7cWoPUlArdZQLnKDhzaw1SUyp0lwmco+DMrTW4a0qq7eFIpsvuQKcQw/ZOy978MWDO+ub4Eud9AvaV61nAaBfdm8/F2LM33/hqbcvtxQLKdEIXcQCH4OHImJ31w3G00PNw8Ihfi3ZkVm6OkeZga2UBoU1qnsj47Z4hdHadef8oUGakRR4dAjGL1xEHawPmKZVpHUyV3YCRRtEfQGiL1gFF6wDQjnx5fLo769T8Mp92ZM9unjLA4nUMUXpNqU2TaS3akWk1mVZvWjvy+fqsU935dUadN0svZyp4Z2f23HR9IdqRoh1ZvKawMI6sKccXFG9NKVVQ+pB/1kb/Hft4tFtTALPJuA2uRTsyoTGhplg6b1eLTnXnV8eJvOE6bwULSh+30CPakaIdGdq82fUUs6Ys5+po1UhPTTn8hFIGUT9IDb2XcZ6Cr6iXRcTvPmtEhHEabvZIA9mOZosgbULzZMbIYChrjI5Sh3bkx5nS8W5xk1w7Uv/1P/fqnROU7Qx7VuOanHk9RXfCa5Yi2pH2PeHmqYwpHVvC2Lc/RbQjK3OXCZyj4MytNYh2ZIXuMoFzFJy5tQbRjqzQXSZwjoIzt9Yg2pEVussEzlFw5tYaRDuyQneZwDkKztxag2hHCgSClBD9lArdZQLnKDhzaw1SUyp0lwmco+DMrTVITanQXSZwjoIzt9YgNaVCd5nAOQrO3FqDRzvyyN3hSKZTuYhDJDHZmw8A5qxvji9x3idgX7l1yg/Igt18vZdb8W7kXx+EWdwM/MSs+4+dUo2RsQxcGp+HgqNw1ZQVop8YBjDT6VzEIYrY4Tha6Hk4eMSvDSVB9BBjYYBRYCfKJsovxM3Rnw62ZhawXDsP1MG2WDbXFyfaVy5jm5gdnq7iQDZGxrnjEnrncRS9793n2KOxhBlpmdO3McSOOFgbNE+BGRQGVoihk+9TxUB599lxoegPILm2m2sxBkk9aOIZuDF01/VWJow2T3EaO3lBl6Bzj65zyaB25O2HTp0uY4UjSdqRhf4xjiB2DNOomvJm5imYQo82Sy5yLlnDmq4diXS9q/nwHV5S6NXWMNZva5Y01z99dGNfqKNLuD7DAajOm1KzZWadN37rKT1AbD2JduSIA6tZSkvakUiu3c13pRMtEpZKCVZ+IBko60u0pniMkQfQdck9Su3mmB7tw+PdojuqrND/9ZgYEcS0TOfUZDJ9MUJMTQHMJuM2uA7SjrRzvULkIK0VlsHY9m77QqgixvpSkLemwMY9OpiR6RIWhVs7UheR7Ba3ObQj7YCnRAyxabQjuRaUvintSCTXVnO8pDqzCXUFkHq7U80fXtDFF8QYI2Ndoq6VOWrK1aJTs8v7h6f7h9tFp2bL9NqRxX/diCI2iXakrXXIBhG/+2g99Za0I7Fc282R9zt3NoGHEUw99tsZ7d3HNEbGOXzJuDNYixzakbp85DHCkYh2ZD+VpCCEEGLGA593PcWpdcgEIXtAHBG9Le3IoP0p0PYcdzaBpx5OPbDs2ve9p6Zgxsg4B1IGzuoMiHZkhe4ygXMUnLm1BtGOrNBdJnCOgjO31iDakRW6ywTOUXDm1hpEO7JCd5nAOQrO3FqDaEdW6C4TOEfBmVtrEO1IgUCQEqKfUqG7TOAcBWdurUFqSoXuMoFzFJy5tQapKRW6ywTOUXDm1hqkplToLhM4R8GZW2vAasrxJzS8mc57CAQGHjW4/z7/3vzxFm1WW/Ob0o7EaAcpPB5pDHdsYWOkOVxTRrJ0kfA8SylcxAEkhmjqaWe8Qksh3A+2Kh92BqwssKFSoXakV5QJaZ7IGOnYssZYxsGasgaU5sKA1pQ0LuJAmCq7DmfFcg2YpxSRfqABK8SQ1sFU4VDefQ7qJl6tA4Q2pXkqY6Rjyxqj6haAduTlqZp/vjn/rju/ixaO9GhHrgAxu0ngH4KuM+7R/+KG1RSebz4taUcitEnNExm7mq9sxbZExgE0cMkYp87b5UydLh+fr8+67uwuj3akI9opgQ/BdUHtSESMozRiaspU4XhryiB3RHrOYdqlawoL4+CaMpSSfDVFf7lkWFP6vndr1cQ+HiFrtCOPrKYqMTUFMJuM2+A6SDvSaHtysV4h2pFW8yBjw5H+91uuKSOdt9tFp3Rk0I7UZpaFJvkBr98DptKOBAmURsx6ioZSNUUvKH0fuMbhnHnJeop3PQXSoz1SjJaiR8ttnrKGNfW03ss4T9FuXep3dhARv/tMFg5W74xcgWxpWSA0T2YMd2xhY6Q5pB25/VwtunzakSb/aUHZzrAnNq7JuddTHAS4IGQPiPUi8Na0IxHaRfenODq2hLFvf4poR1bmLhM4R8GZW2sQ7cgK3WUC5yg4c2sNoh1ZobtM4BwFZ26tQbQjK3SXCZyj4MytNYh2ZIXuMoFzFJy5tQbRjhQIBCkh+ikVussEzlFw5tYapKZU6C4TOEfBmVtrkJpSobtM4BwFZ26tQWpKhe4ygXMUnLm1BmdN0Tc0H7WjGs50Mhdx8A1B9zZtx3Zq2m79IHfDCcu3vDffceVNaUdimbWbIykzjM3jsxYDPzHr/mOnVGNk5AKXxueh4CigmpJG6QKtKSW1QWBia1PaDzsudTighp+QC3NHumUBgFF4umiKREPcHIdCHWytLCCZdR6og23hI6l9v7440b5yGdvE7PB0FQey8QrWSHVcQu88jqJ315R0B/sIo7AMAiYO+KlwWhQB7rSeKVx3LYBRIF00VaIp7z47LhT9ASSzdnNaylzlFuwairDL1spSXw0ydvKCLkHnHl3nkk3tyNsPg4JKLu1IbfLE7Vxy3/fGNA9RryE+9CHvPoMTbiWlIe1IJLOu5pSUWbfEqq1hrN/WLGnWbYKMfaGOLuH6DAe4tCOXp6o7v358fni8W3RqtsygHbkqrJCYqqYQ1SSD1lP2zyGvF5++Je1IJLPu5r6UmbpQjjcGxNhZquxJUrDx2qWRCl9y3NkZhVOP1qEjmVqPdkSLn84beZ5Ce2Ci3n1Kvx9aiKkpgNlk3AbXQdqRdmZXiByklbLB2PZu+0KoIsb6UpC3psDGPTp0kekSFgWm83a81FsFNQVfT6E8S0HuiM9nEcSsp2goVVPMp5S2noLNvMbN8ZS5agTYFU5jV6eaP7ygiy+IMUbGukRdK3PUlKtFp2aX9w9P9w+XM9UtrtNrR2oFsMyvG4kmDqOF8TTzlNJvhQgifveZLNFYvQPfXpA1SDizdnMkZbZ3h1/cuEcLNfHdxzR2/cZ0sAEuGXcGa5FbO/LjbFfS8mlHDr+BM1+j7VGVvRz7U7DtMEURsgfEIURYZI1W3x6jQrQjg/anQClzeoeeerex2cA7WaAaW3nxpgyc1RkQ7cgK3WUC5yg4c2sNoh1ZobtM4BwFZ26tQbQjK3SXCZyj4MytNYh2ZIXuMoFzFJy5tQbRjqzQXSZwjoIzt9Yg2pECKpRSzP9XwAGin1Khu0zYbDZ/WPc8P3X0cB2QmlKhu0xQShWvHdBHKVW6ewQ7SE2p0F0myDxFQIHUlArdZYLMUwQUADWFtuXcC+xZSuQiDr6HnLxZPsPe/OHIF6sDhD06T/ntr/Zd9KvV+NLqe227+a//WGCeQt5Kb+xqd+2SpzZPZoyMvbLGSHNQj3ZreeTBLzjTyVzEASY2PNHEM4SkKMLclewYDAqap/zx4he/vPj9uv/Dev3rXxqFY/W9ev/bcvMUx4k4R0KtLCCnWUjNExkjY6+sMarI4aop6QYz+CyVfl4CJg6o1kFr2pHuR/qH98P05Le/Ut//4Cw3eT+Ut8uDuolX6wA5/09pnsoYH3sFjVF1C4d25M35d2r++RjJSK92ZPFj/PSH3KPJRIsi5N1ncMKtpIDzlN//5uQXv1nbf+9qyn6OPPp+qnmKhjVdO1KXHEHURnB5keONXc1XtmJbIuMAGrhkjEPn7fqs6+anO0na+WW0yBui87Ymqy5mQqqa0pp2ZHBN+eG9OrwWjacw085TBrkj0nMOS6KUriksjGNqSm492vUFVXUxE5LNU2hRRL37IPPvMoiZpwBmE89TtEyFPeeDzQrRjrSaBxkbjvS/33JNMbQjl/NBLzKXdiRZdTETUq2nEKNIUsKKI2Y9ZaqagvSwXlD6PnCNw86CrKdQ1lMsPdrLmZp/eni6f7hddGq2TK8dqTHhPk/x/e5DiiKshBWdwSGI+N3n97852Zeb1fc5f0sG5ymHn+b2ABM6quzanMP3s4jVPJnxkb/O5DNGZ9Nu7cgvn+a7FarZMpN25FvenzIuAanXU5Bf/ssiZH/K4Sfk4VLWNVqoh1cuQUbKnpGVWz+xJzZPZYyMvaLG2CgV7cgK3WUCOE9h8AHnKYLJIdqRFbrLBDnvI6BAtCMrdJcJMk8RUCDakRW6ywSZpwgoEO3ICt1lgsxTBBSI4p4gAIqBQCT0vwImEP2UCt1lAucoOHNrDVJTKnSXCZyj4MytNUhNqdBdJnCOgjO31iA1pUJ3mcA5Cs7cWoOjpmjaEcDmWzKgTCd0kZbYHuTN8hn25q8Za0dCl+Au0jfHZwwoiBtpvzycWbs5kjLDGB/5QRv5h2tjp1RjZOQCl8bnoeAofPOU9cXJEc876V+P41zEASa2Fu1ICGAUpGOWZbit47UjgTQ4D9TBto5jifplQzbHNLaJ2eHpKg5k4wNTK0GOS+idrefXU1O00/wxoNSUI13EIWDigJ8Kp3EPcKc9ldyE3sAokC6aSq+BMtLWdO1IJLN2c1rKXOUW7BqKsMsgCkabpziNnbygS9C5R9e5ZEM78vC5/dB1H24yaEeSIsoJ+kPu0WSiPfQh7z6DE24lBZsLgJov2iw5a54JNWVN145EMutqTkmZdUts5BvG+m3NkmbdJsjYF+roEq7PcIBD500XfDtG5A3RedMpFXluUtWU1rQjnd9jNWUqORjKSKNrRyKZdTf3pczUhUJHvmXsLFX2JCnYeMsamtUg6yl4FEhNuZyp0+URBYVQU4r9U5xsnkJ7YKLefbjJvEXVFMBsMm6D6yDtSDuzK0QO0krZYGx7t30hVBFjfSnIW1Ng4x4dush0CYvCqfOWQDXSp/M2CnV6pFpPaU070n0BVRIcUKqmmE8pbT0Fm3mNm+Mpc9UIsCucxq5ONX94QRdfEGOMjHWJulYG1ZTbRdctro8qKL6aUvKRSTRxGC2Mp5mnTPWyEIGI3320GPL+jIXVO/DtBVmDhDNrN0dSZnt3+MWNe7RQE999TGPkByn4knFn8PmFtSOPU40kaUeWe2KO258yLgGp11Ow7TBFEbIHxCFEWGSNVt8eo0K0I4P2p0Apc3qHRr7b2GzgnSxQja28eFMGzuoMiHZkhe4ygXMUnLm1BtGOrNBdJnCOgjO31iDakRW6ywTOUXDm1hpEO7JCd5nAOQrO3FqDaEdW6C4TOEfBmVtrENE9gUCQEqKfUqG7TOAcBWdurUFqSoXuMoFzFJy5tQapKRW6ywTOUXDm1hqkplToLhM4R8GZW2tw15Th1NGR+6mRTKdyEQffECRvls+wN79szyBAooC7SN9xXuK8T8BW+vi9+UjKDGPzPJ99PMhLzLo//bzPqph25OHo0LHnvsBMp3MRB3gIDt1FPENICiPMXdGeQYBl03/Msgw3x4k4B1srC0hm7eZwypCjervvDNkc09gmZof3JrQjtYFx5IggjMIyZwkDJg6o1oFoR/Y92kVTHT6nvPsc1E0IWgeiHYme28aicGpH3p11353dvjw+fZ6r02UW7ciBbpkHh/6QezSZaNxD3n0K9wwCZC4ACohos+Qi55I1rEU70uGTLsnkjM4VBaDzdnPeKaVUt7jJpvO2LqmQmKqmiHZkj9eUqeRgvDVlkDsi1hTRjkTWU/AoXDXl5rzrzq8fnx8e7xbdUWUl6g18CiSbp9AemKh3n5KaVU7E1BTAbDJug2vRjnQZ9+jQxTWZwCgcOm+6auSRCpJHjsJ8SLWeQowiSQkrjpj1FA2laor5lNLWU7CZ17g5njJXjQC7wmns6lTzhxd08QUxxshYl6hrZS7tyOVcdedXOwVJNVtm0I6cakoMIdHEYbQwnmaeUrpnEETMOrUY8v6MhY008O0FWYOEM2s3R1Jme3f4xY17tFAT331MY42oyRm+ZNwZrEVu7ciPs31NO05BMmpHwxQ4bn/KuASkXk8p2zMIQrLpFSKciNvKJciYfH8KlDKnd+ipdxubDbyTBaqxlRdvysBZnQHRjqzQXSZwjoIzt9Yg2pEVussEzlFw5tYaRDuyQneZwDkKztxag2hHVuguEzhHwZlbaxDtyArdZQLnKDhzaw2iHSkQCFJC9FMqdJcJnKPgzK01SE2p0F0mcI6CM7fWIDWlQneZwDkKztxag9SUCt1lAucoOHNrDe6aQttx7geS6VQu4uAbguTN8hn25g9HvlgdIOzb0o7EaJO29icyRsZeWWOkuaumrGBduUCAmU7nIg7wEByeaOIZwp0dfkIuzB3plgUARkE6ZlmGm+NEnIOtlQWENql5ImOkY8sao4ocjppinN0+ZkQgmU7lIg4BEwdU60C0I/se7aKp9Boo7z4HdROv1gFCm9I8lTE+9goao+oWLu3Iy1PVfbjZiUiq7vwuvXbk+Hg4Z/0UjyYT7aEPefcZnHArKQ1pRyK0Sc0TGbuar2zFtkTGATRwyRi3duRyvu3PbjbvurO7HNqR+/cxp+5udqSqKaId2eM1ZSo5GG9NGf7tIj3nMO3SNYWFcUxN0YtLppoyoMiyQbJ5Cu2BiXr3meq1gYyYmgKYTcZtcB2kHWm0PblYrxDtSKt5kLHhSP/7LdcUQ+ft8Mmm8zbqyQJT/FTrKZRnKcgd8fksgpj1FA2laor5dh20xuGcecl6inc9xdajVbPLnYikmn+KLSi4Hu3hxw1ev/tsQZw4DGbp5ilTvSxEIOJ3Hy2GvPNRrN4ZvQiyHVV2kDaheTJjZOyVNUZn0x7tyNmneOFIonZkkcfmuP0p4xKQej0F+eW/LEL2gFgvAm9NOxKhTWmeypjSsSWMsVEq2pEVussEzlFw5tYaRDuyQneZwDkKztxag2hHVuguEzhHwZlbaxDtyArdZQLnKDhzaw2iHVmhu0zgHAVnbq1BtCMFAkFKiH5Khe4ygXMUnLm1BqkpFbrLBM5RcObWGqSmVOguEzhHwZlba5CaUqG7TOAcBWdurQGpKQk2jPsyXWxPejJiGfbmr0U7MjM30n55OLN2cyRlhvH6IMziZuAnZt1/7JRqjIxc4NL4PBQchbOmrEP0EzHAmU7mIg6JiB0OqOEn5MLckW5ZAGAUpGOWZbhppwH3fzrYWllAMus8UAfbOo4l6pcN2RzT2CZmh6erOJCNV7B4q+MSeue1KdRIm6fQDrPbCPj3OdZFHNIQIz/1Ae60p3Kix5EMMAqki6bSa6C8++y4UPQHkMzazWkpc5VbsGsowi5bK0vSLMjYyQu6BJ17dJ1LNrUjd5/Pc3W6fHp5fHq5Of9uUJDU/47XjrRYTqwbkoQYXYwg5N1ncMKtpDSkHYlk1tWckjLrltgoN4z125olzbpNkLEv1NElXJ/hAETn7XKmTpePzw+Pz9dnBwVJ/e+jdd7efE0R7cgez91UcjDemjIoMxFrCpRZd3NfykxdKMcbA2LsLFX2JCnYeMsamtUg6yl4FFJT4olpufc8MFHvPlO9NpARU1MAs8m4Da6DtCPtzK4QOUgrZYOx7d32hVBFjPWlIG9NgY17dOgi0yUsCkQ78nI2iLwt5zvlt4enT7MANcnq11OIdZBtbQ1CzHqKhlI1xXxKaesp2Mxr3BxPmatGgF3hNHZ1qvZKab5XBhljZKxL1LUyWk25Pu+686udQm23uM5QU6b99zkRsdHCeJp5ylQvCxGI+N1HiyHvz1hYvQPfXpA1SDizdnMkZbZ3h1/cuMf/kaW9+5jGrt+YDjbAJePO4HPq1o78cv/45X45U/OPlpqkmi2TaEfaLN/O/pRxCUi9njJlPwQhZA/IIaLhUpE1Wn17jArRjkQyazeHUub0Dj31bmOzgXeyQDW28uJNGTirMyDakRW6ywTOUXDm1hpEO7JCd5nAOQrO3FqDaEdW6C4TOEfBmVtrEO3ICt1lAucoOHNrDaIdWaG7TOAcBWdurUG0IwUCQUqIfkqF7jKBcxScubUGqSkVussEzlFw5tYapKZU6C4TOEfBmVtrkJpSobtM4BwFZ26tgaodCXzjgS/TCVzEIYgYtlk+w9784cgXqwOEvWhHwjdEUmYYm+f57ONBXmLW/ennfVaMtCMd35AAZzqZizgEEPOcIdzZ4SfkwtyRblkAYBSkY5ZluDlOxDnY2mMPzqzdHE4ZclRv950hm2Ma+x6K9dvVjgS/8aCGeQqqdSDakX2PdtFUeg2Ud5+DuglB60C0I9Fz21gUJO1I+Jtk2pG+HkkMOjGPJhONbEg/DE64lRTRjjSbaBMVb2qQ1G8AAAO7SURBVMoQzQSvMXDUePWWtSPhb5LpvGG8MyBVTRHtyB6vKVPJwXhryiB3RKwpoh2JrKfgUUhNcYI8T6E9MFHvPlO9NpARU1MAs8m4Da5FO9Jl3KNDF9dkAqOg6byB36TTeUNZJkeq9RTKsxTkjvh8FkHMeoqGUjXFfEpp6ynYzGvcHE+Zq0aAXeE0dnWq+cMLuviCGGNkrEvUtTKpKU4QJw6jhfE085SpXhYiEPG7jxZD3p+xsHoHvr0ga5BwZu3mSMps7w6/uHGPPxS0dx/TGPlBCr5k3BmsRVTtSOCblNqR3h5JiOP2p4xLQOr1FGw7TFGE7AE5RLRyCxFOxG3lEmRMvj8FSpnTOzTG3cZmA+9kgWps5cWbMnBWZ0C0Iyt0lwmco+DMrTWIdmSF7jKBcxScubUG0Y6s0F0mcI6CM7fWINqRFbrLBM5RcObWGkQ7skJ3mcA5Cs7cWoNoRwoEgpQQ/ZQK3WUC5yg4c2sNUlMqdJcJnKPgzK01SE2p0F0mcI6CM7fWIDWlQneZwDkKztxaA1WTKW7DuC/TxfakJyOWYW/+cOSL1QHCXrQj4RsiKTOMzfN89vEgLzHr/vTzPis+2pHIiSMccKaTuYgDnZjvDOHODj8hF+aOdMsCAKMgHbMsw80xrhxsrSwgmbWbwynzjOq1aEfu70Af6UH/Pse5iEMAMVTrQLQj+x7toqn0GijvPgd1E4LWgWhHoue2sSiCtCNfHp/uzjo1v8ynHdmXnqdsQdZkohEN6YfBCbeSItqRZhNtouJNGaKZ4DUGjhqvatCOfL4+61R3fp1R5w2SpUmMVDVFtCN7vKZMJQfjrSnDuCLWFNGORNZT8CgCakpoQYmoKdMUlF60I6MQU1MAs8m4Da5FO9Jl3KNDF9dkAqOg6rxdLTrVnV+FiLyF6rxNVlB60Y6MQsx6ioZSNcUcV7T1FGzmNW6Op8w5qqGuAB4Bu1O1V0rzvTLIGCNjXaKuldFqynKuAlUjg2vKYaF9CiSaOIwWxtPMU6Z6WYhAxO8+Wgx5f8bC6h349oKsQcKZtZsjKXOPauBhBB8BbGWN9u5jGrt+YzrYAJeMO4O1iKQd+XE2qnPvFjfJtSNXoHBeFhy3P8UohYnXU6bcpxOEkD0gjswWWaN1jqvk+1OglLlHNfDUw48AsOza972npmDGVl68KQNndQZEO7JCd5nAOQrO3FqDaEdW6C4TOEfBmVtrEO3ICt1lAucoOHNrDaIdWaG7TOAcBWdurUG0Iyt0lwmco+DMrTX8P3cxQrIQZKcCAAAAAElFTkSuQmCC" alt="" />

这是全部数据,现在需要ordercount列的总数,代码如下:

select COUNT(ordercount) from dbo.tb_order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAF8AAAAuCAIAAADBUFLGAAADDklEQVRoge2YSW7iUBCG/0M1COUuUYSNcpBISBl6kU1fIVJEBhOknIFd1EN6E6kzD71oYSYD9rPohcGx34QZCgxyqYT8ykW98scvYxeG62MHX78t2bHqS061oXJey1zlqJzXSPG3Wi3S+gu3aMMZHd6WSqfZbJLWX7hFG15vOgD/ryJGprU00Lnexe71cAi1jfpTxMOzXF0FndF20zZMTse2bTF4d7wlNqv52VVQxLiSZmI+0YbJ6TQaDSEm73ROOtKvR4LSX2RCw6ic1zyP9fouhTPmv3/85etXSzCvxGQAqjpRLUQzuQOuAre0TJjVRA3H6Lgec3oDCmfMf3175+r/PCoUjn47vYHmphNYkB8ehMfSU1yCeCrcd2LDS6Lj+/7L65uKjvSq9PEkdMRgcjpBw3E6rtd1+hTOfP/55Y2r/+OwUDi85TIBhJ9SjyaEadF8jQY1+0obXiWdrmXCqIpXrqcjTQthaeCGwUsDhpWo4Ridget1uj0KZ8x/fHoR6lsGzIvxEkBn0rG4nD7n10Hhc1N9wzE6/YHb7jgUzpj/8Pgs1v9+UCheOO2OAyAajy5Vx6r8YCm1dsdpXxgwrIQNx+n03Va7S+GMsfuHJ1n9yyIMAOJXolcVRjRpXFCaGWx3lrjhGJ1ef9BsdSjcY+zP/SNdfaKGY3RuM4sb96xMaK7rktZfuLmuG6NjExuANfqMvnON6CR4O5vRbNv++NdaFwf3ursEOgBWftkJPdNOpp2laGeKgaOGjrahs20UT8bLk53xQ97OWZq1c3e8FbS5ADoK7dzs5YItRnTq5fwYys1eDtunmXbi2gm9Xs5/Kd+kRTuK4emViZI13/B00n1HSidl2lGMB6sGStZ848EZtFMv55Hbry8djVI7pHSm0s4K0ai1Ix8PVg2Yl/ONB6fSzmrRqLVDSSepdk6LkN2hV68dxfA0NuKczZNr5/NhBwCQov8suuHpJjwr0w1PN+E9i264uQnaoZtCboJ26AaRm6AdakvDSDThJ09nmJnCMjo6y+joLKOjs4yOzv4Dk9imH42gO3EAAAAASUVORK5CYII=" alt="" />

结合上面两个检索的结果图,我们发现中间有很多的重复数据,下面是解决代码:

select COUNT(DISTINCT ordercount) from dbo.tb_order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEYAAAAoCAIAAADMshv5AAACPElEQVRoge2Z3UrDQBCF56G0LL6LFJvigwiCmBR8CkHqT6rgM3ghFGLrjaD2x6oX0jS2TbrZRS+yDU3XpGoym1I6DGVYw5x+HGr3UKieXX1hluM4qPtlLVgj/bkGgwHqfllrjfRfGTWlCMm2bdT9shY6Ur/fR90va0H17Mr3metRjGaMv769R/Y3K4RUdA2C0mripDHz10azQkhZIwAAxLjUxXD/Gy2BRH02dicYzRh/6b1G9jcMAlAypzMx7oJXN3oC5LARPDwdwmcStdCROOfdl948kgwQdyIPi7SmSNQfjT2MZpx3ur3I/juDEMOanRNO5GGR1nIgjWsl0C6CE1ODTJAm1B+OXIxmjLfa3ch+Sy8Q3YrO1iER/y4IKRDdCp+Rh0VaAsmb0M/hGKMZ48+tDt5+WWuK5FHnc4TRjLGn5zbefllLILneZOAMMdpn7PGphbdf1hJIzRWq8PaAWJRS1P1zWgLJRi4AUPMa3PQEEt5V0rbttw9HQYcUKpAAQAHS2qV0SEvl0vUu7F6nRkp+K8dF2NivK3Dp4WgruHVlgJTk0u3BBmSDtCQu1fc2C9vFAopLMVn9UoOymS6rJ7l0sgPF05v9bJDmXYoJtrUSlM10wTbepdNt2Dn+cLJCmncJFelHl0ISNJd+DrYzefO/HeNSfW8TZis9leQSJlLy9xKWSzFZ3SyBdp4uqy/8XsL6LOFl6dxuD3hZOrc7Hl6Wzs0lvOScm0t44Tk3l7BLWUr/UvD7kvpaQaRvxHrpMIyN31YAAAAASUVORK5CYII=" alt="" />

ok,完成需求!把重复的都过滤掉了.

注意:DISTINCT不能用于COUN(*),只能用于COUNT(),因为DISTICT只能作用于列名,也就是说DISTICT 后面只能跟列名!