组合查询

Question

ID     资产编号  资产名称 使用部门   价格    使用者
1 DN001 电脑 1 4000 admin
2 DN001 电脑 1 4000 caojieming
3 DN001 电脑 1 4000 zhangsan
6 DN001 电脑 2 4000 yuzaowei
7 DN001 电脑 2 4000 wangxiaoli
8 DN001 电脑 2 4000 guoying
9 DN001 电脑 NULL 4000 NULL
10 DN001 电脑 NULL 4000 NULL
11 XSQ001 显示器 1 1800 admin
12 XSQ001 显示器 1 1800 caojieming
13 XSQ001 显示器 2 1800 yuzaowei
14 XSQ001 显示器 2 1800 wangxiaoli
15 XSQ001 显示器 NULL 1800 NULL
16 XSQ001 显示器 NULL 1800 NULL
17 ZJ001 主机 1 2000 admin
18 ZJ001 主机 1 2000 caojieming
19 ZJ001 主机 1 2000 zhangsan
20 ZJ001 主机 2 2000 yuzaowei
21 ZJ001 主机 2 2000 wangxiaoli
22 ZJ001 主机 2 2000 guoying
23 ZJ001 主机 NULL 2000 NULL
24 ZJ001 主机 NULL 2000 NULL从上面的表中查询出 资产编号，资产名称，使用数量(使用者不为空),库存(使用者为空),总资产原值,总资产折旧(*50%)大侠们帮帮忙，急用

Answer 1

declare @t table(ID int,资产编号 varchar(10), 资产名称 nvarchar(10),使用部门 int, 价格 money,使用者 nvarchar(20))insert @t
select 1,'DN001','电脑',1 ,4000,'admin' union all
select 2,'DN001','电脑',1 ,4000,'caojieming' union all
select 3,'DN001','电脑',1 ,4000,'zhangsan' union all
select 6,'DN001','电脑',2 ,4000,'yuzaowei' union all
select 7,'DN001','电脑',2 ,4000,'wangxiaoli' union all
select 8,'DN001','电脑',2 ,4000,'guoying' union all
select 9,'DN001','电脑',NULL ,4000,NULL union all
select 10,'DN001','电脑',NULL ,4000,NULL union all
select 11,'XSQ001','显示器',1 ,1800,'admin' union all
select 12,'XSQ001','显示器',1 ,1800,'caojieming' union all
select 13,'XSQ001','显示器',2 ,1800,'yuzaowei' union all
select 14,'XSQ001','显示器',2 ,1800,'wangxiaoli' union all
select 15,'XSQ001','显示器',NULL ,1800,NULL union all
select 16,'XSQ001','显示器',NULL,1800,NULL union all
select 17,'ZJ001','主机',1 ,2000,'admin' union all
select 18,'ZJ001','主机',1 ,2000,'caojieming' union all
select 19,'ZJ001','主机',1 ,2000,'zhangsan' union all
select 20,'ZJ001','主机',2 ,2000,'yuzaowei' union all
select 21,'ZJ001','主机',2 ,2000,'wangxiaoli' union all
select 22,'ZJ001','主机',2 ,2000,'guoying' union all
select 23,'ZJ001','主机',NULL ,2000,NULL union all
select 24,'ZJ001','主机',NULL ,2000,NULL 
select 资产编号,资产名称,count(使用者) as 使用数量,count(1)-count(使用者) as 库存,sum(价格) as 总资产原值,sum(价格)*0.5 as 总资产折旧
from @t
group by 资产编号,资产名称
资产编号       资产名称       使用数量        库存          总资产原值                 总资产折旧
---------- ---------- ----------- ----------- --------------------- ---------------------------------------
DN001      电脑         6           2           32000.00              16000.00000
XSQ001     显示器        4           2           10800.00              5400.00000
ZJ001      主机         6           2           16000.00              8000.00000

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-10-12 20:23:23
-- Version:
--      Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
-- May  3 2005 23:18:38 
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[资产编号] varchar(6),[资产名称] varchar(6),[使用部门] int,[价格] int,[使用者] varchar(10))
insert [tb]
select 1,'DN001','电脑',1,4000,'admin' union all
select 2,'DN001','电脑',1,4000,'caojieming' union all
select 3,'DN001','电脑',1,4000,'zhangsan' union all
select 6,'DN001','电脑',2,4000,'yuzaowei' union all
select 7,'DN001','电脑',2,4000,'wangxiaoli' union all
select 8,'DN001','电脑',2,4000,'guoying' union all
select 9,'DN001','电脑',null,4000,null union all
select 10,'DN001','电脑',null,4000,null union all
select 11,'XSQ001','显示器',1,1800,'admin' union all
select 12,'XSQ001','显示器',1,1800,'caojieming' union all
select 13,'XSQ001','显示器',2,1800,'yuzaowei' union all
select 14,'XSQ001','显示器',2,1800,'wangxiaoli' union all
select 15,'XSQ001','显示器',null,1800,null union all
select 16,'XSQ001','显示器',null,1800,null union all
select 17,'ZJ001','主机',1,2000,'admin' union all
select 18,'ZJ001','主机',1,2000,'caojieming' union all
select 19,'ZJ001','主机',1,2000,'zhangsan' union all
select 20,'ZJ001','主机',2,2000,'yuzaowei' union all
select 21,'ZJ001','主机',2,2000,'wangxiaoli' union all
select 22,'ZJ001','主机',2,2000,'guoying' union all
select 23,'ZJ001','主机',null,2000,null union all
select 24,'ZJ001','主机',null,2000,null
--------------开始查询--------------------------
select 
  资产编号,资产名称,
  sum(case  when 使用部门 is null then 1 else 0 end)as 使用数量,
  sum(case  when 使用部门 is not null then 1 else 0 end) as 库存,
  sum(价格) as 总资产原值,
  sum(价格)*0.5 as 总资产折旧
