34,594
社区成员
发帖
与我相关
我的任务
分享
/*
gid cnt
1 2
2 2
20 0
4 0
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(gid INT,[name] NVARCHAR(10))
INSERT INTO t
SELECT 1,'张三'
UNION ALL SELECT 2,'李四'
UNION ALL SELECT 1,'张二麻子'
UNION ALL SELECT 2,'理查德'
UNION ALL SELECT 3,NULL
UNION ALL SELECT 4,''
GO
--1. 增加一个表值函数
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL DROP FUNCTION [dbo].[Fun_Split]
GO
-- =============================================
-- Author : yenange
-- Create date: 2014-03-04
-- Description: 切分字符串
-- Example : SELECT * FROM [dbo].[Fun_Split]('a,b,d,c',',')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
(
@str NVARCHAR(MAX),
@separator NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() OVER (order by (select 0)) AS rowNum, B.id
FROM (
SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
) A
OUTER APPLY(
SELECT id = N.v.value('.', 'nvarchar(max)')
FROM A.[value].nodes('/v') N(v)
) B
WHERE ISNULL(B.id,'')!=''
)
GO
--2. 查询
SELECT
fs.id AS gid
,SUM(CASE WHEN t.name>'' THEN 1 ELSE 0 END) AS cnt
FROM dbo.Fun_Split('1,2,4,20',',') AS fs
LEFT JOIN t ON fs.id=t.gid
GROUP BY fs.id
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([gid] int,[name] nvarchar(24))
Insert #A
select 1,N'张三' union all
select 2,N'李四' union all
select 1,N'张二麻子' union all
select 2,N'理查德' union all
select 3,null
Go
--测试数据结束
;WITH cte AS (
SELECT 1 AS gid
UNION
SELECT 2 AS gid
UNION
SELECT 4 AS gid
UNION
SELECT 20 AS gid
)
SELECT SUM(CASE WHEN name IS NOT NULL THEN 1
ELSE 0
END) AS cnt ,
cte.gid
FROM cte
LEFT JOIN #A ON cte.gid = #A.gid
GROUP BY cte.gid
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([gid] int,[name] nvarchar(24))
Insert #A
select 1,N'张三' union all
select 2,N'李四' union all
select 1,N'张二麻子' union all
select 2,N'理查德' union all
select 3,null
Go
--测试数据结束
;WITH cte AS (
SELECT 1 AS gid
UNION
SELECT 2 AS gid
UNION
SELECT 3 AS gid
UNION
SELECT 4 AS gid
)
SELECT SUM(CASE WHEN name IS NOT NULL THEN 1
ELSE 0
END) AS cnt ,
cte.gid
FROM cte
LEFT JOIN #A ON cte.gid = #A.gid
GROUP BY cte.gid