username(用户名) in(入) out(出) time(时间)
张三 1 1 2008-1-1
李四 1 2 2008-1-1
张三 1 3 2008-1-1
张三 2 2 2008-1-1
李四 1 2 2008-1-1
张三 1 3 2008-1-1
我想实现一个查询结果如下
username(用户名) in=out(总数) in<>out(总数) 用户记录总数
张三 2 2 4
李四 2 0 2
能实现么!
张三 1 1 2008-1-1
李四 1 2 2008-1-1
张三 1 3 2008-1-1
张三 2 2 2008-1-1
李四 1 2 2008-1-1
张三 1 3 2008-1-1
我想实现一个查询结果如下
username(用户名) in=out(总数) in<>out(总数) 用户记录总数
张三 2 2 4
李四 2 0 2
能实现么!
select name,sum(case when in=out then 1 else 0 end),
sum(case when in<>out then 1 else 0),count(*) from tb group by name
drop table #t
Go
Create table #t([username] nvarchar(2),[in] int,[out] int,[time] nvarchar(8))
Insert #t
select N'张三',1,1,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1' union all
select N'张三',2,2,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1'
Go
Select [username],
[in]=sum(case when [in]=[out] then 1 else 0 end)
,[out]=sum(case when [in]=[out] then 1 else 0 end),
count(1) as用戶數
from #t group by [username]
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([username] nvarchar(2),[in] int,[out] int,[time] nvarchar(8))
Insert #T
select '张三',1,1,'2008-1-1' union all
select '李四',1,2,'2008-1-1' union all
select '张三',1,3,'2008-1-1' union all
select '张三',2,2,'2008-1-1' union all
select '李四',1,2,'2008-1-1' union all
select '张三',1,3,'2008-1-1'
Go
Select * from #T--我想实现一个查询结果如下
--
--username(用户名) in=out(总数) in <>out(总数) 用户记录总数
-- 张三 2 2 4
-- 李四 2 0 2
select distinct username,
in1=(select count(*) from #T where username=a.username and [in]=out),
out1=(select count(*) from #T where username=a.username and [in]=out),
total=(select count(*) from #T where username=a.username)
from #T a
order by username
drop table #t
Go
Create table #t([username] nvarchar(2),[in] int,[out] int,[time] nvarchar(8))
Insert #t
select N'张三',1,1,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1' union all
select N'张三',2,2,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1'
Go
Select [username],
[in]=sum(case when [in]=[out] then 1 else 0 end)
,[out]=sum(case when [in]<>[out] then 1 else 0 end),
count(1) as用戶數
from #t group by [username]
(6 個資料列受到影響)
username in out as用戶數
-------- ----------- ----------- -----------
李四 0 2 2
张三 2 2 4(2 個資料列受到影響)
declare @tb table([username(用户名)] nvarchar(2),[in(入)] int,[out(出)] int,[time(时间)] nvarchar(8))
Insert @tb
select N'张三',1,1,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1' union all
select N'张三',2,2,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1'
Select [username(用户名)],
sum(case when [in(入)] =[out(出)] then 1 else 0 end) as 'in=out(总数) ' ,
sum(case when [in(入)] =[out(出)] then 0 else 1 end) as 'in <>out(总数)' ,
count(1) as '用户记录总数 ' from @tb group by [username(用户名)]
/*
username(用户名) in=out(总数) in <>out(总数) 用户记录总数
------------- ----------- ------------ -----------
李四 0 2 2
张三 2 2 4
*/
create table a
(
username nvarchar(10),
[in] int,
[out] int,
time datetime
)
insert into a
SELECT
N'张三',1,1,'2008-1-1' union all select
N'李四',1,2,'2008-1-1' union all select
N'张三',1,3,'2008-1-1' union all select
N'张三',2,2,'2008-1-1' union all select
N'李四',1,2,'2008-1-1' union all select
N'张三',1,3,'2008-1-1'
select username,
sum(case when [in]= [out] then 1 else 0 end) as 'in = out',
sum(case when [in] <> [out] then 1 else 0 end) as 'in <> out',
count(username) as N'用户记录总数 '
from a
group by username
结果
username in = out in <> out 用户记录总数
---------- ----------- ----------- -----------
张三 2 2 4
李四 0 2 2(2 row(s) affected)
count(case when ins=outs then 1 else 0 end ) as same,
count ( case when ins <>outs then 1 else 0 end ) as diffrent from b group by names
你上面的SQL 应该是这样来写
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([username] nvarchar(2),[in] int,[out] int,[time] nvarchar(8))
Insert #t
select N'张三',1,1,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1' union all
select N'张三',2,2,N'2008-1-1' union all
select N'李四',1,2,N'2008-1-1' union all
select N'张三',1,3,N'2008-1-1'
Go
Select [username],
[in=out]=sum(case when [in]=[out] then 1 else 0 end)
,[in<>out]=sum(case when [in]=[out] then 0 else 1 end),
count(*) as 用戶數
from #t group by [username]