表结构:CREATE TABLE [dbo].[Demand](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BoxID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[PlatID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginName] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CompanyAddress] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[ProjectName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[OriginTec] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginContacts] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[SysPlatID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SysPlatName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ResolutionID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[IsTouch] [bit] NULL,
[Keyboard] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[IsTFin] [bit] NULL,
[IsNeedInlay] [bit] NULL,
[InlayGame] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[OriginBillingType] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[IsNeedG] [bit] NULL,
[IsDynamic] [bit] NULL,
[GGameID] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[OriginSpecialContent] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[AppointmentDate] [datetime] NULL,
[ProjectEndDate] [datetime] NULL,
[Note] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[Billingway] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[BillingTime] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AssignedClient] [int] NULL,
[CurrentHead] [int] NULL,
[Userid] [int] NULL,
[CreateTime] [datetime] NULL,
[LastUpdateTime] [datetime] NULL,
CONSTRAINT [PK_DEMAND] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[SupplementDemand](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DemandID] [int] NULL,
[ManufacturerName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginID] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Date] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[ProjectID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DemandPerson] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[Post] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Mobile] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[Email] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IM] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DemandDate] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[DemandDes] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[Rate] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AssignedClient] [int] NULL,
[Res] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[CreateUser] [int] NULL,
[CreateTime] [datetime] NULL,
CONSTRAINT [PK_SUPPLEMENTDEMAND] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
现在有2个表,demand 和Supplementdemand表,demand表是主表,另个是子表,现在我想查的是:
查询主表信息,根据子表的CreateTime排序
[ID] [int] IDENTITY(1,1) NOT NULL,
[BoxID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[PlatID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginName] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CompanyAddress] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[ProjectName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[OriginTec] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginContacts] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[SysPlatID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SysPlatName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ResolutionID] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[IsTouch] [bit] NULL,
[Keyboard] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[IsTFin] [bit] NULL,
[IsNeedInlay] [bit] NULL,
[InlayGame] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[OriginBillingType] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[IsNeedG] [bit] NULL,
[IsDynamic] [bit] NULL,
[GGameID] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[OriginSpecialContent] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[AppointmentDate] [datetime] NULL,
[ProjectEndDate] [datetime] NULL,
[Note] [nvarchar](300) COLLATE Chinese_PRC_CI_AS NULL,
[Billingway] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[BillingTime] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AssignedClient] [int] NULL,
[CurrentHead] [int] NULL,
[Userid] [int] NULL,
[CreateTime] [datetime] NULL,
[LastUpdateTime] [datetime] NULL,
CONSTRAINT [PK_DEMAND] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[SupplementDemand](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DemandID] [int] NULL,
[ManufacturerName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[OriginID] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Date] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[ProjectID] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DemandPerson] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[Post] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[Mobile] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[Email] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[IM] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DemandDate] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[DemandDes] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[Rate] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[AssignedClient] [int] NULL,
[Res] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
[CreateUser] [int] NULL,
[CreateTime] [datetime] NULL,
CONSTRAINT [PK_SUPPLEMENTDEMAND] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
现在有2个表,demand 和Supplementdemand表,demand表是主表,另个是子表,现在我想查的是:
查询主表信息,根据子表的CreateTime排序
a.*
from
demand a join Supplementdemand b
on
a.id=b.[DemandID]
order by
b.CreateTime
on a.id=b.id
order by b.id
from demand a
left join (select DemandID,max(CreateTime) CreateTime
from Supplementdemand group by DemandID)
on a.ID = b.DemandID
order by (case when b.DemandID is null then 1 else 0 end),b.CreateTime