这个问题可能说不清楚了,各位大大帮忙转个脚本算了,转完就结贴放分了。 这是2005生成的脚本,麻烦转成MysqlCREATE VIEW [dbo].[OrderListView] AS SELECT n.ContentText AS CommentText, m.OrderId, m.CustomerId, m.Photos, m.OriginalSize, m.PhotoBefore, m.PhotoAfter, m.PhotoSlightly, m.PhotoTitle, m.Description, m.StatusId, m.AddTime, n.OrderCommentsId, o.StatusTitle, n.AddTime AS CommentTime, p.UserName FROM dbo.CustomerOrders AS m LEFT OUTER JOIN (SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime FROM dbo.OrderComments AS t WHERE (OrderCommentsId = (SELECT MAX(OrderCommentsId) AS Expr1 FROM dbo.OrderComments WHERE (OrderId = t.OrderId)))) AS n ON m.OrderId = n.OrderId LEFT OUTER JOIN dbo.OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT OUTER JOIN dbo.aspnet_Users AS p ON p.UserId = m.CustomerId
CREATE VIEW v_OrderCommentsMaxId AS SELECT OrderId,MAX(OrderCommnetsId) AS Expr1 FROM OrderComments GROUP BY OrderId; CREATE VIEW v_OrderCommentsJoin AS SELECT oc.OrderCommentsId,oc.OrderId, oc.UserId,oc.ContentText,oc.AddTime FROM OrderComments AS oc JOIN v_OrderCommentsMaxId AS ocm ON oc.OrderId = ocm.OrderId AND ocm.Exprl = oc.OrderCommentsId; CREATE VIEW v_OrderListView AS SELECT FROM CustomerOrders AS m LEFT JOIN v_OrderCommentJoin AS ocj ON m.OrderId = ocj.OrderId LEFT JOIN OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT JOIN aspnet_Users AS p ON p.UserId = m.CostomerId;-----------然后查v_OrderListView
CREATE VIEW [dbo].[OrderListView] AS SELECT n.ContentText AS CommentText, m.OrderId, m.CustomerId, m.Photos, m.OriginalSize, m.PhotoBefore, m.PhotoAfter, m.PhotoSlightly, m.PhotoTitle, m.Description, m.StatusId, m.AddTime, n.OrderCommentsId, o.StatusTitle, n.AddTime AS CommentTime, p.UserName FROM dbo.CustomerOrders AS m LEFT OUTER JOIN (SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime FROM dbo.OrderComments AS t WHERE (OrderCommentsId = (SELECT MAX(OrderCommentsId) AS Expr1 FROM dbo.OrderComments WHERE (OrderId = t.OrderId)))) AS n ON m.OrderId = n.OrderId LEFT OUTER JOIN dbo.OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT OUTER JOIN dbo.aspnet_Users AS p ON p.UserId = m.CustomerId语法应该都是一样的!在mysql front上试试先
多谢梁哥,我对mysql语法不熟,前面两个view中有些小错误,我还是看得出来,已经修复, 请问第三个view(v_OrderListView)中的ocj,是不是就是我那个sqlserver view 中的 n ?(SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime FROM dbo.OrderComments AS t WHERE (OrderCommentsId = (SELECT MAX(OrderCommentsId) AS Expr1 FROM dbo.OrderComments WHERE (OrderId = t.OrderId)))) AS n
查 v_orderlistview 的最后语句是不是这样: CREATE VIEW OrderListView AS SELECT m.OrderId, m.CustomerId, m.Photos, m.OriginalSize, m.PhotoBefore, m.PhotoAfter, m.PhotoSlightly, m.PhotoTitle, m.Description, m.StatusId, m.AddTime, n.ContentText AS CommentText, n.OrderCommentsId, n.AddTime AS CommentTime, o.StatusTitle, p.Name FROM CustomerOrders AS m LEFT JOIN v_OrderCommentsJoin AS n ON m.OrderId = n.OrderId LEFT JOIN OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT JOIN my_aspnet_Users AS p ON p.Id = m.CustomerId;
这是2005生成的脚本,麻烦转成MysqlCREATE VIEW [dbo].[OrderListView]
AS
SELECT n.ContentText AS CommentText, m.OrderId, m.CustomerId, m.Photos, m.OriginalSize, m.PhotoBefore, m.PhotoAfter, m.PhotoSlightly, m.PhotoTitle,
m.Description, m.StatusId, m.AddTime, n.OrderCommentsId, o.StatusTitle, n.AddTime AS CommentTime, p.UserName
FROM dbo.CustomerOrders AS m LEFT OUTER JOIN
(SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime
FROM dbo.OrderComments AS t
WHERE (OrderCommentsId =
(SELECT MAX(OrderCommentsId) AS Expr1
FROM dbo.OrderComments
WHERE (OrderId = t.OrderId)))) AS n ON m.OrderId = n.OrderId LEFT OUTER JOIN
dbo.OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT OUTER JOIN
dbo.aspnet_Users AS p ON p.UserId = m.CustomerId
语法不太懂,等梁哥,树哥
AS
SELECT OrderId,MAX(OrderCommnetsId) AS Expr1
FROM OrderComments
GROUP BY OrderId;
CREATE VIEW v_OrderCommentsJoin
AS
SELECT oc.OrderCommentsId,oc.OrderId,
oc.UserId,oc.ContentText,oc.AddTime
FROM OrderComments AS oc
JOIN v_OrderCommentsMaxId AS ocm
ON oc.OrderId = ocm.OrderId
AND ocm.Exprl = oc.OrderCommentsId;
CREATE VIEW v_OrderListView
AS
SELECT
FROM CustomerOrders AS m
LEFT JOIN v_OrderCommentJoin AS ocj
ON m.OrderId = ocj.OrderId
LEFT JOIN OrderStatus AS o
ON o.OrderStatusId = m.StatusId
LEFT JOIN aspnet_Users AS p
ON p.UserId = m.CostomerId;-----------然后查v_OrderListView
AS
SELECT n.ContentText AS CommentText, m.OrderId, m.CustomerId, m.Photos, m.OriginalSize, m.PhotoBefore, m.PhotoAfter, m.PhotoSlightly, m.PhotoTitle,
m.Description, m.StatusId, m.AddTime, n.OrderCommentsId, o.StatusTitle, n.AddTime AS CommentTime, p.UserName
FROM dbo.CustomerOrders AS m LEFT OUTER JOIN
(SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime
FROM dbo.OrderComments AS t
WHERE (OrderCommentsId =
(SELECT MAX(OrderCommentsId) AS Expr1
FROM dbo.OrderComments
WHERE (OrderId = t.OrderId)))) AS n ON m.OrderId = n.OrderId LEFT OUTER JOIN
dbo.OrderStatus AS o ON o.OrderStatusId = m.StatusId LEFT OUTER JOIN
dbo.aspnet_Users AS p ON p.UserId = m.CustomerId语法应该都是一样的!在mysql front上试试先
请问第三个view(v_OrderListView)中的ocj,是不是就是我那个sqlserver view 中的 n ?(SELECT OrderCommentsId, OrderId, UserId, ContentText, AddTime
FROM dbo.OrderComments AS t
WHERE (OrderCommentsId =
(SELECT MAX(OrderCommentsId) AS Expr1
FROM dbo.OrderComments
WHERE (OrderId = t.OrderId)))) AS n
CREATE VIEW OrderListView
AS
SELECT
m.OrderId,
m.CustomerId,
m.Photos,
m.OriginalSize,
m.PhotoBefore,
m.PhotoAfter,
m.PhotoSlightly,
m.PhotoTitle,
m.Description,
m.StatusId,
m.AddTime,
n.ContentText AS CommentText,
n.OrderCommentsId,
n.AddTime AS CommentTime,
o.StatusTitle,
p.Name FROM
CustomerOrders
AS m
LEFT JOIN
v_OrderCommentsJoin
AS n
ON
m.OrderId = n.OrderId
LEFT JOIN OrderStatus AS o
ON o.OrderStatusId = m.StatusId
LEFT JOIN my_aspnet_Users AS p
ON p.Id = m.CustomerId;
所以得一步一步的细化了.