34,576
社区成员
发帖
与我相关
我的任务
分享
-- 猜你的数据是这样的
WITH a(name,sex,[index]) AS (
SELECT '小明','男', 3 UNION ALL
SELECT '小红','女', 4
)
,b (num) AS (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4
)
-- 这是你原先的查询
-- SELECT * FROM b LEFT JOIN a ON b.num = a.[index]
-- 先统计数量再联合查询最方便了
SELECT c.num, a.*, c.[count]
FROM (
SELECT num,
COUNT(*) AS [count]
FROM b
GROUP BY num
) c
LEFT JOIN a
ON c.num = a.[index]
num name sex index count
----------- ---- ---- ----------- -----------
1 NULL NULL NULL 1
2 NULL NULL NULL 1
3 小明 男 3 3
4 小红 女 4 1
SELECT
b.num
,c.[name]
,c.sex
,c.[index]
,ISNULL(c.[COUNT],1)[COUNT]
FROM
b
LEFT JOIN
(SELECT [name],sex,[index],COUNT(1)[COUNT]FROM a GROUP BY[name],sex,[index])
c ON b.num=c.[index]
试下