Siv SQL COUNT rov qab ntau cov ntaub ntawv
Cov lus queries yog ib qho tseem ceeb ntawm cov Lus Nyuaj Kawm Lus (SQL). Nws retrieves cov ntaub ntawv raws li tej kev cai los ntawm ib cov ntaub ntawv database. Koj tuaj yeem siv SQL queries - suav nrog COUNT () muaj nuj nqi - kom tau txais txhua hom ntaub ntawv los ntawm lub database.
Txoj kev ua haujlwm SQL COUNT () yog qhov tseem ceeb tshaj vim tias nws tso cai rau koj los suav cov ntaub ntawv khaws tseg raws li tus neeg siv kev ntsuas. Koj tuaj yeem siv nws suav tag nrho cov ntaub ntawv nyob rau hauv ib lub rooj, suav cov cim tseem ceeb hauv ib kem, los yog suav cov zauv cov ntaub ntawv tshwm sim uas muaj feem xyuam nrog cov txheej xwm.
Tsab xov xwm no piav qhia txhua yam ntawm cov qauv no.
Cov piv txwv yog raws li feem ntau siv Northwind database, uas nquag nkoj nrog cov khoom database rau kev siv ua ib qho kev qhia.
Ntawm no yog ib qho excerpt los ntawm cov ntaub ntawv database cov lus:
| ProductID | ProductName | Muab khoom siv | QuantityPerUnit | UnitPrice | UnitsInStock |
|---|---|---|---|---|---|
| 1 | Chai | 1 | 10 lub thawv x 20 hnab | 18.00 | 39 |
| 2 | Tseeb | 1 | 24 - 12 oz fwj | 19.00 | 17 |
| 3 | Aniseed Syrup | 1 | 12 - 550 ml hwj | 10.00 | 13 |
| 4 | Tus kws kho mob Anton tus Cajun Seasoning | 2 | 48 - 6 oz txhiab | 22.00 | 53 |
| 5 | Tus kws lij choj Anton lub Gumbo Mix | 2 | 36 lub thawv | 21.35 | 0 |
| 6 | Pog yawg Grand Boysenberry kis mus | 3 | 12 - 8 txhiab txhiab | 25.00 | 120 |
| 7 | Txiv ntxawm Bob lub Organic qhuav Pears | 3 | 12 - 1 lb pkgs. | 30.00 | 15 |
Suav Cov Ntaub Ntawv Hauv Ib Lub Sij Hawm
Cov lus nug yooj yim tshaj plaws yog suav cov xov tooj ntawm cov ntaub ntawv hauv lub rooj. Yog tias koj xav paub seb muaj pes tsawg ntawm cov khoom uas muaj nyob rau hauv cov khoom rooj, siv cov lus nug hauv qab no:
SELECT COUNT (*)
LOS NTAWM cov khoom;
Cov lus nug no rov qab cov naj npawb ntawm cov kab hauv rooj. Hauv qhov ua piv txwv, nws yog 7.
Suav Ntaus Tus Ntaus Nqi hauv Kem
Koj tseem tuaj yeem siv lub COUNT kev ua haujlwm los txheeb xyuas cov naj npawb ntawm qhov tseem ceeb hauv ib kem. Hauv qhov ua piv txwv no, yog tias koj xav paub txog ntau tus neeg muag khoom uas lawv cov khoom muag hauv chav haujlwm tsim, koj tuaj yeem ua tiav qhov siv cov lus nug nram qab no:
SELECT COUNT (DISTINCT SupplierID)
LOS NTAWM cov khoom;
Cov lus nug no rov qab tus naj npawb ntawm qhov tseem ceeb muaj nyob rau hauv qhov kev pab cuam Supports. Hauv qhov no, lo lus teb yog 3, sawv cev 1, 2, thiab 3.
Suav Cov Ntaub Ntawv Sib Txuas Lus
Muab cov dej num COUNT () coj los ua ke nrog qhov chaw txiav txim siab kom paub cov xov tooj ntawm cov ntaub ntawv uas muaj feem xyuam nrog cov txheej xwm. Piv txwv, xav hais tias tus thawj tswj hwm xav kom muaj kev paub ntawm cov theem seem hauv fab haujlwm. Cov lus nug nram qab no qhia txog cov zauv sawv cev ntawm UnitsInStock tsawg tshaj 50 units:
SELECT COUNT (*)
Ntawm cov khoom
Qhov twg UnitsInStock <50;
Hauv qhov no, cov lus nug yuav xa ib qho nqi 4, sawv cev Chai, Tseej, Aniseed Syrup, thiab txiv ntxawm Bob cov Organic qhuav Pears.
COUNT () txhais tau tias yog ib qho tseem ceeb rau cov thawj coj ntawm cov neeg ua haujlwm uas nrhiav cov ntsiab lus kom tau raws li kev lag luam. Nrog me ntsis creativity, koj siv tau cov COUNT () muaj nuj nqi rau ntau lub hom phiaj.