62,074
社区成员
发帖
与我相关
我的任务
分享
/*
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------
1 Chai 1 1 10 boxes x 20 bags 18.0000 39 0 10 0
2 Chang 1 1 24 - 12 oz bottles 19.0000 17 40 25 0
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.0000 13 70 25 0
4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22.0000 53 0 0 0
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.3500 0 0 0 1
6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25.0000 120 0 25 0
7 Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. 30.0000 15 0 10 0
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.0000 6 0 0 0
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.0000 29 0 0 1
10 Ikura 4 8 12 - 200 ml jars 31.0000 31 0 0 0
(所影响的行数为 10 行)
*/
/*
部门 季度1 季度2 季度3 季度4
--------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
部门1 100.00 105.00 98.00 87.00
(所影响的行数为 1 行)
*/
/*
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|ProductI|ProductNam|Supplier|Category|QuantityPerU|UnitPri|UnitsInSto|UnitsOnOrd|ReorderLev|Discontinu|
| D | e | ID | ID | nit | ce | ck | er | el | ed |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |10 boxes x | | | | | |
|1 |Chai |1 |1 |20 bags |18 |39 |0 |10 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |24 - 12 oz | | | | | |
|2 |Chang |1 |1 |bottles |19 |17 |40 |25 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| |Aniseed | | |12 - 550 ml | | | | | |
|3 |Syrup |1 |2 |bottles |10 |13 |70 |25 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Chef | | | | | | | | |
|4 |Anton's |2 |2 |48 - 6 oz |22 |53 |0 |0 |False |
| |Cajun | | |jars | | | | | |
| |Seasoning | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Chef | | | | | | | | |
|5 |Anton's |2 |2 |36 boxes |21.35 |0 |0 |0 |True |
| |Gumbo Mix | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|--------|----------|--------|--------|------------|-------|----------|----------|----------|----------|
| |Grandma's | | | | | | | | |
|6 |Boysenberr|3 |2 |12 - 8 oz |25 |120 |0 |25 |False |
| |y Spread | | |jars | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Uncle | | | | | | | | |
| |Bob's | | | | | | | | |
|7 |Organic |3 |7 |12 - 1 lb |30 |15 |0 |10 |False |
| |Dried | | |pkgs. | | | | | |
| |Pears | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Northwoods| | | | | | | | |
|8 |Cranberry |3 |2 |12 - 12 oz |40 |6 |0 |0 |False |
| |Sauce | | |jars | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|--------|Mishi Kobe|--------|--------|18 - 500 g |-------|----------|----------|----------|----------|
|9 |Niku |4 |6 |pkgs. |97 |29 |0 |0 |True |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |12 - 200 ml | | | | | |
|10 |Ikura |4 |8 |jars |31 |31 |0 |0 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
*/
--测试完毕,还是全英文比较美观
/*
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
*/
create table sales
(
部门 int ,
季度 int,
销售额 decimal(18,2)
)
insert into sales values(1,1,100)
insert into sales values(1,2,105)
insert into sales values(1,3,98)
insert into sales values(1,4,87)
--...
select
'部门'+convert(nvarchar(5),部门) as '部门',
sum(case when 季度=1 then 销售额 else 0 end) 季度1,
sum(case when 季度=2 then 销售额 else 0 end) 季度2,
sum(case when 季度=3 then 销售额 else 0 end) 季度3,
sum(case when 季度=4 then 销售额 else 0 end) 季度4
from sales group by 部门
drop table sales
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
/*
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
*/
create table sales
(
部门 int ,
季度 int,
销售额 decimal(18,2)
)
insert into sales values(1,1,100)
insert into sales values(1,2,105)
insert into sales values(1,3,98)
insert into sales values(1,4,87)
--...
select
'部门'+convert(nvarchar(5),部门) as '部门',
sum(case when 季度=1 then 销售额 else 0 end) 季度1,
sum(case when 季度=2 then 销售额 else 0 end) 季度2,
sum(case when 季度=3 then 销售额 else 0 end) 季度3,
sum(case when 季度=4 then 销售额 else 0 end) 季度4
from sales group by 部门
drop table sales
declare @t table(部门 int,季度 int,销售额 numeric(12,4))
insert @t select 1,1,100
union all select 1,2,105
union all select 1,3,98
union all select 1,4,87
select * from @t
select 部门='部门'+cast(部门 as varchar),
'季度1'=sum((case 季度 when 1 then 销售额 end)),
'季度2'=sum((case 季度 when 2 then 销售额 end)),
'季度3'=sum((case 季度 when 3 then 销售额 end)),
'季度4'=sum((case 季度 when 4 then 销售额 end))
from @t
group by 部门
/*
(所影响的行数为 4 行)
部门 季度 销售额
----------- ----------- --------------
1 1 100.0000
1 2 105.0000
1 3 98.0000
1 4 87.0000
(所影响的行数为 4 行)
部门 季度1 季度2 季度3 季度4
-------- ------- -------------- ---------------- ----------------
部门1 100.0000 105.0000 98.0000 87.0000
(所影响的行数为 1 行)
警告: 聚合或其它 SET 操作消除了空值。
*/