select * from A1 as a,B2 as b where a.iUserID=b.iID and not exists(select 1 from A1 where iUserID=a.iUserID and iID>a.iID)
解决方案 »
- SQL和T-SQL的区别是什么?
- 菜鸟问题:sql2005查询里执行建库脚本成功,select能看见表,为什么左面对象里无所建的表?
- 授权失败
- 安装sql2000出现command line option syntax error type commanrd/? for help出错,什么原因?
- Microsoft OLE DB Provider for ODBC Drivers 错误 '80004005'
- 难倒一大片公司技术人员的SQL查询,求教CSDN达人!
- 请教个SQL查询问题?
- 怎样连接SQL Server2000?
- 请教高手把两个表合并的写法
- 求解一道简单的触发器问题
- SQL选择更新,怎么
- exec在复杂语句中加单引号'的问题
if not object_id('A1') is null
drop table A1
Go
Create table A1([iID] int,[cProducts] nvarchar(3),[iUserID] int)
Insert A1
select 1,N'Mca',1 union all
select 2,N'Mcb',2 union all
select 3,N'Mcc',1 union all
select 4,N'Mcd',3 union all
select 5,N'Mce',1 union all
select 6,N'Mcf',3 union all
select 7,N'Mcf',2
Go--> -->
if not object_id('B2') is null
drop table B2
Go
Create table B2([iID] int,[cUserNo] nvarchar(1))
Insert B2
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c'
Go
select * from A1 as a,B2 as b where a.iUserID=b.iID and not exists(select 1 from A1 where iUserID=a.iUserID and iID<a.iID)
(7 個資料列受到影響)(3 個資料列受到影響)
iID cProducts iUserID iID cUserNo
----------- --------- ----------- ----------- -------
1 Mca 1 1 a
2 Mcb 2 2 b
4 Mcd 3 3 c(3 個資料列受到影響)
--取大小值
/******************************************************************************************************************************************************
Name相同ID最小或最大的记录整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
--1、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时,效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)方法9:
select * from #T a where ID in(select min(ID) from #T group by Name)--SQL2005:方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1(2 行受影响)
*/
--2、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)方法9:
select * from #T a where ID in(select max(ID) from #T group by Name)--SQL2005:方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID生成结果2:
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2(2 行受影响)
*/
select *
from (select * from A1 t where not exists(select 1 from A1 where iUserID=t.iUserID and iID<t.iID))
as a join B2 as b on a.iUserID=b.iID
drop table A1
Go
Create table A1([iID] int,[cProducts] nvarchar(3),[iUserID] int)
Insert A1
select 1,N'Mca',1 union all
select 2,N'Mcb',2 union all
select 3,N'Mcc',1 union all
select 4,N'Mcd',3 union all
select 5,N'Mce',1 union all
select 6,N'Mcf',3 union all
select 7,N'Mcf',2
Go--> -->
if not object_id('B2') is null
drop table B2
Go
Create table B2([iID] int,[cUserNo] nvarchar(1))
Insert B2
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c'
Goselect *
from (select * from A1 t where not exists(select 1 from A1 where iUserID=t.iUserID and iID<t.iID))
as a join B2 as b on a.iUserID=b.iID /*
iID cProducts iUserID iID cUserNo
----------- --------- ----------- ----------- -------
1 Mca 1 1 a
2 Mcb 2 2 b
4 Mcd 3 3 c(3 行受影响)
*/
select a.iID,a.iUserID,a.cProductName,b.cUserNo,d.cCompanyName from MSM_Products a,MSM_User b,MSM_ProductClass c,MSM_Company d
where a.iUserID=b.iID and a.iState=1 and b.iState=1 and a.iUserID=c.iUserID and c.iState=1 and d.iUserID=b.iID
and not exists(select 1 from MSM_Products where iUserID=a.iUserID and iUserID>a.iUserID)order by a.dorder desc
根据上面的意思,我写了自己的这个,发现,还有重复的 iUserID 咱办?