27,579
社区成员
发帖
与我相关
我的任务
分享
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
/*---------------------------------
-- 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 行受影响)
*/
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 )
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 行)
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 行受影响)
*/
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 行受影响)
--or
select CustomerName,max(case isable when 1 then State else '' end) as State,1 as isable
from Customer
group by CustomerName
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 行)
*/
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
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 行)
*/