多说无益,直接上表
use tempdb
go
--主表A
if not OBJECT_ID('tempdb..#A') IS NULL
drop table #A
CREATE TABLE #A(Auid int)
INSERT INTO #A(Auid)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
GO
--从表B
if not OBJECT_ID('tempdb..#B') IS NULL
drop table #B
CREATE TABLE #B(Auid int,B1 varchar(10) null)
INSERT INTO #B(Auid,B1)
SELECT 1,'' UNION ALL
SELECT 2,'' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 3,'A'
--SELECT 1,' ' UNION ALL(暂不考虑,也视为无记录)
--SELECT 1,null UNION ALL(同上)
--SELECT 2,null UNION ALL(同上)
--要的效果结果(希望效率比较优化的)
/*
Auid B1
-------------
1 0 --A中的1在B中没有正常记录
2 1 --A中的1在B中有1个记录
3 2 --A中的1在B中有两个记录
4 0 --A中的1在B中没有记录
*/我是这样做的
表连接 加 sum case
不知道效率怎样
use tempdb
go
--主表A
if not OBJECT_ID('tempdb..#A') IS NULL
drop table #A
CREATE TABLE #A(Auid int)
INSERT INTO #A(Auid)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
GO
--从表B
if not OBJECT_ID('tempdb..#B') IS NULL
drop table #B
CREATE TABLE #B(Auid int,B1 varchar(10) null)
INSERT INTO #B(Auid,B1)
SELECT 1,'' UNION ALL
SELECT 2,'' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 3,'A'
--SELECT 1,' ' UNION ALL(暂不考虑,也视为无记录)
--SELECT 1,null UNION ALL(同上)
--SELECT 2,null UNION ALL(同上)
--要的效果结果(希望效率比较优化的)
/*
Auid B1
-------------
1 0 --A中的1在B中没有正常记录
2 1 --A中的1在B中有1个记录
3 2 --A中的1在B中有两个记录
4 0 --A中的1在B中没有记录
*/我是这样做的
表连接 加 sum case
不知道效率怎样
from #A as a left join #B as b on a.auid=b.auid
where b.B1<>''
group by a.auid
ISNULL(b1, 0) b1
FROM #a a
LEFT JOIN ( SELECT Auid ,
COUNT(1) b1
FROM #b
WHERE b1 <> ''
GROUP BY Auid
) b ON a.Auid = b.Auid
(select * from #B where rtrim(ltrim(B1))<>'') b
on a.Auid =b.auid
group by a.Auid
SELECT
A.Auid,SUM(CASE B.B1 WHEN ''THEN 0 ELSE 1 END)
FROM
A INNER JOIN B ON A.Auid=B.Auid
group by A.Auid
不知道我这效率咋样
我想要效率好点的
执行计划还不懂得看...
SELECT
title,COUNT(TITLE),SUM(CASE BB.SINGE WHEN 0 THEN 1 ELSE 0 END)
FROM
aa LEFT JOIN BB ON AA.kk=BB.KK GROUP BY TITLE
(select * from #B where B1<>'') b
on a.Auid =b.auid
group by a.Auid
use tempdb
go
if not OBJECT_ID('[tempdb..#A]') IS not NULL
drop table #A
CREATE TABLE #A(Auid int)
INSERT INTO #A(Auid)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 GO
if not OBJECT_ID('[tempdb..#B]') IS not NULL
drop table #B
CREATE TABLE #B(Auid int,B1 varchar(10) null)
INSERT INTO #B(Auid,B1) SELECT 1,'' UNION ALL
SELECT 2,'' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 3,'A'
; with sel
as(
select a.auid,b1 from #A as a left join #B as b
on a.Auid=b.Auid
)
select auid,sum(case when B1 IS null then 0 else case when b1='' then 0 else 1 end end) AS [count] from sel
group by Auid
/*
(4 row(s) affected)(5 row(s) affected)
auid count
----------- -----------
1 0
2 1
3 2
4 0(4 row(s) affected)*/
介不介意问下,那种GUID(全球唯一标识)形式的列适合做索引吗?
sum(CASE WHEN b1<>'' AND b1 IS NOT NULL THEN 1 ELSE 0 END)
FROM #a a LEFT JOIN #b b ON a.auid=b.auid
GROUP BY a.auid