有单位给单位打分的2张表如下:
表a
id name(单位名)表btableid fordepartid fromdepartid datetime表b里的fordepartid和fromdepartid从表a的id里来,且fordepartid和fromdepartid可在一行重复 datetime为判断条件smalldatetime类型的要求:把每月或某月 没有打分的单位或者没有被打分的单位名都查出来;(含所有单位给其他单位打分)谢啦
表a
id name(单位名)表btableid fordepartid fromdepartid datetime表b里的fordepartid和fromdepartid从表a的id里来,且fordepartid和fromdepartid可在一行重复 datetime为判断条件smalldatetime类型的要求:把每月或某月 没有打分的单位或者没有被打分的单位名都查出来;(含所有单位给其他单位打分)谢啦
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
name
from
a
where
not exists(select 1 from b where fordepartid=a.id or fromdepartid=a.id)
现在有交叉打分的记录表及单位基本信息表如下:CREATE TABLE [dbo].[Branch](
[ID] [uniqueidentifier] NULL,
[BranchName] [varchar](100) NOT NULL,
[IsUse] [char](1) NULL
) ON [PRIMARY]
insert into Branch select '095a9817-98e3-4c67-8c0f-c60cd48afb9b','一分厂','1'
insert into Branch select 'b59f8f76-3410-4887-898d-9803c8278170','二分厂','1'
insert into Branch select '370e564f-bd23-4b78-a76a-7c4e138c15fd','三分厂','1'
insert into Branch select '664fc11a-3340-4a3d-b610-95d48fd22c29','四分厂','1'CREATE TABLE [dbo].[Ratingtable](
[ratingtableid] [uniqueidentifier] NOT NULL,
[Plusdepartforid] [uniqueidentifier] NOT NULL,
[Plusdepartfromid] [uniqueidentifier] NOT NULL,
[Average] [decimal](18, 2) NULL,
[Ratingmonth] [smalldatetime] NULL
)
insert into Ratingtable select '7a597209-8e03-4b69-9bc4-b255b9cf5fc6','095a9817-98e3-4c67-8c0f-c60cd48afb9b','370e564f-bd23-4b78-a76a-7c4e138c15fd','98','2011-03-22'
------求如下结果输出 其中Plusdepartforid和Plusdepartfromid都与Branch.ID对应,Branch.IsUse=1则需要打分
Branch.BranchName Ratingtable.Plusdepartforid Ratingtable.Plusdepartfromid Ratingtable.Average Ratingtable.Ratingmonth
---要求 1 求出本月已经打分的单位 2 本月未打分的单位 3 本月已经被打分的单位 4 本月还未被打分的单位 时间条件为Ratingmonth ⊙注意是各单位交叉打分
--本月已经打分的单位
select b.BranchName, r.[Plusdepartforid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartforid] = b.[ID]
where r.[Plusdepartforid] is not null and b.IsUse=1 and month([Ratingmonth]) = month(getdate())--本月未打分的单位
select b.BranchName, r.[Plusdepartforid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartforid] = b.[ID]
where r.[Plusdepartforid] is null and b.IsUse=1 and [Ratingmonth] is null
--本月已经被打分的单位
select b.BranchName,r.[Plusdepartfromid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartfromid] = b.[ID]
where r.[Plusdepartfromid] is not null and b.IsUse=1 and month([Ratingmonth]) = month(getdate())--本月未被打分的单位
select b.BranchName,r.[Plusdepartfromid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartfromid] = b.[ID]
where r.[Plusdepartfromid] is null and b.IsUse=1 and r.[Ratingmonth] is null
--查询结果
BranchName Plusdepartforid
---------------------------------------------------------------------------------------------------- ------------------------------------
一分厂 095A9817-98E3-4C67-8C0F-C60CD48AFB9B(1 行受影响)BranchName Plusdepartforid
---------------------------------------------------------------------------------------------------- ------------------------------------
二分厂 NULL
三分厂 NULL
四分厂 NULL(3 行受影响)BranchName Plusdepartfromid
---------------------------------------------------------------------------------------------------- ------------------------------------
三分厂 370E564F-BD23-4B78-A76A-7C4E138C15FD(1 行受影响)BranchName Plusdepartfromid
---------------------------------------------------------------------------------------------------- ------------------------------------
一分厂 NULL
二分厂 NULL
四分厂 NULL(3 行受影响)
--本月未打分的单位
select b.BranchName
from [Branch] b
where b.IsUse =1 and not exists(select 1 from [Ratingtable] r where b.ID =r.[Plusdepartforid] and month([Ratingmonth]) = month(getdate()))
BranchName
----------------------------------------------------------------------------------------------------
二分厂
三分厂
四分厂(3 行受影响)
是要交叉打分的:不能说在打分表里只要查到一分厂给二分厂打了分,就算作他本月已经打完分了,实际他还没有给其他单位打分。应该是一分厂给其他所有单位包括他自己都打了分才能算本月已经打分。
USE TEST
if object_id('Branch') is not null drop table Branch
GO
CREATE TABLE [dbo].[Branch](
[ID] [uniqueidentifier] NULL,
[BranchName] [varchar](100) NOT NULL,
[IsUse] [char](1) NULL
) ON [PRIMARY]
insert into Branch select '095a9817-98e3-4c67-8c0f-c60cd48afb9b','一分厂','1'
insert into Branch select 'b59f8f76-3410-4887-898d-9803c8278170','二分厂','1'
insert into Branch select '370e564f-bd23-4b78-a76a-7c4e138c15fd','三分厂','1'
insert into Branch select '664fc11a-3340-4a3d-b610-95d48fd22c29','四分厂','1'if object_id('Ratingtable') is not null drop table Ratingtable
GO
CREATE TABLE [dbo].[Ratingtable](
[ratingtableid] [uniqueidentifier] NOT NULL,
[Plusdepartforid] [uniqueidentifier] NOT NULL,
[Plusdepartfromid] [uniqueidentifier] NOT NULL,
[Average] [decimal](18, 2) NULL,
[Ratingmonth] [smalldatetime] NULL
)
insert into Ratingtable select '7a597209-8e03-4b69-9bc4-b255b9cf5fc6','095a9817-98e3-4c67-8c0f-c60cd48afb9b','370e564f-bd23-4b78-a76a-7c4e138c15fd','98',getdate()
------求如下结果输出 其中Plusdepartforid和Plusdepartfromid都与Branch.ID对应,Branch.IsUse=1则需要打分
Branch.BranchName Ratingtable.Plusdepartforid Ratingtable.Plusdepartfromid Ratingtable.Average Ratingtable.Ratingmonth
/*
要求 1 求出本月已经打分的单位
2 本月未打分的单位
3 本月已经被打分的单位
4 本月还未被打分的单位 时间条件为Ratingmonth ⊙注意是各单位交叉打分
*/--本月已经打分的单位
select b.BranchName, r.[Plusdepartforid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartforid] = b.[ID]
where r.[Plusdepartforid] is not null and b.IsUse=1 and month([Ratingmonth]) = month(getdate())--本月未打分的单位
--select b.BranchName, r.[Plusdepartforid]
--from [Branch] b
--left join [Ratingtable] r on r.[Plusdepartforid] = b.[ID]
--where r.[Plusdepartforid] is null and b.IsUse=1 and [Ratingmonth] is nullselect b.BranchName
from [Branch] b
where b.IsUse =1 and not exists(select 1 from [Ratingtable] r where b.ID =r.[Plusdepartforid] and month([Ratingmonth]) = month(getdate())) --本月已经被打分的单位
select b.BranchName,r.[Plusdepartfromid]
from [Branch] b
left join [Ratingtable] r on r.[Plusdepartfromid] = b.[ID]
where r.[Plusdepartfromid] is not null and b.IsUse=1 and month([Ratingmonth]) = month(getdate())--本月未被打分的单位
--select b.BranchName,r.[Plusdepartfromid]
--from [Branch] b
--left join [Ratingtable] r on r.[Plusdepartfromid] = b.[ID]
--where r.[Plusdepartfromid] is null and b.IsUse=1 and r.[Ratingmonth] is nullselect b.BranchName
from [Branch] b
where b.IsUse =1 and not exists(select 1 from [Ratingtable] r where b.ID =r.[Plusdepartfromid] and month([Ratingmonth]) = month(getdate()))
select b.BranchName from [Ratingtable] r
left join [Branch] b on r.[Plusdepartforid] = b.[ID]
group by b.BranchName
having count(*) = (select count(*) from [Branch] where IsUse =1) -1--是不是需要这样的结果 如果没有完全打分的不显示
SELECT b.BranchName
FROM Ratingtable AS r LEFT OUTER JOIN
Branch AS b ON r.Plusdepartfromid = b.ID AND MONTH(r.Ratingmonth) = MONTH(GETDATE())
GROUP BY b.BranchName
HAVING (COUNT(*) =
(SELECT COUNT(*) AS Expr1
FROM Branch
WHERE (IsUse = 1)))