求在shuxing TM MPM中,各jz下有多少人。表A:(gh表示一个人的ID号,不重复)
jz xm gh
A 张一 001
A 李一 002
A 李二 003
B 张二 004
B 王二 005
B 赵二 006
C 张三 007
C 李三 008
C 赵三 009
D 张四 010
E 张五 011表B:
jz shuxing
A TM
B TM
C MPM
D MPM
E MPM
jz xm gh
A 张一 001
A 李一 002
A 李二 003
B 张二 004
B 王二 005
B 赵二 006
C 张三 007
C 李三 008
C 赵三 009
D 张四 010
E 张五 011表B:
jz shuxing
A TM
B TM
C MPM
D MPM
E MPM
解决方案 »
- 怎么删除sql server management 2008 中不用的连接?
- 求助一个sql问题
- 有1000条记录,某个字段从1到1000,里面缺几条,怎么把这几条找出来?
- sql in not in where 混搭 效率低下
- 白分求救啊,简单问题复杂化了,怎么搞定啊
- 求一SQL语句,查询到结果后进行处理,再显示出来。
- 请教一个SQL语句!!!,在线等,谢谢。。
- 求SQL语句
- 一个.SQL文件(2000或2005)最多允许执行的代码是多少行
- 如何获得sql数据库中某个表内容的更新日期(最近一次)
- 求一方法
- SQL Server 2005 中执行 xp_cmdshell 时出现类似死循环的状况
SELECT
B.JZ,b.shuxing,
count(a.jz) as con
FROM B
LEFT JOIN A ON A.JZ=B.JZ
GROUP BY B.JZ,b.shuxing
go
--> -->
declare @A table([jz] nvarchar(1),[xm] nvarchar(2),[gh] nvarchar(3))
Insert @A
select N'A',N'张一',N'001' union all
select N'A',N'李一',N'002' union all
select N'A',N'李二',N'003' union all
select N'B',N'张二',N'004' union all
select N'B',N'王二',N'005' union all
select N'B',N'赵二',N'006' union all
select N'C',N'张三',N'007' union all
select N'C',N'李三',N'008' union all
select N'C',N'赵三',N'009' union all
select N'D',N'张四',N'010' union all
select N'E',N'张五',N'011'declare @B table([jz] nvarchar(1),[shuxing] nvarchar(3))
Insert @B
select N'A',N'TM' union all
select N'B',N'TM' union all
select N'C',N'MPM' union all
select N'D',N'MPM' union all
select N'E',N'MPM'SELECT B.JZ,b.shuxing, count(a.jz) as con FROM @B AS B LEFT JOIN @A AS A ON A.JZ=B.JZ GROUP BY B.JZ,b.shuxing ORDER BY 1
/*
JZ shuxing con
A TM 3
B TM 3
C MPM 3
D MPM 1
E MPM 1
*/
TM MPM
6 5
这么结果呢。
(TM 里面JZ为:A B)
(MPM 里面JZ为:C D E)
TM MPM
6 5
go
--> -->
declare @A table([jz] nvarchar(1),[xm] nvarchar(2),[gh] nvarchar(3))
Insert @A
select N'A',N'张一',N'001' union all
select N'A',N'李一',N'002' union all
select N'A',N'李二',N'003' union all
select N'B',N'张二',N'004' union all
select N'B',N'王二',N'005' union all
select N'B',N'赵二',N'006' union all
select N'C',N'张三',N'007' union all
select N'C',N'李三',N'008' union all
select N'C',N'赵三',N'009' union all
select N'D',N'张四',N'010' union all
select N'E',N'张五',N'011'declare @B table([jz] nvarchar(1),[shuxing] nvarchar(3))
Insert @B
select N'A',N'TM' union all
select N'B',N'TM' union all
select N'C',N'MPM' union all
select N'D',N'MPM' union all
select N'E',N'MPM'
SELECT
b.shuxing,
count(a.jz) as con
FROM @B AS B
LEFT JOIN @A AS A ON A.JZ=B.JZ GROUP BY b.shuxing
/*
shuxing con
MPM 5
TM 6
*/SELECT
sum(CASE WHEN [jz] IN('A','B') THEN 1 ELSE 0 END) AS TM,
sum(CASE WHEN [jz] IN('C','D','E') THEN 1 ELSE 0 END) AS MPM
FROM @A
/*
TM MPM
6 5*/
b.shuxing, count(a.jz) as con
FROM
@B AS B LEFT JOIN @A AS A ON A.JZ=B.JZ
GROUP BY
b.shuxing ORDER BY 1
SELECT shuxing ,
COUNT(dbo.A.jz) AS cnt
FROM dbo.A
JOIN dbo.B ON dbo.A.jz = dbo.B.jz
GROUP BY shuxing
ORDER BY shuxing/*
MPM 5
TM 6
*/
SELECT SUM(CASE shuxing WHEN 'TM' THEN 1 ELSE 0 END) AS TM,
SUM(CASE shuxing WHEN 'MPM' THEN 1 ELSE 0 END) AS MPM
FROM dbo.A
JOIN dbo.B ON dbo.A.jz = dbo.B.jz/*
TM MPM
6 5
*/
(
jz varchar(1),
xm varchar(10),
gh varchar(3)
)
insert into t1
select 'A', '张一', '001' union all
select 'A', '李一', '002' union all
select 'A', '李二', '003' union all
select 'B', '张二', '004' union all
select 'B', '王二', '005' union all
select 'B', '赵二', '006' union all
select 'C', '张三', '007' union all
select 'C', '李三', '008' union all
select 'C', '赵三', '009' union all
select 'D', '张四', '010' union all
select 'E', '张五', '011'
create table t2
(
jz varchar(1),
shuxing varchar(5)
)
insert into t2
select 'A', 'TM' union all
select 'B', 'TM' union all
select 'C', 'MPM' union all
select 'D', 'MPM' union all
select 'E', 'MPM'
select * from t1
select * from t2;with aaa as
(select t1.*,t2.shuxing from t1 inner join t2 on t1.jz=t2.jz)
select shuxing,COUNT(shuxing) as account from aaa group by shuxingshuxing account
MPM 5
TM 6
B.shuxing = 'TM' OR B.shuxing = 'MPM' GROUP BY A.jz 结果:
TM group的 3
MPM group的 2以下是我的数据:
表A:(gh表示一个人的ID号,不重复)
jz xm gh
A 张一 001
A 李一 002
A 李二 003
B 张二 004
B 王二 005
B 赵二 006
表B:
jz shuxing
A TM
B TM
C MPM
D MPM
E MPM
go
--> -->
declare @A table([jz] nvarchar(1),[xm] nvarchar(2),[gh] nvarchar(3))
Insert @A
select N'A',N'张一',N'001' union all
select N'A',N'李一',N'002' union all
select N'A',N'李二',N'003' union all
select N'B',N'张二',N'004' union all
select N'B',N'王二',N'005' union all
select N'B',N'赵二',N'006' union all
select N'C',N'张三',N'007' union all
select N'C',N'李三',N'008' union all
select N'C',N'赵三',N'009' union all
select N'D',N'张四',N'010' union all
select N'E',N'张五',N'011'declare @B table([jz] nvarchar(1),[shuxing] nvarchar(3))
Insert @B
select N'A',N'TM' union all
select N'B',N'TM' union all
select N'C',N'MPM' union all
select N'D',N'MPM' union all
select N'E',N'MPM'
SELECT
b.shuxing,
count(a.jz) as con
FROM @B AS B
LEFT JOIN @A AS A ON A.JZ=B.JZ GROUP BY b.shuxing
/*
shuxing con
MPM 5
TM 6
*/SELECT
sum(CASE WHEN [jz] IN('A','B') THEN 1 ELSE 0 END) AS TM,
sum(CASE WHEN [jz] IN('C','D','E') THEN 1 ELSE 0 END) AS MPM
FROM @A
/*
TM MPM
6 5*/