求一SQL语句：两个表综合

Question

表一Person
结构如下
PER_NAME    TOTAL_QTY    TOP1_NAME    TOP1_QTY    TOP2_NAME    TOP2_QTY    TOP3_NAME    TOP3_QTY    
A           50           APPLE        10          PEAR         8           BANANER      5
B           12           PEAR         8           ORANGE       4           (空)         (空)
C           60           APPLE        20          PEAR         18          BANANER      8
...表二FRUIT
结构如下
PER_NAME    FRUIT_NAME    FRUIT_QTY
A           APPLE         10
A           PEAR          8
B           PEAR          12
...我的问题如下
表二的内容已经生成了，表一中除了PER_NAME外其它都是空的，需要通过表二来获取，表一表二的PER_NAME是同一字段，
TOTAL_QTY是同一人所有水果数量的和，TOP1是最多的水果，TOP2是第二多，TOP3是第三多，如果没有则空显示。不知道我有没有叙述清楚，请高人指点

Answer 1

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html

Answer 2

--环境
create table tab1
(
PER_NAME  varchar(10),
TOTAL_QTY int,
TOP1_NAME  varchar(20), 
TOP1_QTY int,
TOP2_NAME  varchar(20), 
TOP2_QTY int,
TOP3_NAME varchar(20), 
TOP3_QTY int
)create table tab2
(
PER_NAME varchar(10),
FRUIT_NAME varchar(20),
FRUIT_QTY int
)--插入数据
insert into tab1(per_name) select 'A'
insert into tab1(per_name) select 'B'
insert into tab1(per_name) select 'C'insert into tab2 select 'A','APPLE',10
insert into tab2 select 'A','PEAR',8
insert into tab2 select 'A','BANANER',5
insert into tab2 select 'B','PEAR',8
insert into tab2 select 'B','ORANGE',4
insert into tab2 select 'C','APPLE',20
insert into tab2 select 'C','PEAR',18
insert into tab2 select 'C','BANANER',8--更新
update t1
set t1.TOTAL_QTY = t2.TOTAL_QTY,
t1.TOP1_NAME = t2.TOP1_NAME, t1.TOP1_QTY = t2.TOP1_QTY,
t1.TOP2_NAME = t2.TOP2_NAME, t1.TOP2_QTY = t2.TOP2_QTY,
t1.TOP3_NAME = t2.TOP3_NAME, t1.TOP3_QTY = t2.TOP3_QTY
from tab1 t1 inner join
(
select per_name,sum(fruit_qty) as TOTAL_QTY,
max(case itemid when 1 then fruit_name else '' end) as TOP1_NAME,
max(case itemid when 1 then fruit_qty else '' end) as TOP1_QTY,
max(case itemid when 2 then fruit_name else '' end) as TOP2_NAME,
max(case itemid when 2 then fruit_qty else '' end) as TOP2_QTY,
max(case itemid when 3 then fruit_name else '' end) as TOP3_NAME,
max(case itemid when 3 then fruit_qty else '' end) as TOP3_QTY
from
(
select *,itemid = (select count(1) from tab2 where per_name = a.per_name 
and fruit_qty >= a.fruit_qty)
from tab2 a
)tab
group by per_name
) t2 on t1.per_name = t2.per_name --更新后查询
select * from tab1--结果
/*
A 23 APPLE 10 PEAR 8 BANANER 5
B 12 PEAR 8 ORANGE 4 0
C 46 APPLE 20 PEAR 18 BANANER 8
*/--删除
drop table tab1
drop table tab2

Answer 3

SELECT F.PER_NAME ,NTILE(4) OVER (ORDER BY F.PER_NAME ) AS '排名'
FROM Person P,FRUIT F
WHERE P.PER_NAME=F.PER_NAME
GROUP BY F.PER_NAME 

Answer 4

SELECT F.PER_NAME ,NTILE(4) OVER (ORDER BY F.PER_NAME ) AS '排名'
FROM Person P,FRUIT F
WHERE P.PER_NAME=F.PER_NAME
GROUP BY F.PER_NAME 

Answer 5

大概
--建立测试环境
IF OBJECT_ID('FRUIT') IS NOT NULL  DROP TABLE FRUIT
GO
CREATE TABLE FRUIT
(
PER_NAME varchar(10),
FRUIT_NAME varchar(20),
FRUIT_QTY int
)
GO
INSERT FRUIT
select 'A','APPLE', 10 union all
select 'A','PEAR', 8 union all
select 'B','PEAR', 12
 
