sql问题，期待高手，加班等待中

Question

表如下      主键        第一审批人           第二审批人     金额       
      OID         first                 second        price
       1           A                      B            5.00  
       2           B                      C            6.89
       3           B                      D            8.22（注  主键为字增长的， A B C D 为4个人的名字）
现在要实现查询显示成
     审批人            审批总金额              审批总项目数
      A                  5.00                         1
      B                  20.11                        3
      C                  6.89                         1
      D                  8.22                          1有高手能写出这样的sql语句吗？我想半天了 没头绪  菜

Answer 1

select distinct 字段名 from tablename  inner join  表名

Answer 2

--> Test Data: @T
declare @T table ([OID] int,[first] varchar(1),[second] varchar(1),[price] numeric(3,2))
insert into @T
select 1,'A','B',5.00 union all
select 2,'B','C',6.89 union all
select 3,'B','D',8.22select * from @T
--Code
select 审批人=person,审批总金额=SUM([price]),审批总项目数=count(1) from
(
select [first] as person,[price] from @T
union all
select [second],[price] from @T
) t group by person
--Drop--Result
/*
审批人  审批总金额                                   审批总项目数
---- --------------------------------------- -----------
A    5.00                                    1
B    20.11                                   3
C    6.89                                    1
D    8.22                                    1
*/

Answer 3

create table tb(OID int ,[first] nvarchar(10),[second] nvarchar(10),price decimal(18,2))
goinsert into tb select 
1,          'A',                      'B',            5.00 union all select
2,          'B',                      'C',            6.89 union all select
3,          'B',                      'D',            8.22 
select 审批人=[first],
审批总金额=SUM(Price),
审批总项目数=COUNT(1)
from
( Select [first],price
from  tb
union all
select [second],price
from tb
) V
group by [first]/*
审批人        审批总金额                                   审批总项目数
---------- --------------------------------------- -----------
A          5.00                                    1
B          20.11                                   3
C          6.89                                    1
D          8.22                                    1
*/

Answer 4

select 审批人,sum(金额)as 审批总金额,count(*) as 审批总项目数　from ((select 第一审批人 as 审批人,金额 from table1) union all (select 第二审批人 as 审批人,金额 from table1)) as table2 group by 审批人;

Answer 5

2 3楼的果然两位牛人  我测试过了都能正常显示   但是还有一个问题 我现在想显示成审批人        审批总金额      第一审批人项目数   第二审批人项目数      审批总项目数
---------- --------------------------------------- -----------
A          5.00                   1                 0               1
B          20.11                  2                 1               3
C          6.89                   0                1                1
D          8.22                   0                1                1
恳求指教！

Answer 6

create table tb(OID int ,[first] nvarchar(10),[second] nvarchar(10),price decimal(18,2))
goinsert into tb select 
1,          'A',                      'B',            5.00 union all select
2,          'B',                      'C',            6.89 union all select
3,          'B',                      'D',            8.22 
select 审批人=[first],
        审批总金额=SUM(Price),
        第一审批人项目数=Sum(case 级别 when 1 then 1 else 0 end),
        第二审批人项目数=Sum(case 级别 when 2 then 1 else 0 end),
        审批总项目数=COUNT(*)
        
from        
(    Select [first],price,1 as 级别
    from  tb
    union all
    select [second],price,2 as 级别
    from tb
) V
group by [first]/*
审批人        审批总金额                                   第一审批人项目数    第二审批人项目数    审批总项目数
---------- --------------------------------------- ----------- ----------- -----------
A          5.00                                    1           0           1
B          20.11                                   2           1           3
C          6.89                                    0           1           1
D          8.22                                    0           1           1(4 行受影响)
*/