27,582
社区成员
发帖
与我相关
我的任务
分享一个简单的记录筛选
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
...全文
请发表友善的回复…
发表回复
lcqtgb 2010-04-09
- 打赏
- 举报
看现这么多人顶，我也顶一下，献上自己的写法
select customername,max(state),isable from a3
where isable=1
group by customername, isable
order by customername
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 行）
*/