一个简单的记录筛选

songguozhi 2010-04-09 10:42:12
create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)



--希望得到以下结果(每个人一条记录,isable必须为1)
---------------------------
--张三 16 1
--李四 13 1
--王五 16 1
--麻子 3 1
...全文
142 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
lcqtgb 2010-04-09
  • 打赏
  • 举报
回复
看现这么多人顶,我也顶一下,献上自己的写法


select customername,max(state),isable from a3
where isable=1
group by customername, isable
order by customername

--小F-- 2010-04-09
  • 打赏
  • 举报
回复
流氓太多了
zhang286104091 2010-04-09
  • 打赏
  • 举报
回复
select distinct customername,max(state),isable from customer group by customername,isable having isable=1
htl258_Tony 2010-04-09
  • 打赏
  • 举报
回复

/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-09 10:42:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

---------------------------------*/
--> 生成测试数据表:tb

IF OBJECT_ID('[Customer]') IS NOT NULL
DROP TABLE [Customer]
GO
create table Customer(CustomerName nvarchar(2), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

--2005
;with t as
(
select rn=ROW_NUMBER()over(partition by CustomerName order by getdate()),*
from Customer where isable=1
)
select CustomerName,State,isable from t where rn=1
/*
CustomerName State isable
------------ ----------- ------
李四 8 1
麻子 3 1
王五 16 1
张三 8 1

(4 行受影响)
*/
--2000
select * from Customer t where State=(select top 1 State from Customer where CustomerName=t.CustomerName and isable=1) and isable=1
/*
CustomerName State isable
------------ ----------- ------
张三 8 1
李四 8 1
王五 16 1
麻子 3 1

(4 行受影响)
*/
obuntu 2010-04-09
  • 打赏
  • 举报
回复

select CustomerName,State,isable from Customer a
where
a.State=(select MAX(b.state) from Customer b
where a.CustomerName=b.CustomerName and b.isable=1
group by b.CustomerName )

比较菜。。
永生天地 2010-04-09
  • 打赏
  • 举报
回复
create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

select CustomerName,max(State),1 from
Customer
where isable=1
group by CustomerName

drop table Customer


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)


(所影响的行数为 1 行)

CustomerName
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
李四 13 1
麻子 3 1
王五 16 1
张三 16 1

(所影响的行数为 4 行)
htl258_Tony 2010-04-09
  • 打赏
  • 举报
回复

/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-09 10:42:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

---------------------------------*/
--> 生成测试数据表:tb

IF OBJECT_ID('[Customer]') IS NOT NULL
DROP TABLE [Customer]
GO
create table Customer(CustomerName nvarchar(2), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

;with t as
(
select rn=ROW_NUMBER()over(partition by CustomerName order by getdate()),*
from Customer where isable=1
)
select CustomerName,State,isable from t where rn=1
/*
CustomerName State isable
------------ ----------- ------
李四 8 1
麻子 3 1
王五 16 1
张三 8 1

(4 行受影响)
*/
黄_瓜 2010-04-09
  • 打赏
  • 举报
回复
create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)



select * from Customer a where
not exists(
select 1 from Customer where
CustomerName=a.CustomerName
and isable =1
and State>a.State
)
and isable =1
/*
CustomerName State isable
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------
张三 16 1
李四 13 1
王五 16 1
麻子 3 1

(4 行受影响)

*/
ws_hgo 2010-04-09
  • 打赏
  • 举报
回复
create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

select CustomerName,max(State) State
from
(
select * from Customer where isable=1
)tt
group by CustomerName

CustomerName State
-------------------------------------------------------------------------------------------------------------------------------- -----------
李四 13
麻子 3
王五 16
张三 16

(4 行受影响)
nianran520 2010-04-09
  • 打赏
  • 举报
回复
--or

select CustomerName,max(case isable when 1 then State else '' end) as State,1 as isable
from Customer
group by CustomerName
dawugui 2010-04-09
  • 打赏
  • 举报
回复
如果还存在多个字段,则用:

create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

select t.* from Customer t where isable = 1 and State = (select max(State) from Customer where isable = 1 and CustomerName = t.CustomerName)

select t.* from Customer t where isable = 1 and not exists (select 1 from Customer where isable = 1 and CustomerName = t.CustomerName and State > t.State)

drop table Customer

/*
CustomerName State isable
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
李四 13 1
麻子 3 1
王五 16 1
张三 16 1

(所影响的行数为 4 行)

*/
nianran520 2010-04-09
  • 打赏
  • 举报
回复
select * from Customer t
where state in (select max(state) from Customer where CustomerName = t.CustomerName and isable = 1)
张三 16 1
李四 13 1
王五 16 1
麻子 3 1
dawugui 2010-04-09
  • 打赏
  • 举报
回复
create table Customer(CustomerName nvarchar(128), State int, isable bit)

insert into Customer values(N'张三', 8, 1)
insert into Customer values(N'张三', 16, 1)
insert into Customer values(N'张三', 13, 1)
insert into Customer values(N'李四', 8, 1)
insert into Customer values(N'李四', 16, 0)
insert into Customer values(N'李四', 13, 1)
insert into Customer values(N'王五', 16, 1)
insert into Customer values(N'王五', 13, 1)
insert into Customer values(N'麻子', 3, 1)

select CustomerName , max(State) State , isable = 1 from Customer t where isable = 1 group by CustomerName

drop table Customer

/*
CustomerName State isable
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
李四 13 1
麻子 3 1
王五 16 1
张三 16 1

(所影响的行数为 4 行)

*/

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