认为自己是 LINQ 高手的进来看看
有一个需求要查出点东西。在QQ 群里已经难倒一大片人了。
如果你认为自己是 LINQ 高手的可以试试看看。
如果LINQ 不强的，就不要浪费时间了，留意本贴，等高手来搞定吧
我虽自己搞定了。但对自己写出的语句不是很满意。
所以再另请高人试试！
不多说了。先上东西
数据库
USE [JCIWorkOrder]GO/****** Object: Table [dbo].[Site] Script Date: 05/16/2011 00:11:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Site]( [SiteId] [int] IDENTITY(1,1) NOT NULL, [SiteName] [varchar](32) NOT NULL, CONSTRAINT [PK_SiteId] PRIMARY KEY CLUSTERED ( [SiteId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Site] ONINSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (1, N'LXH')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (4, N'HMT')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (5, N'Sigma')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (6, N'Tsinghua')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (7, N'Viva')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (8, N'Metro')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (9, N'Zizhu')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (10, N'Gateway')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (11, N'Tongfang')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (12, N'Kerryplaza')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (13, N'Guangzhou')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (14, N'Chengdu')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (15, N'Account')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (16, N'Beijing')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (17, N'Shanghai')INSERT [dbo].[Site] ([SiteId], [SiteName]) VALUES (18, N'GEO')SET IDENTITY_INSERT [dbo].[Site] OFF/****** Object: Table [dbo].[OrderStatus] Script Date: 05/16/2011 00:11:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[OrderStatus]( [StatusId] [int] NOT NULL, [StatusName] [varchar](32) NOT NULL, CONSTRAINT [PK_0rderStatus_StatusId] PRIMARY KEY CLUSTERED ( [StatusId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[OrderStatus] ([StatusId], [StatusName]) VALUES (1, N'Open')INSERT [dbo].[OrderStatus] ([StatusId], [StatusName]) VALUES (2, N'Close')INSERT [dbo].[OrderStatus] ([StatusId], [StatusName]) VALUES (3, N'Processing')INSERT [dbo].[OrderStatus] ([StatusId], [StatusName]) VALUES (4, N'Completed ')/****** Object: Table [dbo].[OrderRequest] Script Date: 05/16/2011 00:11:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[OrderRequest]( [WOId] [int] IDENTITY(1,1) NOT NULL, [Alias] [varchar](32) NOT NULL, [Requestor] [varchar](64) NULL, [SiteId] [int] NOT NULL, [SeatNo] [varchar](32) NULL, [Mobile] [varchar](13) NOT NULL, [Phone] [varchar](10) NULL, [CategoryId] [int] NOT NULL, [Description] [varchar](4000) NULL, [StatusId] [int] NOT NULL, [RequestTime] [datetime] NOT NULL, [ExpectedTime] [datetime] NULL, CONSTRAINT [PK_OrderRequest_WOId] PRIMARY KEY CLUSTERED ( [WOId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[OrderRequest] ONINSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (6, N'v-alias', N'赵本山', 1, N'122N', N'131888888', N'1298', 13, N'Description', 1, CAST(0x00009EE0016F4B76 AS DateTime), NULL)INSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (7, N'v-alias', N'赵本山', 1, N'122N', N'131888888', N'9807', 23, N'Description', 4, CAST(0x00009EE001705DAA AS DateTime), NULL)INSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (8, N'v-alias', N'赵子龙', 1, N'8797', N'131888888', N'9999', 13, N'Description', 4, CAST(0x00009EE001708A07 AS DateTime), NULL)INSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (9, N'v-alias', N'大家好', 1, N'12398', N'131888888', N'0989', 18, N'Description', 3, CAST(0x00009EE00170EEEC AS DateTime), NULL)INSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (10, N'v-alias', N'赵子龙', 1, N'98791', N'131888888', N'9989', 10, N'Description', 4, CAST(0x00009EE00171FF1A AS DateTime), NULL)INSERT [dbo].[OrderRequest] ([WOId], [Alias], [Requestor], [SiteId], [SeatNo], [Mobile], [Phone], [CategoryId], [Description], [StatusId], [RequestTime], [ExpectedTime]) VALUES (11, N'v-alias', N'赵本山', 1, N'jhai', N'131888888', N'9876', 10, N'Description', 4, CAST(0x00009EE100A0F3FE AS DateTime), CAST(0x00009EF401666DD0 AS DateTime))SET IDENTITY_INSERT [dbo].[OrderRequest] OFF/****** Object: ForeignKey [FK_OrderRequest_CategoryId] Script Date: 05/16/2011 00:11:51 ******/ALTER TABLE [dbo].[OrderRequest] WITH CHECK ADD CONSTRAINT [FK_OrderRequest_CategoryId] FOREIGN KEY([CategoryId])REFERENCES [dbo].[WorkOrderCategory] ([CategoryId])GOALTER TABLE [dbo].[OrderRequest] CHECK CONSTRAINT [FK_OrderRequest_CategoryId]GO/****** Object: ForeignKey [FK_OrderRequest_OrderStatus] Script Date: 05/16/2011 00:11:51 ******/ALTER TABLE [dbo].[OrderRequest] WITH CHECK ADD CONSTRAINT [FK_OrderRequest_OrderStatus] FOREIGN KEY([StatusId])REFERENCES [dbo].[OrderStatus] ([StatusId])GOALTER TABLE [dbo].[OrderRequest] CHECK CONSTRAINT [FK_OrderRequest_OrderStatus]GO/****** Object: ForeignKey [FK_OrderRequest_SiteId] Script Date: 05/16/2011 00:11:51 ******/ALTER TABLE [dbo].[OrderRequest] WITH CHECK ADD CONSTRAINT [FK_OrderRequest_SiteId] FOREIGN KEY([SiteId])REFERENCES [dbo].[Site] ([SiteId])GOALTER TABLE [dbo].[OrderRequest] CHECK CONSTRAINT [FK_OrderRequest_SiteId]GO
