卡号表中status 代表的意思 1 正常(start) 2停用(stop) 0过期(over)
卡号表
codeid userid codeno status
1 1 41676996642 1
2 1 32176916344 1
3 1 51326161634 0
4 1 74656161634 2
5 2 66526161634 1
6 2 96526161634 2
用户表
userid username
1 zhangsan
2 lisi统计卡号各个状态下的个数,得到一下结果
username start stop over
zhangsan 2 1 1
lisi 1 1 0SQL统计行转列
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-26 20:13:55
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[code]
if object_id('[code]') is not null drop table [code]
go
create table [code]([codeid] int,[userid] int,[codeno] bigint,[status] int)
insert [code]
select 1,1,41676996642,1 union all
select 2,1,32176916344,1 union all
select 3,1,51326161634,0 union all
select 4,1,74656161634,2 union all
select 5,2,66526161634,1 union all
select 6,2,96526161634,2--> 测试数据:[Users]
if object_id('[Users]') is not null drop table [Users]
go
create table [Users]([userid] int,[username] varchar(8))
insert [Users]
select 1,'zhangsan' union all
select 2,'lisi'
--------------开始查询--------------------------
select u.userid,u.username,[start]=sum( CASE WHEN [status]=1 THEN 1 ELSE 0 END ),
[stop]=sum( CASE WHEN [status]=2 THEN 1 ELSE 0 END ),
[over]=sum( CASE WHEN [status]=0 THEN 1 ELSE 0 END )
from [Users] u INNER JOIN [code] c ON u.userid=c.userid
GROUP BY u.userid,u.username
----------------结果----------------------------
/*
userid username start stop over
----------- -------- ----------- ----------- -----------
1 zhangsan 2 1 1
2 lisi 1 1 0
*/
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2013-04-26 20:22:56
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[Users]
if object_id('[Users]') is not null
drop table [Users]
go
create table [Users]
(
[姓名] varchar(4),
[性别] varchar(2),
[年龄] int
)
insert [Users]
select '张三','男',26
--> 测试数据:[BState]
if object_id('[BState]') is not null
drop table [BState]
go
create table [BState]
(
[姓名] varchar(4),
[类型] varchar(6),
[借款] int
)
insert [BState]
select '张三','通过',1800 union all
select '张三','不通过',2000 union all
select '张三','通过',1900
goselect
a.*,
SUM(case when [类型]='通过' then 1 else 0 end) as 通过,
SUM(case when [类型]='不通过' then 1 else 0 end) as 不通过
from
Users a inner join BState b on a.姓名=b.姓名
group by a.年龄,a.姓名,a.性别
/*
姓名 性别 年龄 通过 不通过
------------------------------------------------------------
张三 男 26 2 1
*/