from 
  tb
group by 
  资产编号,资产名称
----------------结果----------------------------
/* 资产编号   资产名称   使用数量        库存          总资产原值       总资产折旧          
------ ------ ----------- ----------- ----------- -------------- 
DN001  电脑     2           6           32000       16000.0
XSQ001 显示器    2           4           10800       5400.0
ZJ001  主机     2           6           16000       8000.0（所影响的行数为 3 行）
*/

Answer 3

declare @t table(ID int,资产编号 varchar(10), 资产名称 nvarchar(10),使用部门 int, 价格 money,使用者 nvarchar(20))insert @t
select 1,'DN001','电脑',1 ,4000,'admin' union all
select 2,'DN001','电脑',1 ,4000,'caojieming' union all
select 3,'DN001','电脑',1 ,4000,'zhangsan' union all
select 6,'DN001','电脑',2 ,4000,'yuzaowei' union all
select 7,'DN001','电脑',2 ,4000,'wangxiaoli' union all
select 8,'DN001','电脑',2 ,4000,'guoying' union all
select 9,'DN001','电脑',NULL ,4000,NULL union all
select 10,'DN001','电脑',NULL ,4000,NULL union all
select 11,'XSQ001','显示器',1 ,1800,'admin' union all
select 12,'XSQ001','显示器',1 ,1800,'caojieming' union all
select 13,'XSQ001','显示器',2 ,1800,'yuzaowei' union all
select 14,'XSQ001','显示器',2 ,1800,'wangxiaoli' union all
select 15,'XSQ001','显示器',NULL ,1800,NULL union all
select 16,'XSQ001','显示器',NULL,1800,NULL union all
select 17,'ZJ001','主机',1 ,2000,'admin' union all
select 18,'ZJ001','主机',1 ,2000,'caojieming' union all
select 19,'ZJ001','主机',1 ,2000,'zhangsan' union all
select 20,'ZJ001','主机',2 ,2000,'yuzaowei' union all
select 21,'ZJ001','主机',2 ,2000,'wangxiaoli' union all
select 22,'ZJ001','主机',2 ,2000,'guoying' union all
select 23,'ZJ001','主机',NULL ,2000,NULL union all
select 24,'ZJ001','主机',NULL ,2000,NULL 
select 资产编号,资产名称,
使用数量=sum(case when 使用者 is null then 0 else 1 end),
库存=sum(case when 使用者 is null then 1 else 0 end),
总资产原值=sum(价格),                 ---根据需要修改
总资产折旧=sum(价格)*0.5
from @t 
group by 资产编号,资产名称
资产编号       资产名称       使用数量        库存          总资产原值                 总资产折旧
---------- ---------- ----------- ----------- --------------------- ---------------------------------------
DN001      电脑         6           2           32000.00              16000.00000
XSQ001     显示器        4           2           10800.00              5400.00000
ZJ001      主机         6           2           16000.00              8000.00000(3 行受影响)

Answer 4

--貌似结果写反
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-10-12 20:23:23
-- Version:
--      Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
-- May  3 2005 23:18:38 
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[资产编号] varchar(6),[资产名称] varchar(6),[使用部门] int,[价格] int,[使用者] varchar(10))
insert [tb]
select 1,'DN001','电脑',1,4000,'admin' union all
select 2,'DN001','电脑',1,4000,'caojieming' union all
select 3,'DN001','电脑',1,4000,'zhangsan' union all
select 6,'DN001','电脑',2,4000,'yuzaowei' union all
select 7,'DN001','电脑',2,4000,'wangxiaoli' union all
select 8,'DN001','电脑',2,4000,'guoying' union all
select 9,'DN001','电脑',null,4000,null union all
select 10,'DN001','电脑',null,4000,null union all
select 11,'XSQ001','显示器',1,1800,'admin' union all
select 12,'XSQ001','显示器',1,1800,'caojieming' union all
select 13,'XSQ001','显示器',2,1800,'yuzaowei' union all
select 14,'XSQ001','显示器',2,1800,'wangxiaoli' union all
select 15,'XSQ001','显示器',null,1800,null union all
select 16,'XSQ001','显示器',null,1800,null union all
select 17,'ZJ001','主机',1,2000,'admin' union all
select 18,'ZJ001','主机',1,2000,'caojieming' union all
select 19,'ZJ001','主机',1,2000,'zhangsan' union all
select 20,'ZJ001','主机',2,2000,'yuzaowei' union all
select 21,'ZJ001','主机',2,2000,'wangxiaoli' union all
select 22,'ZJ001','主机',2,2000,'guoying' union all
select 23,'ZJ001','主机',null,2000,null union all
select 24,'ZJ001','主机',null,2000,null
--------------开始查询--------------------------
select 
  资产编号,资产名称,
  sum(case  when 使用部门 is not null then 1 else 0 end)as 使用数量,
  sum(case  when 使用部门 is  null then 1 else 0 end) as 库存,
  sum(价格) as 总资产原值,
  sum(价格)*0.5 as 总资产折旧
from 
  tb
group by 
  资产编号,资产名称
----------------结果----------------------------
/* 资产编号   资产名称   使用数量        库存          总资产原值       总资产折旧          
------ ------ ----------- ----------- ----------- -------------- 
DN001  电脑     6           2           32000       16000.0
XSQ001 显示器    4           2           10800       5400.0
ZJ001  主机     6           2           16000       8000.0（所影响的行数为 3 行）*/