求联合查询存储过程
table A:tid(pk),userid table B：oid(pk),tid,ostatus,rid,rstatus
现在我想给定userid查询表B，如果rid为空，则返回ostatus以及count(rid为空)，如果rid不为空，则返回rstatus 及count(rid不为空 )
[解决办法]
SELECT
a.userid,
b.oid,
b.tid,
b.rid,
[status] = (CASE WHEN b.rid IS NULL THEN b.ostatus ELSE b.rstatus END),
[count] = COUNT(*)--不知道楼主的COUNT指的是什么？
FROM tableA a
INNER JOIN tabelB b
ON a.tid = b.tid
WHERE a.userid = 1
create table tableA(tid int, UserID nvarchar(10))
insert into tableA values (1,'张三')
insert into tableA values (2,'李四')
create table tableB(oid int,tid int,ostatus int, rid int ,rstatus int)
insert into tableB values (1,1,99,12,88)
insert into tableB values (2,2,66,NULL,77)
insert into tableB values (3,2,78,NULL,54)
insert into tableB values (4,2,78,NULL,55)
insert into tableB values (5,1,98,12,87)
insert into tableB values (6,1,99,12,88)
IFEXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('TEST') AND type = 'P')
DROP PROC TEST
GO
/*
EXEC TEST
@USER_ID= '李四'
*/
CREATE PROC TEST
@USER_IDVARCHAR(10)
AS
BEGIN
Declare @ISNULL As int
set @ISNULL=(select max(b.rid)
from tableA a
inner join tableB b
on a.tid=b.tid
where a.UserID=@USER_ID
)
if(@ISNULL=NULL)
select b.ostatus,
[COUNT]= COUNT(case when b.rid is not null then 1 else 0 end)
from tableA a
inner join tableB b
on a.tid=b.tid
where a.UserID=@USER_ID
group by b.ostatus
else
select b.rstatus,
[COUNT]= COUNT(case when b.rid is not null then 1 else 0 end)
from tableA a
inner join tableB b
on a.tid=b.tid
where a.UserID=@USER_ID
group by b.rstatus
END
/*
李四
rstatus COUNT
----------- -----------
54 1
55 1
77 1
张三
rstatus COUNT
----------- -----------
87 1
88 2
*/
