现在有表1{trans}d的部分字段和数据如下:[dbo].[trans](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CreateTime] [datetime] NULL,
[Quantity] [int] NULL,
[ById] [int] NULL
}
表2 :
transRelate(
byid int,
clientId int
)
表3:
clients{
clientId int,
Clientname nvarchar(20)
}
也就是表1和表3存在多对多的关系.现在我进行如下查询:
select t.Name,t.CreateTime,t.Quantity,c.ClientName
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
得到如下的结果:Name CreateTime Quantity Clientname
-------------------- ----------------------- ----------- --------------------
dfgdg 2006-01-01 00:00:00.000 20 aa
fghfg 2006-04-02 00:00:00.000 30 dd
trhrh 2006-05-09 00:00:00.000 40 gg
fjhjkj 2006-06-02 00:00:00.000 50 jj我现在想把上结果改为如下:Name CreateTime Quantity Clientname ClientName1
-------------------- ----------------------- ----------- --------------------
dfgdg 2006-01-01 00:00:00.000 20 aa
fghfg 2006-04-02 00:00:00.000 30 aa dd
trhrh 2006-05-09 00:00:00.000 40 dd gg
fjhjkj 2006-06-02 00:00:00.000 50 gg jj请问能用查询直接修改吗?应该怎样修改呢!?
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[CreateTime] [datetime] NULL,
[Quantity] [int] NULL,
[ById] [int] NULL
}
表2 :
transRelate(
byid int,
clientId int
)
表3:
clients{
clientId int,
Clientname nvarchar(20)
}
也就是表1和表3存在多对多的关系.现在我进行如下查询:
select t.Name,t.CreateTime,t.Quantity,c.ClientName
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
得到如下的结果:Name CreateTime Quantity Clientname
-------------------- ----------------------- ----------- --------------------
dfgdg 2006-01-01 00:00:00.000 20 aa
fghfg 2006-04-02 00:00:00.000 30 dd
trhrh 2006-05-09 00:00:00.000 40 gg
fjhjkj 2006-06-02 00:00:00.000 50 jj我现在想把上结果改为如下:Name CreateTime Quantity Clientname ClientName1
-------------------- ----------------------- ----------- --------------------
dfgdg 2006-01-01 00:00:00.000 20 aa
fghfg 2006-04-02 00:00:00.000 30 aa dd
trhrh 2006-05-09 00:00:00.000 40 dd gg
fjhjkj 2006-06-02 00:00:00.000 50 gg jj请问能用查询直接修改吗?应该怎样修改呢!?
经过上关联查询得到的结果并不一定唯一!如有可能出现如下的结果"
Name CreateTime Quantity Clientname
-------------------- ----------------------- ----------- --------------------
dfgdg 2006-01-01 00:00:00.000 20 aa
dfgdg 2006-01-01 00:00:00.000 20 bb
dfgdg 2006-01-01 00:00:00.000 20 cc
fghfg 2006-04-02 00:00:00.000 30 dd
fghfg 2006-04-02 00:00:00.000 30 ee
fghfg 2006-04-02 00:00:00.000 30 ff
trhrh 2006-05-09 00:00:00.000 40 gg
trhrh 2006-05-09 00:00:00.000 40 hh
fjhjkj 2006-06-02 00:00:00.000 50 ii
fjhjkj 2006-06-02 00:00:00.000 50 jj
你要的结果是不是clientname为clientname1,而clientname取前一个比自己小的quantity值的clientname?
要补充结果得到前面结果查询语句应该为:
select t.Name,t.CreateTime,t.Quantity,min(c.ClientName)
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
group by t.Name,t.CreateTime,t.Quantity
要得到你真正想要的结果,查询语句可能为:
select a.name,a.CreateTime,a.Quantity,isnull(b.clientname,'') as clientname,a.clientname as clientname1
from
(select t.Name,t.CreateTime,t.Quantity,min(c.ClientName) as clientname
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
group by t.Name,t.CreateTime,t.Quantity) a,
(select t.Name,t.CreateTime,t.Quantity,min(c.ClientName) as clientname
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
group by t.Name,t.CreateTime,t.Quantity) b
where b.Quantity *=(
select max(Quantity)
from (select t.Name,t.CreateTime,t.Quantity,min(c.ClientName) as clientname
from Clients c,transRelate tr,trans t
where c.ClientId = tr.clientId and t.ById = tr.ById
group by t.Name,t.CreateTime,t.Quantity) c
where c.Quantity<a.Quantity)