求查询SQL语句

Question

表数据如下，
EmployeesNo，WindowsID,   Status
1            a            0
2                         0 
3            a            1
4            b            1
5            c            1 
6            c            0 
7            d            0
8            e            1
9            d            0
10                        0
11           a            0
说明：
EmployeesNo字段不为空，且不重复。
WindowsID可能为空，可能重复，
Status字段不为空，要么为0，要么为1.
WindowsID相同的字段最多只能有一个Status为1，可以全部为0。
所有3个字段都是字符串型的数据。需求：
一个SQL查询语句，查询结果仍然包含EmployeesNo，WindowsID,  Status三个字段，
不包含WindowsID为空的记录，
如果有WindowsID重复的记录，则只取其中的1条：如果存在Status为1的，则取Status为1的记录；否则任意取一条。比如上例中的数据，结果就是：
EmployeesNo，WindowsID,   Status 
3            a            1
4            b            1
5            c            1 
7            d            0
8            e            1求以上功能的查询语句。

Answer 1

select t.* from tb t where WindowsID <> '' and Status = (select max(Status) from tb where WindowsID = t.WindowsID and WindowsID <> '') order by t.WindowsID

Answer 2

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-11-26 16:09:33
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (EmployeesNo int,WindowsID varchar(1),Status int)
INSERT INTO @tb
SELECT 1,'a',0 UNION ALL
SELECT 2,null,0 UNION ALL
SELECT 3,'a',1 UNION ALL
SELECT 4,'b',1 UNION ALL
SELECT 5,'c',1 UNION ALL
SELECT 6,'c',0 UNION ALL
SELECT 7,'d',0 UNION ALL
SELECT 8,'e',1 UNION ALL
SELECT 9,'d',0 UNION ALL
SELECT 10,null,0 UNION ALL
SELECT 11,'a',0--SQL查询如下:SELECT *
FROM @tb AS A
WHERE NULLIF(WindowsID,'') IS NOT NULL
    AND EmployeesNo = (SELECT TOP 1 EmployeesNo FROM @tb
                      WHERE A.WindowsID = WindowsID
                      ORDER BY Status DESC)/*
EmployeesNo WindowsID Status
----------- --------- -----------
3           a         1
4           b         1
5           c         1
8           e         1
9           d         0(5 行受影响)
*/

Answer 3

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/26
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB table([EmployeesNo] int,WindowsID varchar(1),[Status] int)
insert @TB
select 1,'a',0 union all
select 2,'',0 union all
select 3,'a',1 union all
select 4,'b',1 union all
select 5,'c',1 union all
select 6,'c',0 union all
select 7,'d',0 union all
select 8,'e',1 union all
select 9,'d',0 union all
select 10,'',0 union all
select 11,'a',0select * 
from @TB a
where (WindowsID <>'') and
not exists(select 1 from @tb where a.WindowsID=WindowsID and a.status<status)--测试结果:
/*
EmployeesNo WindowsID Status
----------- --------- -----------
3           a         1
4           b         1
5           c         1
7           d         0
8           e         1
9           d         0(6 row(s) affected)
*/

Answer 4

create table tb(EmployeesNo int,WindowsID varchar(10),  Status varchar(10))
insert into tb values('1' ,           'a' ,           0)
insert into tb values('2' ,           ''  ,           0)
insert into tb values('3' ,           'a' ,           1)
insert into tb values('4' ,           'b' ,           1)
insert into tb values('5' ,           'c' ,           1)
insert into tb values('6' ,           'c' ,           0)
insert into tb values('7' ,           'd' ,           0)
insert into tb values('8' ,           'e' ,           1)
insert into tb values('9' ,           'd' ,           0)
insert into tb values('10',           ''  ,           0)
insert into tb values('11',           'a' ,           0)
goselect m.* from 
(
  select t.* from tb t where WindowsID <> '' and Status = (select max(Status) from tb where WindowsID = t.WindowsID and WindowsID <> '') 
) m where EmployeesNo = 
(
  select min(EmployeesNo) from (select t.* from tb t where WindowsID <> '' and Status = (select max(Status) from tb where WindowsID = t.WindowsID and WindowsID <> '') ) n
  where n.WindowsID = m.WindowsID
)
order by m.WindowsIDdrop table tb/*
EmployeesNo WindowsID  Status     
----------- ---------- ---------- 
3           a          1
4           b          1
5           c          1
7           d          0
8           e          1（所影响的行数为 5 行）
*/

Answer 5

create table tb(EmployeesNo int,WindowsID varchar(10),  Status varchar(10))
insert into tb values('1' ,           'a' ,           0)
insert into tb values('2' ,           ''  ,           0)
insert into tb values('3' ,           'a' ,           1)
insert into tb values('4' ,           'b' ,           1)
insert into tb values('5' ,           'c' ,           1)
insert into tb values('6' ,           'c' ,           0)
insert into tb values('7' ,           'd' ,           0)
insert into tb values('8' ,           'e' ,           1)
insert into tb values('9' ,           'd' ,           0)
insert into tb values('10',           ''  ,           0)
insert into tb values('11',           'a' ,           0)
goselect t.* from tb t where WindowsID <> '' and EmployeesNo = (select top 1 EmployeesNo from tb where WindowsID = t.WindowsID and WindowsID <> '' order by Status desc , EmployeesNo) order by t.WindowsIDdrop table tb/*
EmployeesNo WindowsID  Status     
----------- ---------- ---------- 
3           a          1
4           b          1
5           c          1
7           d          0
8           e          1（所影响的行数为 5 行）*/

Answer 6