--查询declare @sql varchar(8000)
set @sql='select PER_NAME ,sum(FRUIT_QTY) TOTAL_QTY'
select @sql=@sql+' ,max( case when rn='+ltrim(rn)+' then FRUIT_NAME else '''' end) as TOP'+ltrim(rn)+'_NAME 
,sum( case when rn='+ltrim(rn)+' then FRUIT_QTY else 0 end) as TOP'+ltrim(rn)+'_QTY '
from(select distinct rn=(select count(1) from FRUIT where PER_NAME=t.PER_NAME and FRUIT_QTY>=t.FRUIT_QTY)
from FRUIT t)a
set @sql = @sql +' from(select * , rn=(select count(1) from FRUIT where PER_NAME=t.PER_NAME and FRUIT_QTY>=t.FRUIT_QTY)
from FRUIT t)a group by PER_NAME'
exec(@sql)--结果
/*(3 行受影响)
PER_NAME   TOTAL_QTY   TOP1_NAME            TOP1_QTY    TOP2_NAME            TOP2_QTY
---------- ----------- -------------------- ----------- -------------------- -----------
A          18          APPLE                10          PEAR                 8
B          12          PEAR                 12                               0(2 行受影响)*/

Answer 6

行转列参考内容
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx

Answer 7

有个问题，假如FRUIT_QTY有并列的，比如
PEAR 10
APPLE 10
这个时候itemid结果是
2
2
3
...
然后找1的时候是空的，
怎么解决？

Answer 8

如果你用的sql 2005及以上就可以这样--建立测试环境
IF OBJECT_ID('FRUIT') IS NOT NULL  DROP TABLE FRUIT
GO
CREATE TABLE FRUIT
(
    PER_NAME varchar(10),
    FRUIT_NAME varchar(20),
    FRUIT_QTY int
)
GO
INSERT FRUIT
select 'A','APPLE', 10 union all
select 'A','PEAR', 8 union all
select 'B','PEAR', 12
 
--查询declare @sql varchar(8000)
set @sql='select PER_NAME ,sum(FRUIT_QTY) TOTAL_QTY'
select @sql=@sql+'    ,max( case when rn='+ltrim(rn)+' then FRUIT_NAME else '''' end) as TOP'+ltrim(rn)+'_NAME 
                    ,sum( case when rn='+ltrim(rn)+' then FRUIT_QTY else 0 end) as TOP'+ltrim(rn)+'_QTY '
from(select distinct rn=row_number()over(partition by PER_NAME order by FRUIT_QTY)
from FRUIT t)a
set @sql = @sql +' from(select * , rn=row_number()over(partition by PER_NAME order by FRUIT_QTY)
from FRUIT t)a group by PER_NAME'
exec(@sql)--结果
/*(3 行受影响)
PER_NAME   TOTAL_QTY   TOP1_NAME            TOP1_QTY    TOP2_NAME            TOP2_QTY
---------- ----------- -------------------- ----------- -------------------- -----------
A          18          APPLE                10          PEAR                 8
B          12          PEAR                 12                               0(2 行受影响)*/

Answer 9

--建立测试环境
IF OBJECT_ID('FRUIT') IS NOT NULL  DROP TABLE FRUIT
GO
CREATE TABLE FRUIT
(
    PER_NAME varchar(10),
    FRUIT_NAME varchar(20),
    FRUIT_QTY int
)
GO
INSERT FRUIT
select 'A','APPLE', 10 union all
select 'A','PEAR', 8 union all
select 'B','PEAR', 12
 
--查询declare @sql varchar(8000)
set @sql='select PER_NAME ,sum(FRUIT_QTY) TOTAL_QTY'
select @sql=@sql+'    ,max( case when rn='+ltrim(rn)+' then FRUIT_NAME else '''' end) as TOP'+ltrim(rn)+'_NAME 
                    ,sum( case when rn='+ltrim(rn)+' then FRUIT_QTY else 0 end) as TOP'+ltrim(rn)+'_QTY '
from(select distinct rn=row_number()over(partition by PER_NAME order by FRUIT_QTY desc)
from FRUIT t)a
set @sql = @sql +' from(select * , rn=row_number()over(partition by PER_NAME order by FRUIT_QTY desc)
from FRUIT t)a group by PER_NAME'
exec(@sql)--结果
/*(3 行受影响)
PER_NAME   TOTAL_QTY   TOP1_NAME            TOP1_QTY    TOP2_NAME            TOP2_QTY
---------- ----------- -------------------- ----------- -------------------- -----------
A          18          APPLE                10          PEAR                 8
B          12          PEAR                 12                               0(2 行受影响)*/ 
刚才排序反了，这个改了

Answer 10

怎么返回了所有结果，我只需TOP3，怎么修改啊？