Mysql
是否可以在單個 sql 查詢中將每個類別列為標題及其自己的產品
我想將每個類別和子類別作為標題查看並列出其下的產品
注意:產品必須是同一供應商
像這樣:
類別1
- 子類 1
++產品1
++產品2
- 子類別2
++產品1
++產品2
類別2
- 子類別
++產品1
++產品2
是否可以將其從控制器發送到 laravel 中查看?
GROUP BY
修飾符WITH ROLLUP
可以幫助:SELECT ALL c.name, s.name, p.name FROM product AS p INNER JOIN subcategory AS s ON p.subcategory_id = s.id INNER JOIN category AS c ON s.cat_id = c.id WHERE p.supplier_id = ? GROUP BY 1, 2, 3 WITH ROLLUP ORDER BY 1 ASC, 2 ASC, 3 ASC -- NULL will be first
給出:
category1 | NULL | NULL | category1 | subcategory1 | NULL | category1 | subcategory1 | product1 | category1 | subcategory1 | product2 | category1 | subcategory2 | NULL | category1 | subcategory2 | product3 | category1 | subcategory2 | product4 | category2 | NULL | NULL | category2 | subcategory3 | NULL | category2 | subcategory3 | product5 | category2 | subcategory3 | product6 |
您可以控制生成的行以使用函式
WITH ROLLUP
檢索或標記它們: https ://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_groupingGROUPING()
Laravel 問題超出了範圍。
在 SQL 中建構樹很麻煩。在應用程式碼中這樣做更直接。
GROUP_CONCAT()
是另一種方法:mysql> SET group_concat_max_len = 50; Query OK, 0 rows affected (0.00 sec) mysql> SELECT province, GROUP_CONCAT(ascii_city SEPARATOR ', ') -> FROM canada GROUP BY province; +---------------------------+----------------------------------------------------+ | province | GROUP_CONCAT(ascii_city SEPARATOR ', ') | +---------------------------+----------------------------------------------------+ | Alberta | twin butte, carway, whiskey gap, whisky gap, coutt | | British Columbia | lameque, rocky point, saseenos, saseenos station, | | Manitoba | gretna, emerson, south junction, sprague, middlebr | | New Brunswick | seal cove, north head, campo bello, welchpool, wel | | Newfoundland and Labrador | saint shotts, cape pine, portugal cove south, cape | | Northwest Territories | cunningham landing, bell rock, fort smith, salt ri | | Nova Scotia | lower wood harbour, lower woods harbour, woods har | | Nunavut | charlton depot, belcher islands, sanikiluaq, burwe | | Ontario | scudder, kingsville, point pelee, mcgregor, cottam | | Prince Edward Island | white sands, flat river, ocean view, murray harbou | | Quebec | muncey, deloro, sainte-agnes-de-dundee, sainte-agn | | Saskatchewan | north portal, northgate, arena, bracken, masefield | | Yukon | kaslo, dalton post, watson, carcross, caribou, car | +---------------------------+----------------------------------------------------+ 13 rows in set, 13 warnings (0.01 sec) mysql> SHOW WARNINGS LIMIT 1; +---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1260 | Row 5 was cut by GROUP_CONCAT() | +---------+------+---------------------------------+ 1 row in set (0.00 sec)