{"id":90,"date":"2007-06-04T18:35:06","date_gmt":"2007-06-04T16:35:06","guid":{"rendered":"http:\/\/unckel.com\/blog\/?p=90"},"modified":"2019-12-07T22:20:22","modified_gmt":"2019-12-07T21:20:22","slug":"sql-anzahl-der-artikel-zum-kategorie-per-unterabfrage-ermitteln","status":"publish","type":"post","link":"https:\/\/unckel.de\/blog\/sql-anzahl-der-artikel-zum-kategorie-per-unterabfrage-ermitteln\/","title":{"rendered":"SQL: Anzahl der Artikel zum Kategorie per Unterabfrage ermitteln"},"content":{"rendered":"

Gegeben sind zwei Tabellen, die mit einem Fremdschl\u00fcssel (CategoryID) verbunden sind. Typisch f\u00fcr die Auflistung bei Blogs.<\/p>\n

Categories<\/b><\/p>\n\n\n\n\n\n\n\n\n
ID<\/th>\nCategory<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nFuniture<\/td>\n<\/tr>\n
2<\/td>\nAutomobiles<\/td>\n<\/tr>\n
3<\/td>\nBuildings<\/td>\n<\/tr>\n
4<\/td>\nComputer<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Articles<\/b><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
ID<\/th>\nCategoryID<\/th>\nArticle<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n1<\/td>\nTable<\/td>\n<\/tr>\n
2<\/td>\n1<\/td>\nChair<\/td>\n<\/tr>\n
3<\/td>\n2<\/td>\nCar<\/td>\n<\/tr>\n
4<\/td>\n2<\/td>\nTruck<\/td>\n<\/tr>\n
5<\/td>\n3<\/td>\nSkycraper<\/td>\n<\/tr>\n
6<\/td>\n4<\/td>\nMouse<\/td>\n<\/tr>\n
7<\/td>\n4<\/td>\nHonitor<\/td>\n<\/tr>\n
8<\/td>\n4<\/td>\nKeyboard<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Die Anzahl der Artikel erhalten wir per Unterabfrage (SELECT COUNT…). Da einfach die Schl\u00fcssel bei der Tabellen verkn\u00fcpfen.<\/p>\n

SELECT Category,\n       (SELECT COUNT(*) \n        FROM Articles \n        WHERE CategoryID = Categories.ID) AS Num\nFROM Categories\nORDER BY Category;<\/pre>\n\n\n\n\n\n\n\n\n
Category<\/th>\nNum<\/th>\n<\/tr>\n<\/thead>\n
Automobiles<\/td>\n2<\/td>\n<\/tr>\n
Buildings<\/td>\n1<\/td>\n<\/tr>\n
Computer<\/td>\n3<\/td>\n<\/tr>\n
Funiture<\/td>\n2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Gegeben sind zwei Tabellen, die mit einem Fremdschl\u00fcssel (CategoryID) verbunden sind. Typisch f\u00fcr die Auflistung bei Blogs. Categories ID Category 1 Funiture 2 Automobiles 3 Buildings 4 Computer Articles ID CategoryID Article 1 1 Table 2 1 Chair 3 2 Car 4 2 Truck 5 3 Skycraper 6 4 Mouse 7 4 Honitor 8 4 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[],"class_list":["post-90","post","type-post","status-publish","format-standard","hentry","category-webdesign"],"_links":{"self":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/90","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/comments?post=90"}],"version-history":[{"count":1,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/90\/revisions"}],"predecessor-version":[{"id":814,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/posts\/90\/revisions\/814"}],"wp:attachment":[{"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/media?parent=90"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/categories?post=90"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unckel.de\/blog\/wp-json\/wp\/v2\/tags?post=90"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}