表 worksUser Title Image Islock
张三 274 34534 3
张三 224 34534 3
李四 284 34534 3
李四 254 34534 3
张三 294 34534 3我这样写不行?搞了一下午
写到这了 select user from works where IsLock=3 group by User 。我想要这样的结果:
表 works
张三 274 34534 3
李四 284 34534 3大家帮我写一条语句,谢谢`~
张三 274 34534 3
张三 224 34534 3
李四 284 34534 3
李四 254 34534 3
张三 294 34534 3我这样写不行?搞了一下午
写到这了 select user from works where IsLock=3 group by User 。我想要这样的结果:
表 works
张三 274 34534 3
李四 284 34534 3大家帮我写一条语句,谢谢`~
解决方案 »
- TCP同步连续传输大量数据出现数据不一致现象,求解决方法。
- winform程序,想增加短信群发功能,请推荐个正规的短信平台?急
- DataGridView可以指定某列的事件吗?
- keybd event持续按住某一键
- 资源文件如何打包才能不放到exe里面
- 郁闷了,这是段什么代码呢?XML?还是html?哪位给解释一下?
- 哪位能把这个PDF转化为WORD文件?http://csinjoin.hnu.cn/celeron/a.pdf
- LumiSoft.NET发送邮件为什么都是无主题?求指点!
- 关于调试DLL的问题,超级简单!!!!请帮忙!!!!!!
- 关于如何使用类"FileInfo"的问题?
- 关于 OPenGL场景中两个物体操作
- 获取treeView先中CheckBox的所有子节点
create table #TT
(
UserName varchar(20),
Title varchar(20),
Image varchar(20),
Islock int
)
insert into #TT select '张三','274','34534',3
union all select '张三','224','34534',3
union all select '李四','284','34534',3
union all select '李四','254','34534',3
union all select '张三','294','34534',3select UserName,max(Title) Title,[Image],Islock from #TT group by UserName,[Image],Islock UserName Title Image Islock
-------------------- -------------------- -------------------- -----------
李四 284 34534 3
张三 294 34534 3
select works.* from works inner join (
select [user] , max(Title) as Title from works where IsLock=3 group by User ) a
on a.[user] = works.[user] and a.Title = works.Title
你的那个Title是最大值还是最小值啊
insert @t select '张三',274,34534,3
union all select '张三',224,34534,3
union all select '李四',284,34534,3
union all select '李四',254,34534,3
union all select '张三',294,34534,3select * from @t a
where not exists(select 1 from @t where [User]=a.[User] and title>a.title)
/*(所影响的行数为 5 行)User Title Image Islock
---------- ----------- ----------- -----------
李四 284 34534 3
张三 294 34534 3(所影响的行数为 2 行)
*/
(
ID int identity(1,1) primary key,
UserName varchar(20),
Title varchar(20),
Image varchar(20),
Islock int
)
insert into #TT1 select '张三','274','34534',3
union all select '张三','224','34534',3
union all select '李四','284','34534',3
union all select '李四','254','34534',3
union all select '张三','294','34534',3select T1.UserName,T1.Title,T1.[Image],T1.Islock from #TT1 T1 right join
(
select min(ID) ID,UserName from #TT1
group by UserName
) T2
on T2.ID=T1.ID
UserName Title Image Islock
-------------------- -------------------- -------------------- -----------
李四 284 34534 3
张三 274 34534 3
insert @t select '张三',274,34534,3
union all select '张三',224,34534,3
union all select '李四',284,34534,3
union all select '李四',254,34534,3
union all select '张三',294,34534,3;with tb as(
select id=row_number() over(order by Islock),* from @t)
select [User] ,Title ,[Image],Islock from tb a
where not exists(select 1 from tb where a.[User]=[User] and id<a.id)
--(5 行受影响)
--User Title Image Islock
------------ ----------- ----------- -----------
--张三 274 34534 3
--李四 284 34534 3
--
--(2 行受影响)
select * from works where([user]='张三' and Title=274) or ([user]='李四' and Title=284)
这就是你要的结果,通过测试了的哦!
这条语句可以,但是要是Title都一样?
FROM works AS a
WHERE not exists(select 1 from works where [User]=a.[User] and title>a.title) and islock=3 and Typelist=17大家测试的时候把数据表里面Typelist里面的18改成17试试~~,郁闷死~~
declare @t table([User] varchar(20), Title int , [Image] int , Islock int )
insert into @t values('张三' , 274 , 34534 , 3)
insert into @t values('张三' , 224 , 34534 , 3)
insert into @t values('李四' , 284 , 34534 , 3)
insert into @t values('李四' , 254 , 34534 , 3)
insert into @t values('张三' , 294 , 34534 , 3)
select a.[User], (select top 1 Title from @t where [User] = a.[User]) as Title,
(select top 1 [Image] from @t where [User] = a.[User]) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User]) as [Islock]
from @t a group by a.[User]
lz是不是想要这样的结果
declare @t table([User] varchar(20), Title int , [Image] int , Islock int )
insert into @t values('张三' , 274 , 34534 , 3)
insert into @t values('张三' , 224 , 34534 , 3)
insert into @t values('李四' , 284 , 34534 , 3)
insert into @t values('李四' , 254 , 34534 , 3)
insert into @t values('张三' , 294 , 34534 , 3)select a.[User], (select top 1 Title from @t where [User] = a.[User]) as Title,
(select top 1 [Image] from @t where [User] = a.[User]) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User]) as [Islock]
from @t a where a.islock=3 group by a.[User]
类似这样的。楼上说错了
(select top 1 [Image] from @t where [User] = a.[User]) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User]) as [Islock]
from @t a where a.islock=3 and a.typelist=17 group by a.[User] 这样提示不行啊?
declare @t table([User] varchar(20), Title int , [Image] int , Islock int, typelist int )
insert into @t values('张三' , 274 , 34534 , 3 , 17)
insert into @t values('张三' , 224 , 34534 , 3, 17)
insert into @t values('李四' , 284 , 34534 , 3, 17)
insert into @t values('李四' , 254 , 34534 , 3, 17)
insert into @t values('张三' , 294 , 34534 , 3, 17)select a.[User], (select top 1 Title from @t where [User] = a.[User]) as Title,
(select top 1 [Image] from @t where [User] = a.[User]) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User]) as [Islock] ,
(select top 1 [typelist] from @t where [User] = a.[User]) as [typelist]
from @t a where a.islock=3 and a.typelist =17 group by a.[User]
正常的啊,你写的也没有问题啊,在检查一下
select a.[User] ,
(select top 1 Title from @t where [User] = a.[User] and islock=3 and typelist=17 ) as Title,
(select top 1 [Image] from @t where [User] = a.[User] and islock=3 and typelist=17) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User] and islock=3 and typelist=17 ) as [Islock] ,
(select top 1 [typelist] from @t where [User] = a.[User] and islock=3 and typelist=17 ) as [typelist]
from @t a where a.islock=3 and a.typelist =17 group by a.[User]
select * from (select a.[User], (select top 1 Title from @t where [User] = a.[User]) as Title,
(select top 1 [Image] from @t where [User] = a.[User]) as [Image] ,
(select top 1 [Islock] from @t where [User] = a.[User]) as [Islock] ,
(select top 1 [typelist] from @t where [User] = a.[User]) as [typelist]
from @t a group by a.[User]) as b where b.typelist=17 这样可以,但是提示数据错误,郁闷啊~~