MySQL Group By子句

MySQL Group By子句

可以使用GROUP BY按列的值进行分组,并且,如果愿意的话,也可对列进行计算。可以使用COUNT, SUM, AVG等函数,在上页进行列的分组计算。

要了解GROUP BY子句,我们先考虑有一个 employee_tbl表,这具有以下记录:

mysql> create table employee_tabl(
    id int(10) not null primary key auto_increment,
    name varchar(32) not null default '', 
    work_date date,
    daily_typing_pages int(10) default 0
);
Query OK, 0 rows affected (0.28 sec)

INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('John', '2015-01-24', 150);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Ram', '2015-07-27', 220);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-05-06', 170);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-01-24', 100);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jill', '2015-01-24', 220);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-01-26', 300);
INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-02-24', 350);

查询结果:

mysql> SELECT * FROM employee_tabl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2015-01-24 |                150 |
|    2 | Ram  | 2015-05-27 |                220 |
|    3 | Jack | 2015-05-06 |                170 |
|    3 | Jack | 2015-04-06 |                100 |
|    4 | Jill | 2015-04-06 |                220 |
|    5 | Zara | 2007-06-06 |                300 |
|    5 | Zara | 2007-02-06 |                350 |
+------+------+------------+--------------------+
7 rows in set (0.00 sec)

现在假定在上表的基础上,要算天每个员工做的工作数量。

如果我们如写一个SQL查询,那么我们将得到以下结果:

mysql> SELECT COUNT(*) FROM employee_tbl;
+---------------------------+
| COUNT(*)                  |
+---------------------------+
| 7                         |
+---------------------------+

但是,这不是我们的服务目的,我们要分页显示打印每个人的总数。这是通过使用联聚合函数GROUP BY子句如下:

mysql> SELECT name, COUNT(*)
    -> FROM   employee_tbl 
    -> GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| Jack |        2 |
| Jill |        1 |
| John |        1 |
| Ram  |        1 |
| Zara |        2 |
+------+----------+
5 rows in set (0.04 sec)

我们会看到更多相关 GROUP BY 的函数,如SUM,AVG等

 

移动端:请扫描本页面底部(右侧)二维码并关注微信公众号,回复:”教程” 选择相关教程阅读

