SQL分组问题，高手来看看

Question

有一现存的表格式和内容如下personid sex  name  birthday     salary 
   1      F   张三  1985/12/29    1000
   2      F   李四  1986/12/29    2000
   3      F   王五  1987/12/29    2000
   4      F   张三  1975/12/29    2000
   5      F   李四  1980/12/29    1000
   6      F   牛二  1960/12/29    1000
用SEX和salary来分组，希望得到下面2个表
                    表1                                            表2
personid sex  name  birthday     salary   ID                 ID   sex salary
   1      F   张三  1985/12/29    1000    1                   1    F    1000
   2      F   李四  1986/12/29    2000    2                   2    F    2000
   3      F   王五  1987/12/29    2000    2
   4      F   张三  1975/12/29    2000    2
   5      F   李四  1980/12/29    1000    1
   6      F   牛二  1960/12/29    1000    2
就是在原有的表上添加 ID 字段
分组后的表也添加 ID 字段
并使得它们进行关联高手来看看

Answer 1

select identity(1,1) as id,*
into #temp
from tb

Answer 2

--1
select t.* , id = (select count(distinct salary) from tb where salary < t.salary) + 1 from tb t
--2
select min(personid) id , sex , salary from tb group by sex , salary

Answer 3

--这样吧?? 如果测试数据不改的话 怎么按照SEX分组啊?
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 17:31:49
-- 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]([personid] int,[sex] varchar(1),[name] varchar(4),[birthday] datetime,[salary] int)
insert [tb]
select 1,'F','张三','1985/12/29',1000 union all
select 2,'F','李四','1986/12/29',2000 union all
select 3,'F','王五','1987/12/29',2000 union all
select 4,'F','张三','1975/12/29',2000 union all
select 5,'M','李四','1980/12/29',1000 union all
select 6,'M','牛二','1960/12/29',1000
--------------开始查询--------------------------select *,dense_rank()over(partition by sex order by salary) from [tb]
----------------结果----------------------------
/* personid    sex  name birthday                salary      
----------- ---- ---- ----------------------- ----------- --------------------
1           F    张三   1985-12-29 00:00:00.000 1000        1
2           F    李四   1986-12-29 00:00:00.000 2000        2
3           F    王五   1987-12-29 00:00:00.000 2000        2
4           F    张三   1975-12-29 00:00:00.000 2000        2
5           M    李四   1980-12-29 00:00:00.000 1000        1
6           M    牛二   1960-12-29 00:00:00.000 1000        1(6 行受影响)
*/

Answer 4

select * ,id=(select count(distinct(salary))+1 from tb where a.sex=sex and salary<a.salary ) from tb a

Answer 5

--考虑错误
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-09 17:31:49
-- 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]([personid] int,[sex] varchar(1),[name] varchar(4),[birthday] datetime,[salary] int)
insert [tb]
select 1,'F','张三','1985/12/29',1000 union all
select 2,'F','李四','1986/12/29',2000 union all
select 3,'F','王五','1987/12/29',2000 union all
select 4,'F','张三','1975/12/29',2000 union all
select 5,'F','李四','1980/12/29',1000 union all
select 6,'F','牛二','1960/12/29',1000
--------------开始查询--------------------------
select * , id = (select count(distinct salary) from tb where salary < t.salary) + 1 from tb tselect min(personid) as id , sex , salary from tb group by sex , salary
----------------结果----------------------------
/* 
(6 行受影响)
personid    sex  name birthday                salary      id
----------- ---- ---- ----------------------- ----------- -----------
1           F    张三   1985-12-29 00:00:00.000 1000        1
2           F    李四   1986-12-29 00:00:00.000 2000        2
3           F    王五   1987-12-29 00:00:00.000 2000        2
4           F    张三   1975-12-29 00:00:00.000 2000        2
5           F    李四   1980-12-29 00:00:00.000 1000        1
6           F    牛二   1960-12-29 00:00:00.000 1000        1(6 行受影响)id          sex  salary
----------- ---- -----------
1           F    1000
2           F    2000(2 行受影响)
*/

Answer 6

