判断是否有审核的项目(案例)
网友问:
为问题创建表,并添加相关数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE [dbo].[Goods]( [Projname] [nvarchar](10) NULL, [version] [nvarchar](10) NULL, [state] [nvarchar](3) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[Goods] ([Projname],[version],[state]) VALUES (N'A项目',N'启动会版',N'已审核'), (N'A项目',N'方案版',N'已审核'), (N'A项目',N'施工图版',N'未审核'), (N'B项目',N'启动会版',N'未审核'), (N'B项目',N'方案版',N'未审核'), (N'B项目',N'施工图版',N'未审核') GO
以下是Insus.NET实现方法,创建一个自定义函数:
接下来,就可以使用这个自定义函数时进行查询:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
;WITH cte_proj AS ( SELECT DISTINCT [Projname] FROM [dbo].[Goods] ) SELECT [Projname], [dbo].[svf_IsApproved]([Projname]) AS [是否审核] FROM cte_proj;
以下内容于2019-06-10 10:05:45.930更新:
有另一位网友实现方法,可以借鉴与参考:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT [Projname],IIF(MAX(CASE WHEN [state] = N'已审核' THEN 1 ELSE 0 END) = 1,N'是',N'否') FROM [dbo].[Goods] GROUP BY [Projname]