--> 测试数据: @tb
declare @tb table (EmployeesNo int,WindowsID varchar(1),Status int)
insert into @tb
select 1,'a',0 union all
select 2,null,0 union all
select 3,'a',1 union all
select 4,'b',1 union all
select 5,'c',1 union all
select 6,'c',0 union all
select 7,'d',0 union all
select 8,'e',1 union all
select 9,'d',0 union all
select 10,null,0 union all
select 11,'a',0select * from @tb where status=1 
union all 
select * from @tb t
where not exists (select * from @tb where WindowsID=t.WindowsID and EmployeesNo>t.EmployeesNo  and WindowsID is not null and WindowsID not in(select distinct WindowsID from @tb where status =1 )) 
and WindowsID not in(select distinct WindowsID from @tb where status=1 ) and WindowsID is not null 
order by EmployeesNo
EmployeesNo WindowsID Status
----------- --------- -----------
3           a         1
4           b         1
5           c         1
8           e         1
9           d         0(5 行受影响)

Answer 7

这下应该对了-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/26
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB table([EmployeesNo] int,WindowsID varchar(1),[Status] int)
insert @TB
select 1,'a',0 union all
select 2,'',0 union all
select 3,'a',1 union all
select 4,'b',1 union all
select 5,'c',1 union all
select 6,'c',0 union all
select 7,'d',0 union all
select 8,'e',1 union all
select 9,'d',0 union all
select 10,'',0 union all
select 11,'a',0select [EmployeesNo], WindowsID, [Status]
from (
select *,ID=ROW_NUMBER() OVER (PARTITION BY WindowsID ORDER BY WindowsID,EmployeesNo)
from @TB a where (WindowsID <>'') and not exists(select 1 from @tb where a.WindowsID=WindowsID  and a.status<status)
) p
where P.ID = 1--测试结果:
/*
EmployeesNo WindowsID Status
----------- --------- -----------
3           a         1
4           b         1
5           c         1
7           d         0
8           e         1(5 row(s) affected)*/

Answer 8

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-26 16:10:50
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([EmployeesNo] int,[WindowsID] varchar(1),[Status] int)
insert [tb]
select 1,'a',0 union all
select 2,null,0 union all
select 3,'a',1 union all
select 4,'b',1 union all
select 5,'c',1 union all
select 6,'c',0 union all
select 7,'d',0 union all
select 8,'e',1 union all
select 9,'d',0 union all
select 10,null,0 union all
select 11,'a',0
--------------开始查询--------------------------
select
   * 
from 
  tb t
where
  not exists (select * from tb where WindowsID=t.WindowsID and EmployeesNo>t.EmployeesNo  and WindowsID is not null and WindowsID not in(select distinct WindowsID from tb where status =1 )) 
and 
  WindowsID 
not in
  (select distinct WindowsID from tb where status=1 ) and WindowsID is not null or status=1 
order by 
   EmployeesNo
 
----------------结果----------------------------
/* EmployeesNo WindowsID Status
----------- --------- -----------
3           a         1
4           b         1
5           c         1
8           e         1
9           d         0(5 行受影响)
*/

Answer 9

declare @tb table (employeesno int,windowsid nvarchar(10),status int)
insert into @tb select 1,'a',0
     union all select 2,null,0
     union all select 3,'a',1
     union all select 4,'b',1
     union all select 5,'c',1
     union all select 6,'c',0
     union all select 7,'d',0
     union all select 8,'e',1
     union all select 9,'d',0
     union all select 10,null,0
     union all select 11,'a',0
select * from @tb a where 
           employeesno=(select top 1 employeesno from 
                @tb where a.windowsid=windowsid order by status desc,employeesno asc)/*
(11 行受影响)
employeesno windowsid  status
----------- ---------- -----------
3           a          1
4           b          1
5           c          1
7           d          0
8           e          1(5 行受影响)
*/

Answer 10

DECLARE @tb TABLE (EmployeesNo varchar(10),WindowsID varchar(1),Status varchar(10))
INSERT INTO @tb
SELECT '1','a','0' UNION ALL
SELECT '2',null,'0' UNION ALL
SELECT '3','a','1' UNION ALL
SELECT '4','b','1' UNION ALL
SELECT '5','c','1' UNION ALL
SELECT '6','c','0' UNION ALL
SELECT '7','d','0' UNION ALL
SELECT '8','e','1' UNION ALL
SELECT '9','d','0' UNION ALL
SELECT '10',null,'0' UNION ALL
SELECT '11','a','0';with cte as
(
 select *,rn=row_number() over(PARTITION by WindowsID order by charindex(status,'10'))
 from @tb where WindowsID is not null
)
select EmployeesNo ,WindowsID,Status  from cte where rn=1
/*
EmployeesNo WindowsID Status
----------- --------- ----------
3           a         1
4           b         1
5           c         1
7           d         0
8           e         1*/

Answer 11

DECLARE @tb TABLE (EmployeesNo varchar(10),WindowsID varchar(1),Status varchar(10))
INSERT INTO @tb
SELECT '1','a','0' UNION ALL
SELECT '2',null,'0' UNION ALL
SELECT '3','a','1' UNION ALL
SELECT '4','b','1' UNION ALL
SELECT '5','c','1' UNION ALL
SELECT '6','c','0' UNION ALL
SELECT '7','d','0' UNION ALL
SELECT '8','e','1' UNION ALL
SELECT '9','d','0' UNION ALL
SELECT '10',null,'0' UNION ALL
SELECT '11','a','0'select *
from @tb k
where WindowsID is not null and
 EmployeesNo =(select top 1 EmployeesNo from @tb where k.WindowsID=WindowsID order by CHARINDEX(Status,'10'))
/*
EmployeesNo WindowsID Status
----------- --------- ----------
3           a         1
4           b         1
5           c         1
8           e         1
9           d         0
*/