create table tb(personid int,sex varchar(10), name varchar(10), birthday varchar(10), salary int)
insert into tb values(1 , 'F' , '张三' , '1985/12/29' , 1000 )
insert into tb values(2 , 'F' , '李四' , '1986/12/29' , 2000 )
insert into tb values(3 , 'F' , '王五' , '1987/12/29' , 2000 )
insert into tb values(4 , 'F' , '张三' , '1975/12/29' , 2000 )
insert into tb values(5 , 'F' , '李四' , '1980/12/29' , 1000 )
insert into tb values(6 , 'F' , '牛二' , '1960/12/29' , 1000 )
go--1
select t.* , id = (select count(distinct salary) from tb where salary < t.salary) + 1 from tb t
/*
personid    sex        name       birthday   salary      id          
----------- ---------- ---------- ---------- ----------- ----------- 
1           F          张三         1985/12/29 1000        1
2           F          李四         1986/12/29 2000        2
3           F          王五         1987/12/29 2000        2
4           F          张三         1975/12/29 2000        2
5           F          李四         1980/12/29 1000        1
6           F          牛二         1960/12/29 1000        1（所影响的行数为 6 行）
*/--2
select min(personid) id , sex , salary from tb group by sex , salary
/*
id          sex        salary      
----------- ---------- ----------- 
1           F          1000
2           F          2000（所影响的行数为 2 行）
*/drop table tb

Answer 7

create table tb(personid int,sex varchar(10), name varchar(10), birthday varchar(10), salary int , id int)
insert into tb values(1 , 'F' , '张三' , '1985/12/29' , 1000 ,null)
insert into tb values(2 , 'F' , '李四' , '1986/12/29' , 2000 ,null)
insert into tb values(3 , 'F' , '王五' , '1987/12/29' , 2000 ,null)
insert into tb values(4 , 'F' , '张三' , '1975/12/29' , 2000 ,null)
insert into tb values(5 , 'F' , '李四' , '1980/12/29' , 1000 ,null)
insert into tb values(6 , 'F' , '牛二' , '1960/12/29' , 1000 ,null)
go--查询
--1
select t.* , id = (select count(distinct salary) from tb where salary < t.salary) + 1 from tb t
/*
personid    sex        name       birthday   salary      id          
----------- ---------- ---------- ---------- ----------- ----------- 
1           F          张三         1985/12/29 1000        1
2           F          李四         1986/12/29 2000        2
3           F          王五         1987/12/29 2000        2
4           F          张三         1975/12/29 2000        2
5           F          李四         1980/12/29 1000        1
6           F          牛二         1960/12/29 1000        1（所影响的行数为 6 行）
*/--2
select min(personid) id , sex , salary from tb group by sex , salary
/*
id          sex        salary      
----------- ---------- ----------- 
1           F          1000
2           F          2000（所影响的行数为 2 行）
*/--更新
update tb
set id = (select count(distinct salary) from tb where salary < t.salary) + 1 from tb tselect * from tb
/*
personid    sex        name       birthday   salary      id          
----------- ---------- ---------- ---------- ----------- ----------- 
1           F          张三         1985/12/29 1000        1
2           F          李四         1986/12/29 2000        2
3           F          王五         1987/12/29 2000        2
4           F          张三         1975/12/29 2000        2
5           F          李四         1980/12/29 1000        1
6           F          牛二         1960/12/29 1000        1（所影响的行数为 6 行）
*/drop table tb

Answer 8

--？
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([personid] int,[sex] varchar(1),[name] varchar(4),[birthday] datetime,[salary] int)
insert [tb]
select 1,'F','张三','1985/12/29',1000 union all
select 2,'F','李四','1986/12/29',2000 union all
select 3,'F','王五','1987/12/29',2000 union all
select 4,'F','张三','1975/12/29',2000 union all
select 5,'F','李四','1980/12/29',1000 union all
select 6,'F','牛二','1960/12/29',1000select *,
case [salary] when 1000 then 1 when 2000 then 2 end as id
into #temp
from [tb] tselect * from #tempselect distinct ID ,sex,salary 
from #tempdrop table #temp
---------------------
1 F 张三 1985-12-29 00:00:00.000 1000 1
2 F 李四 1986-12-29 00:00:00.000 2000 2
3 F 王五 1987-12-29 00:00:00.000 2000 2
4 F 张三 1975-12-29 00:00:00.000 2000 2
5 F 李四 1980-12-29 00:00:00.000 1000 1
6 F 牛二 1960-12-29 00:00:00.000 1000 1
--------------------------------
1 F 1000
2 F 2000

