SQL查询的问题,请各位帮我看看.

[wubaowang]

select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID
 

查询结果
did Customid user sex calldate
----------- ------------------- ----------
149 149 张会 女 2007-08-22 12:34:17.700
149 149 张会 女 2007-08-22 12:50:15.560
149 149 张会 女 2007-08-22 13:11:34.077
149 149 张会 女 2007-10-19 12:24:10.140
217 217 张磊 女 2008-02-20 12:50:33.650
217 217 张磊 女 2008-02-20 12:54:24.853
217 217 张磊 女 2008-02-20 12:58:50.183
217 217 张磊 女 2008-02-20 15:43:00.463

我想要查询:did字段不重复和calldate字段以最大时间显示.

[ai19811125]

select u.*,t.calltime from users u join
(select max(calltime) as calltime,userid from tel group by userid) t on t.userid=u.[id] and u.[name] like '%张%'

[xluzhong]

是不是把你想要的结果以及数据给写一下？这样大伙写的时候会更有目的性？

[nextflying]

select a.Customid as did,a.*,max(r.calldate)
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID
and a.customname like '%张%'
join [Order] as r on a.Customid=r.Customer_CustomID
group by a.Customid

OK

[ojuju10]

select a.*,max(r.calldate) as calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID
group by a.Customid,a.Customid, a.user,a.sex,r.calldate    
 

[nextflying]

select a.Customid as did,a.*,max(r.calldate)
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID
and a.customname like '%张%'
join [Order] as r on a.Customid=r.Customer_CustomID
group by a.Customid

OK?

[huangjh_love]

select * from (select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID) h
where exists(select 1 from h where did=h.did and calldate>h.calldate)

没有测试。你自己测试下。

[ojuju10]

--手误
select a.*,max(r.calldate) as calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID
group by a.Customid,a.Customid, a.user,a.sex 

[wubaowang]

To: ojuju10

消息 156，级别 15，状态 1，第 5 行
关键字 'user' 附近有语法错误。


--------------------------------------

To: huangjh_love

消息 208，级别 16，状态 1，第 1 行
对象名 'h' 无效。


--------------------------------------
To: Inspect2008

消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'Customer.CustomName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

[huangjh_love]

select * from (select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID)h
where exists(select 1 from (select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID)h where did=h.did and calldate>h.calldate)

[huangjh_love]

select * from (select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID)h
where exists(select 1 from (select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%'  join [Order] as r on a.Customid=r.Customer_CustomID) k 
where did=h.did and calldate>h.calldate)

你再试试这个？

[wubaowang]

三张表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
	[CustomID] [int] IDENTITY(1,1) NOT NULL,
	[CustomName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Gender] [nvarchar](4) COLLATE Chinese_PRC_CI_AS NULL,
	[WorkingUnit] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
	[ContactAddress] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
	[WorkingTitle] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
	[FavouriteDish] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
	[DislikeDish] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
	[Consumability] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
	[ConsumeTimes] [int] NULL,
	[OrderTimes] [int] NULL,
	[OrderDate] [datetime] NULL,
	[CancelTimes] [int] NULL,
	[IsVip] [bit] NULL,
	[IsBanned] [bit] NULL,
	[LastConsumeTime] [datetime] NULL,
	[Remark] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
	[CustomManager_CustomerManagerID] [int] NULL,
	[Birthday] [nvarchar](25) COLLATE Chinese_PRC_CI_AS NULL,
	[NativePlace] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
	[Group_GroupID] [int] NULL,
	[DestTimes] [int] NULL,
	[ChildGroup_ChildGroupID] [int] NULL,
	[MemoryDate] [datetime] NULL,
	[MainTel] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
	[Fax] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
	[GroupName] [nvarchar](128) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Telephones1](
	[TelephoneID] [int] IDENTITY(1,1) NOT NULL,
	[TelephoneNumber] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Customer_CustomID] [int] NULL,
 CONSTRAINT [PK_Telephones] PRIMARY KEY CLUSTERED 
(
	[TelephoneID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[Telephone] [nvarchar](128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[OtherTel] [nvarchar](128) COLLATE Chinese_PRC_CI_AS NULL,
	[IsRelated] [nvarchar](4) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[SmsTemplate_SmsTemplateID] [int] NULL,
	[PageCount] [int] NULL,
	[CallDate] [datetime] NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[MainNum] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[RoomNum] [int] NULL,
	[Customer_CustomID] [int] NULL,
	[RoomName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[wubaowang]

select distinct(a.Customid) as did,a.*,r.calldate
from Customer as a join Telephones as b on a.Customid=b.Customer_CustomID 
and a.customname like '%张%' 
join [Order] as r on a.Customid=r.Customer_CustomID

查询:did字段不重复和calldate字段以最大时间显示.