主页 / dba / 问题 / 14555
Accepted
Clive
Asked: 2012-03-08 06:16:50 +0800 CST

将一列一分为二并返回结果摘要

  • 772

让我们看一个非常简单的表格

Department           | Staff Name
---------------------|---------------
Web - Electronics    | Matthew
Store - Electronics  | Mark
Web - Home           | Luke
Store - Home         | John
Store - Home         | Paul
Store - Home         | Simon
Web - Electronics    | Peter

我需要计算一些 SQL 以返回以下内容：

Deparment    | Web  | Store
-------------|------|---------
Electronics  | 2    | 1
Home         | 1    | 3

所以我需要为每个部门计算网站和商店的数量。是否可以在 SQL 中执行类似的操作？

在 C# 中，我会循环遍历结果。我会把部门分成两个字符串-。我会在字典中添加一个条目，所以部门名称是关键。对于条目，我将有一个带有 Web 和 Store 字段的小对象。如果条目是 stor 或 web，我会将值加一。

在此之后，我将循环通过我的字典来输出结果，方法是打印字典键然后打印网络和商店的计数。

我什至不确定从哪里开始尝试仅在 SQL 中执行此操作...

sql-server-2008 ssrs

2 个回答

  1. Best Answer

    你可以做这样的事情。

    declare @T table
(
  Department varchar(50),
  [Staff Name] varchar(50)
)

insert into @T values
('Web - Electronics',    'Matthew'),
('Store - Electronics',  'Mark'),
('Web - Home',           'Luke'),
('Store - Home',         'John'),
('Store - Home',         'Paul'),
('Store - Home',         'Simon'),
('Web - Electronics',    'Peter')

select T3.Department,
       sum(case T3.WebStore when 'Web' then 1 else 0 end) as Web,
       sum(case T3.WebStore when 'Store' then 1 else 0 end) as Store
from @T as T1
  cross apply (select replace(Department, ' - ', '.')) as T2(Department)
  cross apply (select parsename(T2.Department, 1),  
                      parsename(T2.Department, 2)) as T3(Department, WebStore)
group by T3.Department
    • 4

  2. 您可能喜欢使用视图作为中间件分两个阶段执行此操作，例如：

    试验台：

    create table staff(name varchar(50) not null, department varchar(50) not null);
go
create view v_staff as
select name, parsename(department,1) as department, parsename(department,2) as type 
from (select name, replace(department, ' - ', '.') as department from staff) z;
go
insert into staff(name, department) values 
('Matthew','Web - Electronics'),
('Mark','Store - Electronics'),
('Luke','Web - Home'),
('John','Store - Home'),
('Paul','Store - Home'),
('Simon','Store - Home'),
('Peter','Web - Electronics');

    询问：

    select department, sum([Web]) as web, sum([Store]) as store
from v_staff pivot (count(type) for type in([Web], [Store])) as z
group by department;

    结果：

    /*
department  web store
Electronics 2   1
Home        1   3
*/
    • 1

相关问题