我在sqlserver2000数据库中有两张表,webcompany(公司表)和webbroker(经济人表),这两张表是通过webcompany表的commpanyId和webbroker表的substring(broker.brokerId,0,5)关联起来的,其中经纪人中有level(积分)字段我要查询的条件是:查询出每个公司积分最高的经纪人,并显示其所有信息。
这是我写的sql语句:
select w.*,webCompany.* from web_broker w,web_company webCompany ,
(SELECT substring(broker.brokerId,0,5) as companyId ,max(level) as mlevel FROM Web_Broker broker GROUP BY substring(broker.brokerId,0,5) ) b2
where substring(w.brokerId,0,5) = b2.companyId AND b2.mlevel = w.level
AND webCompany.companyId=substring(w.brokerId,0,5)
但结果有点不对,原因是要是某个公司最高积分的经济人有两个以上的话,得出的数据就会出现两个以上的相同公司的经纪人,这不是我要的结果,只要留下某个公司一个最高积分的经纪人就可以了,请问有没有什么方法去掉其他经济人啊?
下面是我两张表的结构CREATE TABLE [Web_Company] (
[CompanyID] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Telephone] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Fax] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Contacter] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Pic] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Logo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_Agent_Company_CreateDate] DEFAULT (getdate()),
[District_id] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Block_Id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Description] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[IsAdmin] [bit] NULL CONSTRAINT [DF_Web_Company_IsAdmin] DEFAULT (0),
[IsDel] [bit] NULL CONSTRAINT [DF_Web_Company_IsDel] DEFAULT (0),
[Level] [int] NULL)
CREATE TABLE [Web_Broker] (
[BrokerID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[RoleID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[DepartmentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sex] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Address] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Account] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Position] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[IsPermitted] [bit] NULL ,
[IsEmployed] [bit] NULL ,
[IsLogined] [bit] NULL ,
[FixedPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MobilePhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EmailAddr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Level] [int] NULL CONSTRAINT [DF_Web_Broker_Level] DEFAULT (0),
[IDNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Photo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Certificate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_Agent_Broker_CreateDate] DEFAULT (getdate()),
[LevelGrade] [int] NULL) 请大家帮帮忙?谢谢
这是我写的sql语句:
select w.*,webCompany.* from web_broker w,web_company webCompany ,
(SELECT substring(broker.brokerId,0,5) as companyId ,max(level) as mlevel FROM Web_Broker broker GROUP BY substring(broker.brokerId,0,5) ) b2
where substring(w.brokerId,0,5) = b2.companyId AND b2.mlevel = w.level
AND webCompany.companyId=substring(w.brokerId,0,5)
但结果有点不对,原因是要是某个公司最高积分的经济人有两个以上的话,得出的数据就会出现两个以上的相同公司的经纪人,这不是我要的结果,只要留下某个公司一个最高积分的经纪人就可以了,请问有没有什么方法去掉其他经济人啊?
下面是我两张表的结构CREATE TABLE [Web_Company] (
[CompanyID] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Telephone] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Fax] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[Email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Contacter] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Pic] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Logo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_Agent_Company_CreateDate] DEFAULT (getdate()),
[District_id] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[Block_Id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Description] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[IsAdmin] [bit] NULL CONSTRAINT [DF_Web_Company_IsAdmin] DEFAULT (0),
[IsDel] [bit] NULL CONSTRAINT [DF_Web_Company_IsDel] DEFAULT (0),
[Level] [int] NULL)
CREATE TABLE [Web_Broker] (
[BrokerID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[RoleID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[DepartmentID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sex] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Address] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Account] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Position] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[IsPermitted] [bit] NULL ,
[IsEmployed] [bit] NULL ,
[IsLogined] [bit] NULL ,
[FixedPhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MobilePhone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[EmailAddr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Level] [int] NULL CONSTRAINT [DF_Web_Broker_Level] DEFAULT (0),
[IDNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Photo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Certificate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_Agent_Broker_CreateDate] DEFAULT (getdate()),
[LevelGrade] [int] NULL) 请大家帮帮忙?谢谢
from (select top 1 * from [Web_Company] v ,[Web_Broker] b
where v.[commpanyId]=substring([Web_Broker].brokerId,0,5 order by b.Level desc) t
group by T.commpanyId
from (select top 1 b.* ,v.[companyID]
from [Web_Company] v ,[Web_Broker] b
where [companyId]=substring(brokerID,0,5) order by b.[Level] desc) o
group by [companyID]
--假如RoleID是每个经济人编号的话,或者用经济人的name
select wb.*,wc.* from web_broker wb,webcompany wc,
(select companyid=substring(brokerId,0,5),RoleID=min(RoleID) from
(select * from web_broker a where not exists(select 1 from web_broker where brokerId=a.brokerId and level>a.level)) b
group by b.substring(brokerId,0,5)) wd
where substring(wb.brokerId,0,5)=wc.companyid and wc.companyid=wd.companyid and wb.RoleID=wc.roleid
--modify
--假如RoleID是每个经济人编号的话,或者用经济人的name
select wb.*,wc.* from web_broker wb,webcompany wc,
(select companyid=substring(brokerId,0,5),RoleID=min(RoleID) from
(select * from web_broker a where not exists(select 1 from web_broker where brokerId=a.brokerId and level>a.level)) b
group by b.substring(brokerId,0,5)) wd
where substring(wb.brokerId,0,5)=wc.companyid and wc.companyid=wd.companyid and wb.RoleID=wd.roleid