表A （视为主表），表A 字段 ID 与其它表的字段绑定
表B 字段 B_ID 与表A 字段ID绑定
表C 字段 C_ID 与表A 字段ID绑定
表D 字段 D_ID 与表A 字段ID绑定
用 join 连接，要求查出的记录条数等表A 的记录数
表B，C，D 会有多条记录但只显示最新的一条 表B，C，D 排序都是降序
不知道如何写这个sql啊
------解决思路----------------------
if OBJECT_ID('A') is not null
drop table A
go
create table A
(id int)
if OBJECT_ID('B') is not null
drop table B
go
create table B
(
B_ID int,
B_Name varchar(20),
CreateDate datetime
)
if OBJECT_ID('C') is not null
drop table C
go
create table C
(
C_ID int,
C_Name varchar(20),
CreateDate datetime
)
if OBJECT_ID('D') is not null
drop table D
go
create table D
(
D_ID int,
D_Name varchar(20),
CreateDate datetime
)
go
insert into A
select 1
union all select 2
union all select 3
go
insert into B(B_ID,B_Name,CreateDate)
select 1,'B-111','2015-07-01'
union all
select 1,'B-222','2015-07-03'
union all
select 1,'B-333',GETDATE()
union all
select 2,'B-444','2015-07-03'
union all
select 2,'B-555',GETDATE()
go
insert into C(C_ID,C_Name,CreateDate)
select 1,'C-111','2015-07-01'
union all
select 1,'C-222','2015-07-03'
union all
select 1,'C-333',GETDATE()
union all
select 2,'C-444','2015-07-03'
union all
select 2,'C-555',GETDATE()
go
insert into D(D_ID,D_Name,CreateDate)
select 1,'D-111','2015-07-01'
union all
select 1,'D-222','2015-07-03'
union all
select 1,'D-333',GETDATE()
union all
select 2,'C-444','2015-07-03'
union all
select 2,'C-555',GETDATE()
go
select * from A tt0
left join (
select t0.id,
t1.B_ID,t1.B_Name,t1.CreateDate date1,
t2.C_ID,t2.C_Name,t2.CreateDate date2,
t3.D_ID,t3.D_Name,t3.CreateDate date3
from A t0
cross apply
(select top 1 * from B where B.B_ID=t0.id order by CreateDate desc) t1
cross apply
(select top 1 * from C where C.C_ID=t0.id order by CreateDate desc) t2
cross apply
(select top 1 * from D where D.D_ID=t0.id order by CreateDate desc) t3
) tt1 on tt0.id=tt1.id
------解决思路----------------------
create table A
(
[Id] [uniqueidentifier] NOT NULL,
[EventNo] [nvarchar](50) NULL,
[EventName] [nvarchar](200) NULL
)
create table B
(
[Id] [uniqueidentifier] NOT NULL,
[AId] [uniqueidentifier] NULL,
[status] int,
[EventDetail] nvarchar(500) null
)
Declare @Id [uniqueidentifier]
select @Id = NEWID()
insert into A select @Id,'1','test1'
insert into B select NEWID(),@Id,0,'detail10'
insert into B select NEWID(),@Id,0,'detail11'
insert into B select NEWID(),@Id,3,'detail12'
select @Id = NEWID()
insert into A select @Id,'2','test2'
insert into B select NEWID(),@Id,0,'detail20'
insert into B select NEWID(),@Id,3,'detail21'
select @Id = NEWID()
insert into A select @Id,'3','test3'
select @Id = NEWID()
insert into A select @Id,'4','test4'
insert into B select NEWID(),@Id,0,'detail40'
insert into B select NEWID(),@Id,1,'detail41'
insert into B select NEWID(),@Id,3,'detail42'
select A.EventNo,A.EventName,B.EventDetail,* From A left join B on a.Id = b.AId
select Aid, MIN(status)as status from B group by AId
--方法1
SELECT A.EventNo,A.EventName,T2.EventDetail
FROM A
OUTER APPLY(SELECT TOP 1 EventDetail FROM B WHERE A.ID=B.AID ORDER BY status)T2
--方法2
SELECT EventNo,EventName,EventDetail
FROM(
SELECT A.EventNo,A.EventName,B.EventDetail
,ROW_NUMBER()OVER(PARTITION BY A.ID ORDER BY B.status)RN
FROM A
LEFT JOIN B ON A.ID=B.AID
--WHERE 其他条件 加在这最好
)T
WHERE RN=1
ORDER BY EventNo
------解决思路----------------------
参考一下，这样写
WITH A(id) AS(
SELECT 1 UNION ALL
SELECT 2
),
B(b_id,DT) AS (
SELECT 1,'2015-08-08' UNION ALL
SELECT 1,'2015-08-07' UNION ALL
SELECT 1,'2015-08-06'
),
C(c_id,DT) AS (
SELECT 2,'2015-08-08' UNION ALL
SELECT 2,'2015-08-07' UNION ALL
SELECT 1,'2015-08-06'
),
D(d_id,DT) AS (
SELECT 1,'2015-08-08' UNION ALL
SELECT 2,'2015-08-07' UNION ALL
SELECT 2,'2015-08-06'
)
SELECT
A.*,B.*,C.*,D.*
FROM
A OUTER APPLY ( SELECT TOP 1 B.* FROM B WHERE A.id=b.B_ID ORDER BY B.DT DESC) B
OUTER APPLY ( SELECT TOP 1 C.* FROM C WHERE A.id=C.C_ID ORDER BY B.DT DESC) C
OUTER APPLY ( SELECT TOP 1 D.* FROM D WHERE A.id=D.d_ID ORDER BY B.DT DESC) D
------解决思路----------------------
--加case when的判断
;with cte as
(
select cnt=row_number() over (partition by A表需查的字段 order by 你实际需要的排序字段) , a.*,b.*,c.*,d.*
from 你上边的逻辑
)
--下方case when中的判断字段是你显示A表的字段，字段名自己去补齐
select (case when cnt = 1 then a.[name] else '' end) , --字符型字段
(case when cnt = 1 then ltrim(a.[age]) else '' end) , --数字型 不显示的按''字符处理
(case when cnt = 1 then a.[score] else 0 end) , --数字型 不显示的按0处理
b.*,c.*,d.*
from cte
------解决思路----------------------
with a (id,emp_id,emp_name,dept_id) as (
select 1,'E01','罗代均','D01' union all
select 2,'E02','罗曾英','D02' union all
select 3,'E03','老焦','D03' union all
select 4,'E04','老肖','D05'
),
b (dept_id,dept_name,id) as (
select 'D01','资讯课',1 union all
select 'D02','生产三课',2 union all
select 'D03','生管课',3 union all
select 'D04','采购课',4 union all
select 'D03','其它课',5
)
select a.id,a.emp_id,a.emp_name,a.dept_id,b.dept_name from a left join b on a.dept_id=b.dept_id
and a.id=b.id
