如何从不同的表基于样本数据库结构创建分层菜单 [英] How to Create hierarchical Menu from different table based on sample database structure
问题描述
我正在与一个菜单结构的网站上工作，以便它可以读取来自多个表下方的子菜单是示例菜单示例
我有几个表像pg_Pages，art_Article，art_Categories，杂志每个表都有一个FK PAGEID在pg_Pages表PK。
我想创建一个SQL查询，将读取从这些表中的数据，并创建分层菜单结构与每个链接指向正确的页面。
我可能需要通过多个查询字符串作为HREF的一部分，如 PAGEID，LanguageID，IssueID和类别ID
样本连接
首页= Default.aspx的LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 1
关于我们= Page.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 2
分类=文章 - Category.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 6
- 政治=文章 - Category.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 6＆安培;类别id = 1
- 经济=文章 - Category.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 6＆安培;类别ID = 2
- 商务=文章 - Category.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 6＆安培;类别id = 16
多媒体= Multimedia.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 10
- 视频= Video.aspx LanguageId = 1＆安培; IssueID = 101安培; PAGEID = 11
我不知道如何最好的方式与容不得一点差错处理这个问题，因为我已经通过不同的查询字符串，以不同的菜单我感到有点困惑如何处理这种方法，我应该创建一个联盟的多个SQL查询处理这种或不同的东西。我需要这个了一个asp.net网站
我还增加了样品的SQL查询，＆安培;每个表输出
SQL
SELECT的PageId AS PARENTID，SUBSTRING（页面名称，0,20）AS PARENT_MENU，SUBSTRING（PageInternalLinkURL，0,24）AS页面处理器，PageLinkPosition，SUBSTRING（PageLayoutPosition，0,14）AS MENU_Type，PageLangID，PageInheritance FROM pg_PagesSELECT p.PageID为PARENTID，SUBSTRING（c.ArticleCategoryName，0,20）AS CHILD_MENU，SUBSTRING（p.PageInternalLinkURL，0,24）AS页面处理器，ArticlePostion，AS条款ArticleID childID的，c.ArticleCategoryID，IssueID，LanguageID FROM art_Articles一个JOIN art_Category C对a.ArticleCategoryID = c.ArticleCategoryID
JOIN pg_pages p在p.PageID = a.PageIDSELECT p.PageID AS PARENTID，发行code作为CHILD_MENU，SUBSTRING（p.PageInternalLinkURL，0,24）AS页面处理器，m.Issue code，IssueID AS childID的，CurrentIssue，IssueDate，从语言标识杂志男加入pg_pages p在m.PageID = p.PageIDSELECT p.PageID AS PARENTID，SUBSTRING（c.ArticleCategoryName，0,20）AS CHILD_MENU，SUBSTRING（p.PageInternalLinkURL，0,24）AS页面处理器，语言标识，ArticleCategoryID AS childID的FROM art_Category为C JOIN pg_Pages P于c.PageID = P .PageID
输出
PARENTID PARENT_MENU页面处理器PageLinkPosition MENU_Type PageLangID PageInheritance
----------- -------------------- ------------------- ----- ---------------- -------------- ----------- ---- -----------
1主页的Default.aspx 10顶部菜单1 0
2关于我们Page.aspx 20顶部菜单1 0
3新闻News.aspx 30顶部菜单1 0
4出版Publication.aspx 40顶部菜单1 0
5文章Articles.aspx 20顶部菜单1 0
6类文章 - Category.aspx 25顶部菜单1 0
10多媒体Multimedia.aspx 60顶部菜单1 0
11视频Videos.aspx 10子菜单1 10
12存档的Default.aspx 40顶部菜单1 0PARENTID CHILD_MENU页面处理器ArticlePostion childID的ArticleCategoryID IssueID LanguageID
----------- -------------------- ------------------- ----- -------------- ----------- ----------------- --- -------- -----------
5政治Articles.aspx 10 12 1 1 1
5政治Articles.aspx 10 13 1 3 1
5政治Articles.aspx 10 14 1 4 1
5政治Articles.aspx 1 15 1 5 1
5政治Articles.aspx 20 16 1 5 1
5业务Articles.aspx 30 17 16 10 1
5集团新闻Articles.aspx 40 18 6 5 1
5基础设施Articles.aspx 50 23 17 10 1
5集团新闻Articles.aspx 60 24 6 5 1
5书评Articles.aspx 70 25 18 10 1
PARENTID CHILD_MENU页面处理器发行code childID的CurrentIssue IssueDate的LangID
----------- ----------- ------------------------ ---- ------- ----------- ------------ -------------------- --- -----------
12 106的Default.aspx 106 1 0 2012-09-01 00：00：00.000 1
12 106的Default.aspx 106 2 1 2012-09-01 00：00：00.000 2
12 102 Default.aspx的102 3 1 2011-11-01 00：00：00.000 1
12 103的Default.aspx 103 4 1 2012-02-01 00：00：00.000 1
12 109 109的Default.aspx 5 1 2012年12月1日00：00：00.000 1
PARENTID CHILD_MENU页面处理器的LangID childID的
----------- -------------------- ------------------- ----- ----------- -----------
6条政治，Category.aspx 1 1
6条经济-Category.aspx 1 2
6文化用品-Category.aspx 1 3
6体育用品-Category.aspx 1 4
6 xxxxxxxxxxxxxxxxxxx文章 - Category.aspx 1 5
6组新闻文章-Category.aspx 1 6
6人文章 - Category.aspx 1 7
6社论消息文章-Category.aspx 1 8
6董事长致辞文章 - Category.aspx 1 9
6企业文章-Category.aspx 1月16日
6条的基础设施，Category.aspx 1 17
6书评文章 - Category.aspx 1 18
6条金融-Category.aspx 1 19
6条生活方式，Category.aspx 1 20
6其他文章 - Category.aspx NULL 21
根据您的问题....
- 创建一个SQL查询通过的与条款并做出一个单一的表来所有的记录。之后选择任何HTML表格菜单......在上设置主菜单项UL标记（您可以使用数据读取器等控制）。它的简单的方法......
之后，如果你没有得到THN让我知道... ...我做这种类型的菜单....我会提供样品code ....
I am working on a website with a menu structure so that it can read submenus from multiple table below is Sample Menu example
I have several table like pg_Pages, art_Article, art_Categories, Magazine each table has a FK PageID with PK in pg_Pages table.
I want to create a sql query which will read data from these tables and create a hierarchical Menu Structure with each link pointing to correct page.
I may need to pass several query string as part of href such as PageID, LanguageID, IssueID and CategoryID
Sample Link
Home = Default.aspx?LanguageId=1&IssueID=101&PageID=1
About Us = Page.aspx?LanguageId=1&IssueID=101&PageID=2
Categories = Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6
--Politics = Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=1
--Economy= Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=2
--Business= Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=16
Multimedia = Multimedia.aspx?LanguageId=1&IssueID=101&PageID=10
--Video= Video.aspx?LanguageId=1&IssueID=101&PageID=11
I am not sure how handle this in best manner with no room for error, Since i have to pass different query-string to different menu i am bit confused how to handle this approach, should i create a multiple sql query with Union to handle this or something different. I need this for an asp.net website
I have also added sample sql query, & Output from each table
SQL
SELECT PageId AS ParentID,SUBSTRING(PageName,0,20) AS PARENT_MENU,SUBSTRING(PageInternalLinkURL,0,24) AS PageHandler, PageLinkPosition,SUBSTRING(PageLayoutPosition,0,14) AS MENU_Type,PageLangID,PageInheritance FROM pg_Pages
SELECT p.PageID as ParentID, SUBSTRING(c.ArticleCategoryName,0,20) AS CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler, ArticlePostion,ArticleID AS CHILDID,c.ArticleCategoryID,IssueID,LanguageID FROM art_Articles a JOIN art_Category c ON a.ArticleCategoryID = c.ArticleCategoryID
JOIN pg_pages p ON p.PageID = a.PageID
SELECT p.PageID AS ParentID, IssueCode as CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler, m.IssueCode,IssueID AS CHILDID,CurrentIssue,IssueDate,LangID FROM Magazine m JOIN pg_pages p ON m.PageID = p.PageID
SELECT p.PageID AS ParentID, SUBSTRING(c.ArticleCategoryName,0,20) AS CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler, LangID,ArticleCategoryID AS CHILDID FROM art_Category c JOIN pg_Pages p ON c.PageID = p.PageID
OUTPUT
ParentID PARENT_MENU PageHandler PageLinkPosition MENU_Type PageLangID PageInheritance
----------- -------------------- ------------------------ ---------------- -------------- ----------- ---------------
1 Home Default.aspx 10 TopMenu 1 0
2 About Us Page.aspx 20 TopMenu 1 0
3 News News.aspx 30 TopMenu 1 0
4 Publication Publication.aspx 40 TopMenu 1 0
5 Articles Articles.aspx 20 TopMenu 1 0
6 Categories Article-Category.aspx 25 TopMenu 1 0
10 Multimedia Multimedia.aspx 60 TopMenu 1 0
11 Video Videos.aspx 10 SubMenu 1 10
12 Archive Default.aspx 40 TopMenu 1 0
ParentID CHILD_MENU PageHandler ArticlePostion CHILDID ArticleCategoryID IssueID LanguageID
----------- -------------------- ------------------------ -------------- ----------- ----------------- ----------- -----------
5 Politics Articles.aspx 10 12 1 1 1
5 Politics Articles.aspx 10 13 1 3 1
5 Politics Articles.aspx 10 14 1 4 1
5 Politics Articles.aspx 1 15 1 5 1
5 Politics Articles.aspx 20 16 1 5 1
5 Business Articles.aspx 30 17 16 5 1
5 Group News Articles.aspx 40 18 6 5 1
5 Infrastructure Articles.aspx 50 23 17 5 1
5 Group News Articles.aspx 60 24 6 5 1
5 Book Review Articles.aspx 70 25 18 5 1
ParentID CHILD_MENU PageHandler IssueCode CHILDID CurrentIssue IssueDate LangID
----------- ----------- ------------------------ ----------- ----------- ------------ ----------------------- -----------
12 106 Default.aspx 106 1 0 2012-09-01 00:00:00.000 1
12 106 Default.aspx 106 2 1 2012-09-01 00:00:00.000 2
12 102 Default.aspx 102 3 1 2011-11-01 00:00:00.000 1
12 103 Default.aspx 103 4 1 2012-02-01 00:00:00.000 1
12 109 Default.aspx 109 5 1 2012-12-01 00:00:00.000 1
ParentID CHILD_MENU PageHandler LangID CHILDID
----------- -------------------- ------------------------ ----------- -----------
6 Politics Article-Category.aspx 1 1
6 Economy Article-Category.aspx 1 2
6 Culture Article-Category.aspx 1 3
6 Sports Article-Category.aspx 1 4
6 xxxxxxxxxxxxxxxxxxx Article-Category.aspx 1 5
6 Group News Article-Category.aspx 1 6
6 People Article-Category.aspx 1 7
6 Editorial Message Article-Category.aspx 1 8
6 Chairman's Message Article-Category.aspx 1 9
6 Business Article-Category.aspx 1 16
6 Infrastructure Article-Category.aspx 1 17
6 Book Review Article-Category.aspx 1 18
6 Finance Article-Category.aspx 1 19
6 Lifestyle Article-Category.aspx 1 20
6 Others Article-Category.aspx NULL 21
as per your question....
-create a sql query by with clause and make a one single table with coming your all records. after that choose any html table menu ...in that on ul tag set main menu item(for that you can use data reader etc. control).it's easy way....
after that if you are not getting thn let me know...i made this type of menus....i will provide sample code....
这篇关于如何从不同的表基于样本数据库结构创建分层菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
