22,209
社区成员
发帖
与我相关
我的任务
分享
create table A (数量 int,客户 varchar(100),金额 float)
insert into A values(8000,'联想中国(纽约)',3600.00)
insert into A values(2000,'联想中国(纽约)',.00)
insert into A values(5000,'联想国际(纽约)',.00)
insert into A values(11000,'HP国际(纽约)',2143.92)
insert into A values(6000,'联想中国(纽约)',.00)
insert into A values(4000,'联想中国(纽约)',1347.30)
insert into A values(4000,'联想中国(纽约)',11347.30)
insert into A values(4000,'联想中国(纽约)',-6.30)
create table B (范围值1 int,范围值2 int)
insert into B values(0,2000)
insert into B values(2001,4000)
insert into B values(4001,6000)
insert into B values(6001,8000)
insert into B values(8001,10000)
select 范围值1,范围值2,
sum(case when 客户='联想中国(纽约)' then 数量 else 0 end) as [联想中国(纽约)],
sum(case when 客户='联想中国(纽约)' then 1 else 0 end) as 批次1,
sum(case when 客户='联想中国(纽约)' then 金额 else 0 end) as 金额1,
sum(case when 客户='联想国际(纽约)' then 数量 else 0 end) as [联想国际(纽约)],
sum(case when 客户='联想国际(纽约)' then 1 else 0 end) as 批次2,
sum(case when 客户='联想国际(纽约)' then 金额 else 0 end) as 金额2,
sum(case when 客户='HP国际(纽约)' then 数量 else 0 end) as [HP国际(纽约)],
sum(case when 客户='HP国际(纽约)' then 1 else 0 end) as 批次3,
sum(case when 客户='HP国际(纽约)' then 金额 else 0 end) as 金额3
from (
select case when A.金额 >10000 then 10000 else B.范围值1 end as 范围值1,
case when A.金额 <0 then 0 else B.范围值2 end as 范围值2,
A.* from B
full join A
on A.金额 >=B.范围值1 and A.金额 <=B.范围值2) t
group by t.范围值1,t.范围值2
order by t.范围值1,t.范围值2
/*
范围值1 范围值2 联想中国(纽约) 批次1 金额1 联想国际(纽约) 批次2 金额2 HP国际(纽约)批次3 金额3
----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ---------------------- ----------- ----------- ----------------------
NULL 0 4000 1 -6.3 0 0 0 0 0 0
0 2000 12000 3 1347.3 5000 1 0 0 0 0
2001 4000 8000 1 3600 0 0 0 11000 1 2143.92
4001 6000 0 0 0 0 0 0 0 0 0
6001 8000 0 0 0 0 0 0 0 0 0
8001 10000 0 0 0 0 0 0 0 0 0
10000 NULL 4000 1 11347.3 0 0 0 0 0 0
(7 行受影响)
*/
create table A (数量 int,客户 varchar(50),金额 float)
insert into A values(8000,'联想中国(纽约)',3600.00)
insert into A values(2000,'联想中国(纽约)',.00)
insert into A values(5000,'联想国际(纽约)',.00)
insert into A values(11000,'HP国际(纽约)',2143.92)
insert into A values(6000,'联想中国(纽约)',.00)
insert into A values(4000,'联想中国(纽约)',1347.30)
create table B (范围值1 int,范围值2 int)
insert into B values(0,2000)
insert into B values(2001,4000)
insert into B values(4001,6000)
insert into B values(6001,8000)
insert into B values(8001,10000)
select 范围值1,范围值2,
sum(case when 客户='联想中国(纽约)' then 数量 else 0 end) as [联想中国(纽约)],
sum(case when 客户='联想中国(纽约)' then 1 else 0 end) as 批次1,
sum(case when 客户='联想中国(纽约)' then 金额 else 0 end) as 金额1,
sum(case when 客户='联想国际(纽约)' then 数量 else 0 end) as [联想国际(纽约)],
sum(case when 客户='联想国际(纽约)' then 1 else 0 end) as 批次2,
sum(case when 客户='联想国际(纽约)' then 金额 else 0 end) as 金额2,
sum(case when 客户='HP国际(纽约)' then 数量 else 0 end) as [HP国际(纽约)],
sum(case when 客户='HP国际(纽约)' then 1 else 0 end) as 批次3,
sum(case when 客户='HP国际(纽约)' then 金额 else 0 end) as 金额3
from (
select * from B
left outer join A
on A.金额 >=B.范围值1 and A.金额 <=B.范围值2) t
group by t.范围值1,t.范围值2
/*
范围值1 范围值2 联想中国(纽约) 批次1 金额1 联想国际(纽约) 批次2 金额2 HP国际(纽约)批次3 金额3
----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ---------------------- ----------- ----------- ----------------------
0 2000 12000 3 1347.3 5000 1 0 0 0 0
2001 4000 8000 1 3600 0 0 0 11000 1 2143.92
4001 6000 0 0 0 0 0 0 0 0 0
6001 8000 0 0 0 0 0 0 0 0 0
8001 10000 0 0 0 0 0 0 0 0 0
(5 行受影响)
*/
create table A (数量 int,客户 varchar(50),金额 float)
insert into A values(8000,'联想中国(纽约)',3600.00)
insert into A values(8000,'联想中国(纽约)',3600.00)
insert into A values(2000,'联想中国(纽约)',.00)
insert into A values(5000,'联想国际(纽约)',.00)
insert into A values(11000,'HP国际(纽约)',2143.92)
insert into A values(6000,'联想中国(纽约)',.00)
insert into A values(4000,'联想中国(纽约)',1347.30)
create table B (范围值1 int,范围值2 int)
insert into B values(0,2000)
insert into B values(2001,4000)
insert into B values(4001,6000)
insert into B values(6001,8000)
insert into B values(8001,10000)
select 范围值1,范围值2,
sum(case when 客户='联想中国(纽约)' then 数量 else 0 end) as [联想中国(纽约)],
sum(case when 客户='联想中国(纽约)' then 1 else 0 end) as 批次1,
sum(case when 客户='联想中国(纽约)' then 金额 else 0 end) as 金额1,
sum(case when 客户='联想国际(纽约)' then 数量 else 0 end) as [联想国际(纽约)],
sum(case when 客户='联想国际(纽约)' then 1 else 0 end) as 批次2,
sum(case when 客户='联想国际(纽约)' then 金额 else 0 end) as 金额2,
sum(case when 客户='HP国际(纽约)' then 数量 else 0 end) as [HP国际(纽约)],
sum(case when 客户='HP国际(纽约)' then 1 else 0 end) as 批次3,
sum(case when 客户='HP国际(纽约)' then 金额 else 0 end) as 金额3
from (
select * from B
left outer join A
on A.金额 >=B.范围值1 and A.金额 <=B.范围值2) t
group by t.范围值1,t.范围值2
/*
范围值1 范围值2 联想中国(纽约) 批次1 金额1 联想国际(纽约) 批次2 金额2 HP国际(纽约)批次3 金额3
----------- ----------- ----------- ----------- ---------------------- ----------- ----------- ---------------------- ----------- ----------- ----------------------
0 2000 12000 3 1347.3 5000 1 0 0 0 0
2001 4000 16000 2 7200 0 0 0 11000 1 2143.92
4001 6000 0 0 0 0 0 0 0 0 0
6001 8000 0 0 0 0 0 0 0 0 0
8001 10000 0 0 0 0 0 0 0 0 0
(5 行受影响)
*/
create table tba(数量 int,客户 nvarchar(10),金额 money)
insert into tba select 8000,'联想中国(纽约)',3600.00
insert into tba select 2000,'联想中国(纽约)',.00
insert into tba select 5000,'联想国际(纽约)',.00
insert into tba select 11000,'HP国际(纽约)',2143.92
insert into tba select 6000,'联想中国(纽约)',.00
insert into tba select 4000,'联想中国(纽约)',1347.30
go
create table tbb(范围值1 int,范围值2 int)
insert into tbb select 0,2000
insert into tbb select 2001,4000
insert into tbb select 4001,6000
insert into tbb select 6001,8000
insert into tbb select 8001,10000
insert into tbb select 10001,12000
select 范围值1,范围值2,
(select sum(数量) from tba where 客户='联想中国(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as ['联想中国(纽约)'],
(select count(*) from tba where 客户='联想中国(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as 批次,
(select sum(金额)from tba where 客户='联想中国(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as 金额,
(select sum(数量) from tba where 客户='HP国际(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as ['HP国际(纽约)'],
(select count(*) from tba where 客户='HP国际(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as 批次,
(select sum(金额)from tba where 客户='HP国际(纽约)' and 数量>=b.范围值1 and 数量<=b.范围值2) as 金额
from tbb b
go
drop table tba,tbb
/*
范围值1 范围值2 '联想中国(纽约)' 批次 金额 'HP国际(纽约)' 批次 金额
----------- ----------- ----------- ----------- --------------------- ----------- ----------- ---------------------
0 2000 2000 1 0.00 NULL 0 NULL
2001 4000 4000 1 1347.30 NULL 0 NULL
4001 6000 6000 1 0.00 NULL 0 NULL
6001 8000 8000 1 3600.00 NULL 0 NULL
8001 10000 NULL 0 NULL NULL 0 NULL
10001 12000 NULL 0 NULL 11000 1 2143.92
*/
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: 表A
CREATE TABLE 表A (数量 INT,客户 VARCHAR(14),金额 NUMERIC(6,2))
INSERT INTO 表A
SELECT 8000,'联想中国(纽约)',3600.00 UNION ALL
SELECT 2000,'联想中国(纽约)',.00 UNION ALL
SELECT 5000,'联想国际(纽约)',.00 UNION ALL
SELECT 11000,'HP国际(纽约)',2143.92 UNION ALL
SELECT 6000,'联想中国(纽约)',.00 UNION ALL
SELECT 4000,'联想中国(纽约)',1347.30
--> 生成测试数据: 表B
CREATE TABLE 表B(范围值1 INT,范围值2 INT)
INSERT INTO 表B
SELECT 0,2000 UNION ALL
SELECT 2001,4000 UNION ALL
SELECT 4001,6000 UNION ALL
SELECT 6001,8000 UNION ALL
SELECT 8001,10000
--SQL查询如下:
DECLARE @SQL VARCHAR(8000);
SET @SQL='';
SELECT @SQL=@SQL+',SUM(CASE WHEN A.客户='''+客户+''' THEN A.数量 ELSE 0 END) AS ['+客户+']'
+',SUM(CASE WHEN A.客户='''+客户+''' THEN 1 ELSE 0 END) AS 批次'
+',SUM(CASE WHEN A.客户='''+客户+''' THEN A.金额 ELSE 0 END) AS 金额'
FROM (SELECT DISTINCT 客户 FROM 表A) AS A
EXEC ('
SELECT B.范围值1,B.范围值2'+@SQL+'
FROM 表A AS A
RIGHT JOIN 表B AS B
ON A.数量 BETWEEN B.范围值1 AND B.范围值2
GROUP BY B.范围值1,B.范围值2
')
DROP TABLE 表A,表B
/*
范围值1 范围值2 HP国际(纽约) 批次 金额 联想国际(纽约) 批次 金额 联想中国(纽约) 批次 金额
----------- ----------- ----------- ----------- --------------------------------------- ----------- ----------- --------------------------------------- ----------- ----------- ---------------------------------------
0 2000 0 0 0.00 0 0 0.00 2000 1 0.00
2001 4000 0 0 0.00 0 0 0.00 4000 1 1347.30
4001 6000 0 0 0.00 5000 1 0.00 6000 1 0.00
6001 8000 0 0 0.00 0 0 0.00 8000 1 3600.00
8001 10000 0 0 0.00 0 0 0.00 0 0 0.00
(5 行受影响)
*/
select
b.范围值1,
b.范围值2,
sum(case when 客户='联想中国(纽约)' then 数量 else 0 end) as [联想中国(纽约)],
sum(case when 客户='联想中国(纽约)' then 1 else 0 end) as 批次,
sum(case when 客户='联想中国(纽约)' then 金额 else 0 end) as 金额,
sum(case when 客户='联想国际(纽约)' then 数量 else 0 end) as [联想国际(纽约)],
sum(case when 客户='联想国际(纽约)' then 1 else 0 end) as 批次,
sum(case when 客户='联想国际(纽约)' then 金额 else 0 end) as 金额,
sum(case when 客户='HP国际(纽约)' then 数量 else 0 end) as [HP国际(纽约)],
sum(case when 客户='HP国际(纽约)' then 1 else 0 end) as 批次,
sum(case when 客户='HP国际(纽约)' then 金额 else 0 end) as 金额
from
表B b
left join
表A a
on
a.数量 between b.范围值1 and b.范围值2
group by
b.范围值1,b.范围值2
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: 表A
CREATE TABLE 表A (数量 INT,客户 VARCHAR(14),金额 NUMERIC(6,2))
INSERT INTO 表A
SELECT 8000,'联想中国(纽约)',3600.00 UNION ALL
SELECT 2000,'联想中国(纽约)',.00 UNION ALL
SELECT 5000,'联想国际(纽约)',.00 UNION ALL
SELECT 11000,'HP国际(纽约)',2143.92 UNION ALL
SELECT 6000,'联想中国(纽约)',.00 UNION ALL
SELECT 4000,'联想中国(纽约)',1347.30
--> 生成测试数据: 表B
CREATE TABLE 表B(范围值1 INT,范围值2 INT)
INSERT INTO 表B
SELECT 0,2000 UNION ALL
SELECT 2001,4000 UNION ALL
SELECT 4001,6000 UNION ALL
SELECT 6001,8000 UNION ALL
SELECT 8001,10000
--SQL查询如下:
DECLARE @SQL VARCHAR(8000);
SET @SQL='';
SELECT @SQL=@SQL+',SUM(CASE WHEN A.客户='''+客户+''' THEN A.金额 ELSE 0 END) AS ['+客户+']'
+',COUNT(*) AS 批次,SUM(ISNULL(金额,0)) AS 金额'
FROM (SELECT DISTINCT 客户 FROM 表A) AS A
EXEC ('
SELECT B.范围值1,B.范围值2'+@SQL+'
FROM 表A AS A
RIGHT JOIN 表B AS B
ON A.金额 BETWEEN B.范围值1 AND B.范围值2
GROUP BY B.范围值1,B.范围值2
')
DROP TABLE 表A,表B
/*
范围值1 范围值2 HP国际(纽约) 批次 金额 联想国际(纽约) 批次 金额 联想中国(纽约) 批次 金额
----------- ----------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
0 2000 0.00 4 1347.30 0.00 4 1347.30 1347.30 4 1347.30
2001 4000 2143.92 2 5743.92 0.00 2 5743.92 3600.00 2 5743.92
4001 6000 0.00 1 0.00 0.00 1 0.00 0.00 1 0.00
6001 8000 0.00 1 0.00 0.00 1 0.00 0.00 1 0.00
8001 10000 0.00 1 0.00 0.00 1 0.00 0.00 1 0.00
(5 行受影响)
*/