΢ͻӦ
MICROCOMPUTER & ITS APPLICATIONS
2000Vol.19No.4P.58-59



Ż֣ £ѯķ
Ҧ
ժ Ҫ ֣ £ûʹýṹԣӣѣ̣ӣʣ 󣟣ͣģļȡݣһЩŻѯķ
ؼʣ ѯ ӣѣ Ż
Visual BasicвɱҪõѯܽ˶ʹĵúȫ˲ѯŻķ
1
Ʊֶʱѡ񳤶Ƚ϶̵ͣÿҳдŵļ¼ӣJOINʹõֶαͬݵֶΡ
2ѹݿ
ѹݿжô1ݿӣԭеŻܲЧѹݿ޶ȵؿ˷ȱ㡣2ݿӣͱñȽϡ顱ѹݿ԰1ݼеӲ̵ҳУ˳ɨִٶȡѹݿ£1ݣ
DBEngineCompactDatabase CVBBIBLIOMDB壬CVBBIBLIO2MDB
Kill CVBBIBLIOBAK
Name CVBBIBLIOMDB As CVBBIBLIOBAK
Name CVBBIBLIO2MDB As CVBBIBLIOMDB
ݿⱻƵ£ÿǾѹݿ⡣
3ѯоʽ
ĳбʽĲѯΪһѯʱܻ⡣ѯ1Query1ǵ2SELECT룺
Dim DB As Database
Dim RS As RecordSet
Set DBDBEngineWorkspaces0OpendatabaseBiblioMDB壩
DBCreateQueryDefQuery1壬SELECT IIFAuID1Hello䣬Goodbye䣩 AS X FROM Authors壩
Set RSDBOpenRecordSetSELECT * FROM Query1 WHERE XHello壩
ѯ1еIIFʾʽܱŻԵ2SELECTеWHEREҲܱŻѯͲŻԺϲΪ1Ƕ׵SQL䣺
Set RSDBOpenRecordSetSELECT* FROM Authors WHERE AuID1壩
ڸӵǶײѯֱֶдڱʽУDBCreateQueryDefQuery1壬SELECT IIFAuID1Hello䣬Goodbye䣩 AS XAuIDFROM Authors壩Set RSDBOpenRecordSetSELECT * FROM Query1 WHERE AuID1壩
ڲѯ޷㣬ɰѼϲѯУҪ²ѯС
4顢Ӻۼ
ӶʱۼֶͬӦȷֶۼƣSumCountȣֶͬһ
ĲѯЧʽϵͣΪSUMֶOrdУGROUP BYֶCustС
SELECT CustCustID
FIRSTCustCustNameAS CustName
SUMOrdPriceAS Total
FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
GROUP BY CustCustID
ĲѯЧʽϸߣΪSUMGROUP BYֶξΪOrdCustIDͬһ
SELECT OrdCustID
FIRSTCustCustNameAS CustName
SUMOrdPriceAS Total
FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
GROUP BY OrdCustID
5ֶԽԽ
GROUP BYеֶԽ࣬ѯѵʱԽʹFIRSTۼƺڼGROUP BYֶεĸ
Чʵ͵ӣ
SELECT CustCustID
CustCustName
CustPhone
SUMOrdPriceAS Total
FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
GROUP BY CustCustIDCustCustNameCustPhone
Чʸߵӣ
SELECT OrdCustID
FIRSTCustCustNameAS CustName
FIRSTCustPhoneAS Phone
SUMOrdPriceAS Total
FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
GROUP BY OrdCustID
6ǰǶGROUP BY
Ӷ1еֶνз飬ѸSELECTֽΪ2ѯΪЧѴGROUP BYӾSECECTΪ1Ƕײѯ뵽ϲѯķǷС
Чӣ
SELECT OrdCustID
FIRSTCustCustNameAS CustName
FIRSTCustPhoneAS Phone
SUMOrdPriceAS Total
FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
GROUP BY OrdCustID
Чӣ
ѯ1
SELECT CustIDSUMPriceAS Total
FROM Ord
GROUP BY CustID
ѯ2
SELECT Query1CustIDCustCustNameCustPhoneѯ1Total FROM Cust INNER JOIN Ord ON CustCustIDOrdCustID
7JOINӾеֶ
JOINӱʱJOINӾõֶκ󣬲ѯŻʹñȽϸ߼ڲӲŻѯ䡣
Ȼ1ԱȽСռ12ҳ棩ͲҪԸñڴеҳȽ٣ԣǷԱҪ
8ʹÿŻıʽ
ʹRushmoreŻĲѯRushmoreԶؼеĲѯ
9COUNT*COUNֶݣ
΢ݿCOUNT*رգʹCOUNT*ִʱCOUNTֶݣöࡣ⣬вͬCOUNT*㷵صУCOUNTֶݣֶΪNULLС
10бLIKE
ڲѯڱʱֵδȷãSQLдֵ
11LIKEеͨڿͷ
ʹLIKEѰƥУͨڿͷλãáã
Like Smith壬LikeSm*
ã
Like *sen
Like *sen*
12ʹмʱ
ʹ佨ʱǵѯҪĲѯʹʱӦʹʱЧǳ
13NOT INӲѯһʹ
NOT INӲѯһʹЧǳýתǶײѯʹOUTER JOIN
ǲûжĿͻ
Чѯ
SELECT Customers*
FROM Customers
WHERE CustomersCustomer ID
NOT INSELECTCustomer ID FROM Orders
Чѯ
SELECT Customers*
FROM Customers LEFT JOIN Orders
ON CustomersCustomer IDݣOrdersCustomer IDWHEREOrdersCustomer IDIs Null
Ҧ£й֯ѧУ210463
ο
ͣͣ ʣʹã֣ £㣵̣ӹҵ磬
£ ţУ ң ģ֣ £㣶Աָϣеҵ磬
ӣ £ӣ ʣ֣ £㣶ʹֲᣮеҵ磬
ǣ ã֣ £ͻָϣ廪ѧ磬
ǣ ףã ϣ֣ £㣳ŵͨѧԷ磬
ģ֣ £ơݿƪʵ磬
ոڦ
