将SQL表联合到jqGrid的树网格结构中

Question

我已经用我目前拥有的数据库结构创建了一个示例：

此处

我的目标是获得一个数据库表，其中所有数据都已经在正确的位置，并按正确的顺序排序，以便输入到jqGrid中，最后应该是这样的：

Entity          |OrderTime  |City   |ProductType    |...
-------------------------------------------------------------------
AlexKlar        |           |       |
   SubPack     |17:00      |London |
   Mango   |           |       |Fruit  |
   WelcomePack |15:00      |London |
   Apple   |           |
   Banana  |           |       |Fruit  |
AnnaKlar        |           |       |
   WelcomePack |16:00      |London |
   Apple   |           |       |Fruit  |
JuliaKlar       |           |       |
   PremiumPack |18:00      |London |
   Lychee  |           |       |Fruit  |
   SubPack     |18:30      |London |
   Mango   |           |       |Fruit  |
   WelcomePack |15:00      |London |
   Apple   |           |       |Fruit  |
   Banana  |           |       |Fruit  |

问题1：将所有表合并为一个主表，以便为jgGrid定义id/pparents/level/leaf，最有效的方法是什么？由于不是所有级别都需要所有列的值，我不得不给它分配一个空字符串">

SELECT 
CONVERT(VARCHAR,PersonID) AS id, 
0 AS level, 
'false' AS isLeaf, 
'null' AS parent, 
FullName AS entity,
'' AS OrderTime,
'' AS City,
'' AS Active,
'' AS ProductType,
'' AS Price
from Persons
UNION ALL
SELECT 
CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS id, 
1 AS level, 
'false' AS isLeaf, 
CONVERT(VARCHAR,PersonID) AS parent, 
OrderName AS entity,
COALESCE(CAST(OrderTime AS VARCHAR(5)),'') AS OrderTime,
City,
Active,
'' AS ProductType,
'' AS Price
from Orders
UNION ALL
SELECT 
CONVERT(VARCHAR,per.PersonID) + '>' + CONVERT(VARCHAR,ord.OrderID) + '>' + CONVERT(VARCHAR,prod.ProductID) AS id, 
2 AS level, 
'true' AS isLeaf, 
CONVERT(VARCHAR,per.PersonID) + '>' + CONVERT(VARCHAR,ord.OrderID) AS parent, 
ProductName AS entity,
'' AS OrderTime,
'' AS City,
so.Completed AS Active,
ProductType,
COALESCE(CONVERT(VARCHAR,Price),'') AS Price
from SubmittedOrders so
INNER JOIN Orders ord ON ord.OrderID = so.OrderID
INNER JOIN Persons per ON per.PersonID = ord.PersonID
INNER JOIN Products prod ON prod.ProductID = so.ProductID
Order by id

问题2：在创建了我拥有的4个表的表联合之后，我想对它进行排序，使其按字母顺序排列，但只能在它们自己的级别内(使其看起来像上面所示的表)。我想避免在内置的排序函数grid.jqGrid('sortGrid'，'importJob')中使用jqGrid；在客户端，因为如果我有5000个订单/行，这真的很慢。

提前感谢您的帮助。

Answer 1

这将使他们按照您需要的方式进行排序：