94 Responses

  1. Jzpxhy说道:

    escitalopram 10mg ca purchase lexapro for sale buy generic revia 50 mg

  2. Fyjwwb说道:

    lasix for sale purchase acticlate online cheap brand albuterol

  3. Tnlptz说道:

    buy strattera generic purchase seroquel sale buy sertraline sale

  4. Lorntb说道:

    buy actigall 150mg generic order cetirizine online cetirizine for sale online

  5. Dzkjok说道:

    zithromax cheap buy azithromycin 250mg for sale generic gabapentin 100mg

  6. Lmugff说道:

    online doctor birth control men last longer pills does tadalafil help premature ejaculation

  7. Twipuy说道:

    purchase deltasone generic buy accutane order amoxil 250mg

  8. Jcmflz说道:

    best ulcer medication for ulcers how to test for stomach ulcers at home online urgent care for uti

  9. Hqxzai说道:

    phenergan buy online order phenergan pill ivermectin 12 mg online

  10. Uwuatk说道:

    strongest antifungal over the counter fungus clear now vitality health juicing recipes for high blood pressure

  11. Pwbcel说道:

    buy duloxetine pill generic glucotrol 5mg buy modafinil 100mg sale

  12. Oaakyd说道:

    buy valtrex online overnight herpes pills online drugs approved for prediabetes

  13. Zvfsft说道:

    periactin order online order generic ketoconazole 200mg nizoral 200mg generic

  14. Misida说道:

    nicotine replacement therapy while smoking herb that helps quit smoking buy painkillers online without predictions

  15. Vmkdnc说道:

    medroxyprogesterone 10mg pills buy biltricide 600mg online order hydrochlorothiazide pill

  16. SLOTMR说道:

    100년도 더 전에 선생님, 이것이 과거를 속였습니까?
    하바네슬롯

  17. Kwnwbq说道:

    top 10 sleeping pills nz hair loss treatment without minoxidil virtual doctor for weight loss

  18. Spapnd说道:

    order generic femara aripiprazole 30mg oral aripiprazole canada

  19. Gmitrs说道:

    alfuzosin 10 mg for sale prescription medicine for stomach cramps heartburn covered by medicaid

  20. Rpcctx说道:

    buy minocin 50mg online cheap hytrin 5mg requip 1mg us

  21. Ovnmir说道:

    adult acne medication adult female acne buy trileptal without prescription

  22. Gskfoh说道:

    clonidine 0.1mg uk antivert for sale online spiriva brand

  23. Vgtqyp说道:

    buy calcitriol 0.25mg buy generic labetalol 100mg buy fenofibrate 200mg sale

  24. Qncjmm说道:

    buy trimox without a prescription oral arimidex 1mg buy clarithromycin online

  25. Cyigze说道:

    write thesis essays done for you slots casino games

  26. Rjoqit说道:

    help writing a paper cefixime 200mg drug suprax 100mg ca

  27. Voydte说道:

    order aspirin 75mg generic free slot games online online gambling casinos

  28. Exsjpz说道:

    trazodone 100mg tablet buy clindamycin paypal order clindac a online cheap

  29. Hxqbze说道:

    terbinafine drug play casino online real money online casinos usa

  30. Rxxxng说道:

    order generic cefuroxime 250mg careprost tablet order methocarbamol 500mg generic

  31. Fmgflz说道:

    nolvadex 10mg ca buy nolvadex 20mg pills order budesonide for sale

  32. Eqsolt说道:

    purchase cleocin pills cheap cleocin buy ed pills usa

  33. Tkcvpr说道:

    retin price order generic avana 100mg purchase avanafil sale

  34. Pqezul说道:

    order metronidazole 400mg keflex 125mg sale order cephalexin 250mg generic

  35. Jnwqoj说道:

    lamictal 200mg cost minipress brand buy nemazole pills

  36. Yoasyv说道:

    diflucan 200mg canada cipro cost cipro 1000mg brand

  37. Psnyki说道:

    aurogra 50mg canada aurogra order online estrace price

  38. Zefvqj说道:

    buy spironolactone generic order valacyclovir 500mg pills proscar 5mg pills

  39. Rdgfle说道:

    academicwriters writing dissertation service dissertation writers online

  40. Ytqgua说道:

    tamsulosin where to buy order flomax 0.4mg sale zocor 10mg drug

  41. Jovrwq说道:

    buspar 10mg for sale cordarone 100mg cheap buy cordarone 200mg generic

  42. Suylgv说道:

    imitrex 25mg pill buy dutasteride where can i buy avodart

  43. Cqrehm说道:

    zyloprim 300mg us allopurinol 300mg generic buy rosuvastatin 20mg without prescription

  44. Srgpgm说道:

    order nexium 20mg generic buy nexium without a prescription order topamax 200mg generic

  45. Ieqshw说道:

    famotidine 40mg pills buy pepcid 40mg pill tacrolimus buy online

  46. Gwogwm说道:

    astelin 10 ml oral brand astelin irbesartan oral

  47. Xxveod说道:

    coumadin price paxil 10mg tablet buy reglan 10mg pill

  48. Uawoqr说道:

    purchase xenical order generic asacol 400mg order diltiazem 180mg generic

  49. Rivdbt说道:

    buy pamelor 25mg pill buy pamelor generic paracetamol 500 mg ca

  50. Zyaozl说道:

    inderal for sale order motrin 600mg without prescription purchase clopidogrel without prescription

  51. Nfdkvo说道:

    alendronate 70mg brand buy fosamax online cheap furadantin 100 mg pills

  52. Ywdadb说道:

    purchase amaryl online buy arcoxia 60mg pills buy arcoxia 60mg for sale

  53. Lvwmfa说道:

    ozobax for sale online buy generic ketorolac online ketorolac over the counter

  54. Gjhqts说道:

    ozobax over the counter ozobax tablet toradol pills

  55. Xofukl说道:

    claritin 10mg oral buy generic altace over the counter dapoxetine 60mg sale

  56. Zlmlyr说道:

    phenytoin order online cheap flexeril 15mg ditropan online

  57. Kjgmod说道:

    buy aceon 4mg online cheap fexofenadine 120mg cheap buy generic fexofenadine over the counter

  58. Aadgbl说道:

    purchase levitra online tizanidine pills tizanidine canada

  59. Kazxud说道:

    clomid 50mg pills order generic serophene azathioprine 50mg without prescription

  60. Iqgqha说道:

    methylprednisolone 4mg tablet buy nifedipine 10mg pills aristocort 10mg over the counter

  61. Haykno说道:

    free online blackjack no download generic synthroid 75mcg order generic synthroid

  62. Vbubde说道:

    gambling games free blackjack ivermectin 6 mg tablets

  63. Hntgjb说道:

    amantadine pill cheap avlosulfon 100 mg avlosulfon online order

  64. Ztwduw说道:

    online gambling money sports gambling buy albuterol 4mg for sale buy generic ventolin

  65. Igrpiu说道:

    black jack card game free online casino for real cash cheap lasix

  66. Lpbcix说道:

    buy protonix online order phenazopyridine 200 mg buy generic pyridium over the counter

  67. Kzekzb说道:

    buy azithromycin 500mg generic buy azipro generic buy gabapentin 800mg online

  68. Mcphwt说道:

    buy lipitor 10mg for sale proventil for sale order norvasc generic

  69. Yynwtt说道:

    accutane 10mg tablet order isotretinoin 40mg sale azithromycin 250mg for sale

  70. Ehrxhj说道:

    buy provigil pills order deltasone 40mg online deltasone 20mg pill

  71. Efxtkt说道:

    cefdinir 300 mg ca buy prevacid 30mg for sale buy lansoprazole

  72. Vblijl说道:

    order cialis 10mg without prescription oral cialis 5mg canadian viagra and healthcare

  73. Exrddb说道:

    buy generic telmisartan oral plaquenil 200mg order molnupiravir for sale

  74. Khxaeq说道:

    purchase premarin pills order premarin 600 mg buy viagra 100mg online cheap

  75. Azsrvv说道:

    buy omeprazole tablets order metoprolol online order metoprolol 100mg online cheap

  76. Rynrlo说道:

    xalatan order order xeloda 500mg online cheap oral rivastigmine 6mg

  77. Jwlsjt说道:

    betahistine pill zovirax usa probenecid 500mg canada

  78. Davidval说道:

    Totally! Finding info portals in the UK can be unendurable, but there are scads resources at to boost you find the perfect identical for the sake of you. As I mentioned formerly, conducting an online search with a view https://gingerparrot.co.uk/pags/step-by-step-guide-how-to-delete-news-on-pinterest.html “UK news websites” or “British news portals” is a pronounced starting point. Not but purposefulness this grant you a comprehensive tip of report websites, but it will also provender you with a improved pact of the current story landscape in the UK.
    Aeons ago you obtain a liber veritatis of imminent story portals, it’s important to value each anyone to shape which upper-class suits your preferences. As an benchmark, BBC Dispatch is known for its ambition reporting of information stories, while The Guardian is known for its in-depth criticism of partisan and popular issues. The Unconnected is known championing its investigative journalism, while The Times is known by reason of its vocation and investment capital coverage. Not later than entente these differences, you can pick out the talk portal that caters to your interests and provides you with the hearsay you call for to read.
    Additionally, it’s quality considering neighbourhood expos‚ portals for specific regions within the UK. These portals lay down coverage of events and news stories that are applicable to the area, which can be firstly cooperative if you’re looking to safeguard up with events in your neighbourhood pub community. In search exemplar, municipal communiqu‚ portals in London classify the Evening Paradigm and the Londonist, while Manchester Evening Hearsay and Liverpool Repercussion are hot in the North West.
    Inclusive, there are numberless statement portals available in the UK, and it’s high-level to do your digging to unearth the joined that suits your needs. By evaluating the different news programme portals based on their coverage, variety, and editorial standpoint, you can select the song that provides you with the most relevant and engrossing news stories. Good fortunes with your search, and I anticipate this tidings helps you find the correct dope portal suitable you!

  79. Uubscu说道:

    buy enalapril 5mg generic vasotec cheap buy generic lactulose

  80. Bvqjnd说道:

    order mestinon 60mg sale pyridostigmine oral rizatriptan price

  81. Sxpdcf说道:

    order ferrous 100mg pills risedronate tablet purchase betapace

  82. Gbkmqq说道:

    monograph 600mg price monograph order online cilostazol 100 mg price

  83. Ooyzhv说道:

    buy prasugrel 10 mg order prasugrel 10 mg generic purchase tolterodine generic

  84. Michaeladoto说道:

    To presume from verified dispatch, dog these tips:

    Look in behalf of credible sources: https://starmaterialsolutions.com/pag/news-from-ross-macbeth-s-update.html. It’s high-ranking to safeguard that the report roots you are reading is reputable and unbiased. Some examples of reliable sources categorize BBC, Reuters, and The Different York Times. Announce multiple sources to pick up a well-rounded view of a precisely news event. This can improve you carp a more ended facsimile and dodge bias. Be hep of the position the article is coming from, as flush with reputable report sources can compel ought to bias. Fact-check the low-down with another origin if a expos‚ article seems too unequalled or unbelievable. Forever be inevitable you are reading a current article, as expos‚ can substitute quickly.

    By following these tips, you can become a more aware of dispatch reader and better understand the beget around you.

  85. Tubwxm说道:

    cost dydrogesterone 10mg buy dapagliflozin 10mg sale buy empagliflozin 10mg for sale

  86. Xgimli说道:

    fludrocortisone 100 mcg cost buy imodium 2mg order imodium 2 mg for sale

  87. Xvvarf说道:

    meloset 3mg canada meloset 3mg tablet buy danazol 100 mg generic

  88. Ufkugl说道:

    dipyridamole 100mg usa dipyridamole 100mg for sale order pravastatin 20mg generic

  89. Xgtthe说道:

    aspirin cost order aspirin 75mg pills order imiquimod creams

  90. Kgnljm说道:

    mintop canada erectile dysfunction medicines pills for erection

  91. Urlkzb说道:

    generic precose 50mg order generic repaglinide 2mg fulvicin 250 mg sale

  92. Ximxzh说道:

    best cialis sites online sildenafil brand order viagra 50mg pill

  93. Qdgzcl说道:

    buy generic ketotifen doxepin 25mg pill imipramine online

  94. Lpgqov说道:

    fenofibrate 200mg canada generic fenofibrate purchase fenofibrate generic

发表评论

您的电子邮箱地址不会被公开。

CAPTCHAis initialing...