27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #tbl(BM VARCHAR(10),XM VARCHAR(10),JE MONEY)
INSERT #tbl SELECT 'A1','AAA',500
INSERT #tbl SELECT 'A1','BBB',600
INSERT #tbl SELECT 'B1','CCC',900
INSERT #tbl SELECT 'B1','DDD',0
INSERT #tbl SELECT 'C1','EEE',0
INSERT #tbl SELECT 'C1','FFF',500
INSERT #tbl SELECT 'C1','HHH',700
SELECT BM '部门',SUM(JE) '金额',
(SELECT COUNT(*) FROM #tbl where JE > 0 and BM = t.BM) '人数',
(SELECT SUM(JJ) FROM #tbl where JJ > 0 and BM = t.BM) '奖金',
(SELECT COUNT(*) FROM #tbl where JJ > 0 and BM = t.BM) '奖金人数'
FROM #tbl t
GROUP BY BM
/*
部门 金额 金额人数 奖金 奖金人数
A1 1100.00 2 50 1
B1 900.00 1 200 2
C1 1200.00 2 200 2
*/
CREATE TABLE TB(BM VARCHAR(10), XM VARCHAR(10), JE MONEY ,jj money)
INSERT TB SELECT 'A1' , 'AAA' , 500, 0
INSERT TB SELECT 'A1' , 'BBB' , 600 , 50
INSERT TB SELECT 'B1' , 'CCC' , 900 , 100
INSERT TB SELECT 'B1' , 'DDD' , 0 , 100
INSERT TB SELECT 'C1' , 'EEE' , 0 , 100
INSERT TB SELECT 'C1' , 'FFF' , 500 , 0
INSERT TB SELECT 'C1' , 'HHH' , 700 , 100
SELECT BM,SUM(JE)金额,SUM(CASE WHEN JE<>0 THEN 1 ELSE 0 END )金额人数,SUM(JJ)奖金,
SUM(CASE WHEN JJ<>0 THEN 1 ELSE 0 END )奖金人数 FROM TB GROUP BY BM
DROP TABLE TB
/*BM 金额 金额人数 奖金 奖金人数
---------- --------------------- ----------- --------------------- -----------
A1 1100.0000 2 50.0000 1
B1 900.0000 1 200.0000 2
C1 1200.0000 2 200.0000 2
(影響 3 個資料列)
*/
select BM , 金额 =sum(JE), 金额人数 =(select count(1) from tb where BM=a.BM and JE<>0) ,
奖金=sum(JJ),奖金人数 =(select count(1) from tb where BM=a.BM and JJ<>0) from tb a
group by BM order by BM
SELECT BM,[金额] =SUM(JE),[人数] =COUNT(1)
FROM TB
WHERE JE<>0
GROUP BY BM
ORDER BY BM
select
a.部门,
(select sum(金额) from tb b where b.部门 = a.部门 )as 金额,
(select count(*) from tb c where c.部门= a.部门 and c.金额 <> 0) as 金额
from tb
group by a.部门
SELECT 部门,sum(金额) 金额,count(1) 人数
from tableName
where 金额<>0
group by 部门
create table tab(BM varchar(20),XM varchar(20),JE int)
insert tab values('A1','AAA',500)
insert tab values('A1','BBB',600)
insert tab values('B1','CCC',900)
insert tab values('B1','DDD',0)
insert tab values('C1','EEE',0)
insert tab values('C1','FFF',500)
insert tab values('C1','HHH',700)
select BM 部门,sum(JE) 金额,count(*) 人数 from tab where JE>0 group by BM
/*
部门 金额 人数
-------------------- ----------- -----------
A1 1100 2
B1 900 1
C1 1200 2
(所影响的行数为 3 行)
*/
CREATE TABLE TB(BM VARCHAR(10), XM VARCHAR(10), JE MONEY)
INSERT TB SELECT 'A1' , 'AAA', 500
INSERT TB SELECT 'A1' , 'BBB' , 600
INSERT TB SELECT 'B1' , 'CCC' , 900
INSERT TB SELECT 'B1' , 'DDD' , 0
INSERT TB SELECT 'C1' , 'EEE' , 0
INSERT TB SELECT 'C1' , 'FFF' , 500
INSERT TB SELECT 'C1', 'HHH' , 700
SELECT BM,SUM(JE)JE,COUNT(XM)人数 FROM TB WHERE JE<>0 GROUP BY BM
DROP TABLE TB
/*BM JE 人数
---------- --------------------- -----------
A1 1100.0000 2
B1 900.0000 1
C1 1200.0000 2
(影響 3 個資料列)
*/
CREATE TABLE TB(BM VARCHAR(10), XM VARCHAR(10), JE MONEY)
INSERT TB SELECT 'A1' , 'AAA', 500
INSERT TB SELECT 'A1' , 'BBB' , 600
INSERT TB SELECT 'B1' , 'CCC' , 900
INSERT TB SELECT 'B1' , 'DDD' , 0
INSERT TB SELECT 'C1' , 'EEE' , 0
INSERT TB SELECT 'C1' , 'FFF' , 500
INSERT TB SELECT 'C1', 'HHH' , 700
SELECT BM,SUM(JE)JE,COUNT(XM)人数 FROM TB GROUP BY BM
DROP TABLE TB
/*BM JE 人数
---------- --------------------- -----------
A1 1100.0000 2
B1 900.0000 2
C1 1200.0000 3
(影響 3 個資料列)*/
select BM , 金额 =sum(JE), 人数 =count(1) from tb
where JE<>0
group by BM order by BM
select BM , 金额 =sum(JE) , 人数 =count(1) from tb group by BM order by BM
SELECT 部门,sum(金额) 金额,count(1)
from tableName
group by 部门
select 部门 , 金额 =sum(金额) , 人数 =count(1) from tb group by 部门 order by 部门
SELECT BM,SUM(JE)JE,COUNT(*)人数 FROM TB GROUP BY BM