;WITH
CTE_All
AS
(
SELECT 
per.PersonID,
per.FullName,
per.PersonRow,
ord.OrderID,
ord.OrderName,
ord.OrderTime,
ord.City,
ord.Active,
ord.OrderRow,
prod.ProductID,
prod.ProductName,
prod.ProductType,
prod.Price,
prod.ProductRow,
so.Completed
from #SubmittedOrders so
LEFT JOIN (select *, row_number() over (order by OrderName) OrderRow from #Orders) ord 
ON ord.OrderID = so.OrderID
LEFT JOIN (select *, row_number() over (order by FullName) PersonRow from #Persons) per 
ON per.PersonID = ord.PersonID
LEFT JOIN (select *, row_number() over (order by ProductName) ProductRow from #Products) prod 
ON prod.ProductID = so.ProductID
)
SELECT DISTINCT
CONVERT(VARCHAR,PersonID) AS id, 
0 AS level, 
'false' AS isLeaf, 
'null' AS parent, 
FullName AS entity,
'' AS OrderTime,
'' AS City,
'' AS Active,
'' AS ProductType,
'' AS Price,
PersonRow, 
0 AS OrderRow, 
0 AS ProductRow
from CTE_All Persons
UNION ALL
SELECT DISTINCT
CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS id, 
1 AS level, 
'false' AS isLeaf, 
CONVERT(VARCHAR,PersonID) AS parent, 
OrderName AS entity,
COALESCE(CAST(OrderTime AS VARCHAR(5)),'') AS OrderTime,
City,
Active,
'' AS ProductType,
'' AS Price,
PersonRow, 
OrderRow, 
0 AS ProductRow
from CTE_All Orders
UNION ALL
SELECT 
CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) + '>' + CONVERT(VARCHAR,ProductID) AS id, 
2 AS level, 
'true' AS isLeaf, 
CONVERT(VARCHAR,PersonID) + '>' + CONVERT(VARCHAR,OrderID) AS parent, 
ProductName AS entity,
'' AS OrderTime,
'' AS City,
Completed AS Active,
ProductType,
COALESCE(CONVERT(VARCHAR,Price),'') AS Price,
PersonRow, 
OrderRow, 
ProductRow
from CTE_All
Order by PersonRow, OrderRow, ProductRow

您可以通过使用创建摘要记录的组汇总来消除联合。但是，您必须在选择子句中添加一些case语句：

select 
CONVERT(VARCHAR,PersonID) + isnull('>' + CONVERT(VARCHAR,OrderID),'') + isnull('>' + CONVERT(VARCHAR,ProductID),'') AS id,
Case when ProductID IS NOT null then 2 when OrderID IS NOT null then 1 else 0 end level,
case when ProductID IS NOT null then 'true' else 'false' end isLeaf,
case when ProductID IS NOT null then CONVERT(VARCHAR,PersonID) + isnull('>' + CONVERT(VARCHAR,OrderID),'') when OrderID IS NOT null then CONVERT(VARCHAR,PersonID) else 'null' end parent,
COALESCE(ProductName, OrderName, FullName) entity,
OrderTime,
City,
Active,
ProductType,
Price
from
(
select #Persons.PersonID, 
#Orders.OrderID,
#Products.ProductID, 
MIN(FullName) FullName,
CASE WHEN #Orders.OrderID IS NOT NULL THEN MIN(OrderName) END OrderName,
CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(ProductName) END ProductName,
CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(convert(varchar(1),Completed)) ELSE '' END Active,
CASE WHEN #Orders.OrderID IS NOT NULL AND #Products.ProductID IS NULL THEN MIN(OrderTime) ELSE '' END OrderTime,
CASE WHEN #Orders.OrderID IS NOT NULL AND #Products.ProductID IS NULL THEN MIN(City) else '' END City,
CASE WHEN #Products.ProductID IS NOT NULL THEN MIN(ProductType) ELSE '' END ProductType,
CASE WHEN #Products.ProductID IS NOT NULL THEN convert(varchar(20),MIN(Price)) ELSE '' END Price        
from #SubmittedOrders
JOIN #Orders
on #SubmittedOrders.OrderID = #Orders.OrderID
JOIN #Persons
on #Orders.PersonID = #Persons.PersonID
JOIN #Products
ON #SubmittedOrders.ProductID = #Products.ProductID
GROUP BY ROLLUP (#Persons.PersonID, #Orders.OrderID,#Products.ProductID)
having #Persons.PersonID is not null
) x
ORDER BY FullName, OrderName, ProductName