--> 测试数据:#表a if object_id('tempdb.dbo.#表a') is not null drop table #表a go create table #表a([序号] int,[姓名] varchar(6)) insert #表a select 2,'叶军2' union all select 3,'徐成' union all select 8,'朱兵' union all select 9,'万金' union all select 10,'周明亮' union all select 11,'王俊' union all select 13,'吴辉平' union all select 14,'王军' union all select 15,'付总平' --> 测试数据:#表b if object_id('tempdb.dbo.#表b') is not null drop table #表b go create table #表b([扣分] int,[扣款] int,[姓名] varchar(7)) insert #表b select 0,20,'彭德成' union all select 0,20,'吴辉平' union all select 0,50,'全员' union all select 3,100,'全员' union all select 0,20,'周明亮' union all select 0,20,'周明亮' select * from #表bselect a.*,sum(b.扣分),sum(b.扣款) from #表a a left join #表b b on a.姓名=b.姓名 group by a.序号,a.姓名
使用内连接就可以了 inner join ..........on
select a.* , isnull(b.ee,'no') ee , isnull(b.ff,'no') ff from a left join b on a.aa = b.dd and 录入时间字段 between 时间1 and 时间2
我用以下代码: SELECT dic_dept.name, SUM(checkinfo.扣款) AS Expr1, SUM(checkinfo.扣分) AS Expr2, COUNT(checkinfo.责任单位) AS Expr3 FROM dic_dept LEFT OUTER JOIN checkinfo ON dic_dept.name = checkinfo.责任单位 GROUP BY dic_dept.name 可以列出所有单位的信息,没有扣分,扣款的也能统计出来 但加了where后 SELECT dic_dept.name, SUM(checkinfo.扣款) AS Expr1, SUM(checkinfo.扣分) AS Expr2, COUNT(checkinfo.责任单位) AS Expr3 FROM dic_dept LEFT OUTER JOIN checkinfo ON dic_dept.name = checkinfo.责任单位 WHERE (checkinfo.检查时间 = '2010-11-1') GROUP BY dic_dept.name只显示出检查时间里才有的单位信息。要怎么修改呢
if object_id('tempdb.dbo.#表a') is not null drop table #表a
go
create table #表a([序号] int,[姓名] varchar(6))
insert #表a
select 2,'叶军2' union all
select 3,'徐成' union all
select 8,'朱兵' union all
select 9,'万金' union all
select 10,'周明亮' union all
select 11,'王俊' union all
select 13,'吴辉平' union all
select 14,'王军' union all
select 15,'付总平'
--> 测试数据:#表b
if object_id('tempdb.dbo.#表b') is not null drop table #表b
go
create table #表b([扣分] int,[扣款] int,[姓名] varchar(7))
insert #表b
select 0,20,'彭德成' union all
select 0,20,'吴辉平' union all
select 0,50,'全员' union all
select 3,100,'全员' union all
select 0,20,'周明亮' union all
select 0,20,'周明亮'
select * from #表bselect a.*,sum(b.扣分),sum(b.扣款) from #表a a left join #表b b
on a.姓名=b.姓名
group by a.序号,a.姓名
select a.* , isnull(b.ee,'no') ee , isnull(b.ff,'no') ff
from a left join b
on a.aa = b.dd and 录入时间字段 between 时间1 and 时间2
SELECT dic_dept.name, SUM(checkinfo.扣款) AS Expr1, SUM(checkinfo.扣分) AS Expr2, COUNT(checkinfo.责任单位) AS Expr3
FROM dic_dept LEFT OUTER JOIN
checkinfo ON dic_dept.name = checkinfo.责任单位
GROUP BY dic_dept.name
可以列出所有单位的信息,没有扣分,扣款的也能统计出来
但加了where后
SELECT dic_dept.name, SUM(checkinfo.扣款) AS Expr1, SUM(checkinfo.扣分) AS Expr2, COUNT(checkinfo.责任单位) AS Expr3
FROM dic_dept LEFT OUTER JOIN
checkinfo ON dic_dept.name = checkinfo.责任单位
WHERE (checkinfo.检查时间 = '2010-11-1')
GROUP BY dic_dept.name只显示出检查时间里才有的单位信息。要怎么修改呢