Answer 9

--> 测试时间：2009-12-09 17:32:39
--> 测试菜鸟：l8r
--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([personid] int,[sex] varchar(1),[name] varchar(4),[birthday] datetime,[salary] int)
insert [TB]
select 1,'F','张三','1985/12/29',1000 union all
select 2,'F','李四','1986/12/29',2000 union all
select 3,'F','王五','1987/12/29',2000 union all
select 4,'F','张三','1975/12/29',2000 union all
select 5,'F','李四','1980/12/29',1000 union all
select 6,'F','牛二','1960/12/29',1000select *,ID=cast(REVERSE(rtrim(salary)) as int)from TB/*
personid    sex  name birthday                                               salary      ID          
----------- ---- ---- ------------------------------------------------------ ----------- ----------- 
1           F    张三   1985-12-29 00:00:00.000                                1000        1
2           F    李四   1986-12-29 00:00:00.000                                2000        2
3           F    王五   1987-12-29 00:00:00.000                                2000        2
4           F    张三   1975-12-29 00:00:00.000                                2000        2
5           F    李四   1980-12-29 00:00:00.000                                1000        1
6           F    牛二   1960-12-29 00:00:00.000                                1000        1（所影响的行数为 6 行）*/select ID=(select count(distinct salary) from TB where salary<=t.salary),sex,salary from TB t group by sex,salary/*
ID          sex  salary      
----------- ---- ----------- 
1           F    1000
2           F    2000（所影响的行数为 2 行）*/drop table [TB]

Answer 10

--> 测试数据：[tb]create table [#tb]([personid] int,[sex] varchar(1),[name] varchar(4),[birthday] datetime,[salary] int)
insert [#tb]
select 1,'F','张三','1985/12/29',1000 union all
select 2,'F','李四','1986/12/29',2000 union all
select 3,'F','王五','1987/12/29',2000 union all
select 4,'F','张三','1975/12/29',2000 union all
select 5,'F','李四','1980/12/29',1000 union all
select 6,'F','牛二','1960/12/29',1000
--------------开始查询--------------------------select * ,case when salary=1000 then 1 else 2 end as id from [#tb]select case when salary=1000 then 1 else 2 end as id,sex ,min(salary) as salary from #tb group by salary,sex
/*
1 F 张三 1985-12-29 00:00:00.000 1000 1
2 F 李四 1986-12-29 00:00:00.000 2000 2
3 F 王五 1987-12-29 00:00:00.000 2000 2
4 F 张三 1975-12-29 00:00:00.000 2000 2
5 F 李四 1980-12-29 00:00:00.000 1000 1
6 F 牛二 1960-12-29 00:00:00.000 1000 1
1 F 1000
2 F 2000*/
drop table  [#tb]

Answer 11

多谢各位高手的解答，我没说清楚问题分组后的ID可以随意，比如下面的ID是11，12，和他一组的表也添加个相同的ID，使得他们成为关联表
11    F    1000
     11    F    张三    1985-12-29 00:00:00.000    1000    1
     11    F    李四    1980-12-29 00:00:00.000    1000    1 
     11    F    牛二    1960-12-29 00:00:00.000    1000    1
12    F    2000
     12    F    李四    1986-12-29 00:00:00.000    2000    2
     12    F    王五    1987-12-29 00:00:00.000    2000    2
     12    F    张三    1975-12-29 00:00:00.000    2000    2在麻烦各位高手下

Answer 12

select *,[id]=(select count(distinct [salary]) from [tb] b where a.[sex] = b.[sex] and a.[salary] >= b.[salary])
from [tb] aselect distinct [id],[sex],[salary] from
(
select *,[id]=(select count(distinct [salary]) from [tb] b where a.[sex] = b.[sex] and a.[salary] >= b.[salary])
from [tb] a
)c