求一个视图 mssql2005

aday2005 2018-01-26 05:06:09
原表数据 日期、小组成员、项目种类均不确定会再增加 比如以后小组可能会再添加杜、牧，项目再添穿、行。

日期 小组 项目
2010/1/1 赵 吃,喝
2010/1/1 钱 玩
2010/1/1 孙 吃
2010/1/2 李 喝,乐
2010/1/3 王 吃,乐
2010/1/3 赵 玩
2010/1/3 王 乐,玩
2010/1/4 李 吃,喝,玩
2010/1/4 王 乐
2010/1/4 赵 吃
2010/1/4 王 喝,乐
2010/1/4 李 玩

目的数据

日期 小组 吃 喝 玩 乐 ……
2010/1/1 赵 1 1
2010/1/1 钱 1
2010/1/1 孙 1
2010/1/2 李 1
2010/1/3 王 1 1 2
2010/1/3 赵 1
2010/1/4 李 1 1 2
2010/1/4 王 1 2
2010/1/4 赵 1
...全文
701 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
学海无涯-回头是岸 2018-01-29
  • 打赏
  • 举报
 回复
sql server 声明变量是这种的嘛
aday2005 2018-01-29
  • 打赏
  • 举报
 回复
引用 9 楼 baidu_36457652 的回复:
[quote=引用 7 楼 aday2005 的回复:] [quote=引用 4 楼 baidu_36457652 的回复:] CREAT function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10)) returns @t table(id int IDENTITY,val VARCHAR(200) ) as begin declare @getIndex int set @getIndex=charindex(@split,@str) while(@getIndex<>0) begin insert into @t(val) values (substring(@str,1,@getIndex-1)) set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'') set @getIndex=charindex(@split,@str) end insert into @t(val) values (@str) return EDN 这个自定义函数的代码
[Err] 42000 - [SQL Server]关键字 'function' 附近有语法错误。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。 42000 - [SQL Server]必须声明标量变量 "@str"。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。 [/quote]你是什么数据库[/quote] mssql2008R2
中国风 2018-01-29
  • 打赏
  • 举报
 回复
WHERE ItemName>'' 加上条件过滤为空字符和空值NULL 
DECLARE @Sql NVARCHAR(max)=''
--不确定项目名称时要分拆项目
SELECT  
@Sql+=','+'SUM(SIGN(CHARINDEX('','+ItemName+','','',''+项目+'',''))) AS '+QUOTENAME(ItemName)
FROM (SELECT DISTINCT SUBSTRING(a.项目,b.number,CHARINDEX(',',a.项目+',',b.number)-b.number) AS ItemName
FROM #1 AS a
   , master.dbo.spt_values AS b
WHERE b.type='P' AND CHARINDEX(',',','+a.项目,b.number)=b.number)AS t
WHERE ItemName>''
;
 
EXEC ('SELECT [日期],[小组]'+@Sql+' FROM #1 GROUP BY [日期],[小组]')
aday2005 2018-01-29
  • 打赏
  • 举报
 回复
引用 5 楼 roy_88 的回复:
e.g. 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([日期] Date,[小组] nvarchar(21),[项目] nvarchar(25))
Insert #1
select '2010/1/1',N'赵',N'吃,喝' union all
select '2010/1/1',N'钱',N'玩' union all
select '2010/1/1',N'孙',N'吃' union all
select '2010/1/2',N'李',N'喝,乐' union all
select '2010/1/3',N'王',N'吃,乐' union all
select '2010/1/3',N'赵',N'玩' union all
select '2010/1/3',N'王',N'乐,玩' union all
select '2010/1/4',N'李',N'吃,喝,玩' union all
select '2010/1/4',N'王',N'乐' union all
select '2010/1/4',N'赵',N'吃' union all
select '2010/1/4',N'王',N'喝,乐' union all
select '2010/1/4',N'李',N'玩'
GO

DECLARE @Sql NVARCHAR(max)=''
--不确定项目名称时要分拆项目
SELECT  
@Sql+=','+'SUM(SIGN(CHARINDEX('','+ItemName+','','',''+项目+'',''))) AS '+QUOTENAME(ItemName)
FROM (SELECT DISTINCT SUBSTRING(a.项目,b.number,CHARINDEX(',',a.项目+',',b.number)-b.number) AS ItemName
FROM #1 AS a
   , master.dbo.spt_values AS b
WHERE b.type='P' AND CHARINDEX(',',','+a.项目,b.number)=b.number )AS t
;

EXEC ('SELECT [日期],[小组]'+@Sql+' FROM #1 GROUP BY [日期],[小组]')
/*
日期	小组	吃	喝	乐	玩
2010-01-02	李	0	1	1	0
2010-01-04	李	1	1	0	2
2010-01-01	钱	0	0	0	1
2010-01-01	孙	1	0	0	0
2010-01-03	王	1	0	2	1
2010-01-04	王	0	1	2	0
2010-01-01	赵	1	1	0	0
2010-01-03	赵	0	0	0	1
2010-01-04	赵	1	0	0	0
*/
我实际的表内 有的行 项目字段 没有值 ，不能作为列名，所以报错了，项目字段为空的，需要把空值的合计进 '其它'列 ，该怎么改呢
aday2005 2018-01-29
  • 打赏
  • 举报
 回复
引用 11 楼 baidu_36457652 的回复:
sql server 声明变量是这种的嘛
知道问题了 我实际的表内 有的行 项目字段 没有值 ，不能作为列名，所以报错了，项目字段为空的，需要把空值的值改成 '其它'
aday2005 2018-01-27
  • 打赏
  • 举报
 回复
引用 4 楼 baidu_36457652 的回复:
CREAT function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10)) returns @t table(id int IDENTITY,val VARCHAR(200) ) as begin declare @getIndex int set @getIndex=charindex(@split,@str) while(@getIndex<>0) begin insert into @t(val) values (substring(@str,1,@getIndex-1)) set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'') set @getIndex=charindex(@split,@str) end insert into @t(val) values (@str) return EDN 这个自定义函数的代码
[Err] 42000 - [SQL Server]关键字 'function' 附近有语法错误。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。 42000 - [SQL Server]必须声明标量变量 "@str"。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。
aday2005 2018-01-27
  • 打赏
  • 举报
 回复
引用 5 楼 roy_88 的回复:
e.g. 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([日期] Date,[小组] nvarchar(21),[项目] nvarchar(25))
Insert #1
select '2010/1/1',N'赵',N'吃,喝' union all
select '2010/1/1',N'钱',N'玩' union all
select '2010/1/1',N'孙',N'吃' union all
select '2010/1/2',N'李',N'喝,乐' union all
select '2010/1/3',N'王',N'吃,乐' union all
select '2010/1/3',N'赵',N'玩' union all
select '2010/1/3',N'王',N'乐,玩' union all
select '2010/1/4',N'李',N'吃,喝,玩' union all
select '2010/1/4',N'王',N'乐' union all
select '2010/1/4',N'赵',N'吃' union all
select '2010/1/4',N'王',N'喝,乐' union all
select '2010/1/4',N'李',N'玩'
GO

DECLARE @Sql NVARCHAR(max)=''
--不确定项目名称时要分拆项目
SELECT  
@Sql+=','+'SUM(SIGN(CHARINDEX('','+ItemName+','','',''+项目+'',''))) AS '+QUOTENAME(ItemName)
FROM (SELECT DISTINCT SUBSTRING(a.项目,b.number,CHARINDEX(',',a.项目+',',b.number)-b.number) AS ItemName
FROM #1 AS a
   , master.dbo.spt_values AS b
WHERE b.type='P' AND CHARINDEX(',',','+a.项目,b.number)=b.number )AS t
;

EXEC ('SELECT [日期],[小组]'+@Sql+' FROM #1 GROUP BY [日期],[小组]')
/*
日期	小组	吃	喝	乐	玩
2010-01-02	李	0	1	1	0
2010-01-04	李	1	1	0	2
2010-01-01	钱	0	0	0	1
2010-01-01	孙	1	0	0	0
2010-01-03	王	1	0	2	1
2010-01-04	王	0	1	2	0
2010-01-01	赵	1	1	0	0
2010-01-03	赵	0	0	0	1
2010-01-04	赵	1	0	0	0
*/
[Err] 42000 - [SQL Server]必须声明标量变量 "@Sql"。
学海无涯-回头是岸 2018-01-27
  • 打赏
  • 举报
 回复
引用 7 楼 aday2005 的回复:
[quote=引用 4 楼 baidu_36457652 的回复:] CREAT function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10)) returns @t table(id int IDENTITY,val VARCHAR(200) ) as begin declare @getIndex int set @getIndex=charindex(@split,@str) while(@getIndex<>0) begin insert into @t(val) values (substring(@str,1,@getIndex-1)) set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'') set @getIndex=charindex(@split,@str) end insert into @t(val) values (@str) return EDN 这个自定义函数的代码
[Err] 42000 - [SQL Server]关键字 'function' 附近有语法错误。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。 42000 - [SQL Server]必须声明标量变量 "@str"。 42000 - [SQL Server]必须声明标量变量 "@split"。 42000 - [SQL Server]必须声明表变量 "@t"。 [/quote]你是什么数据库
中国风 2018-01-26
  • 打赏
  • 举报
 回复
项目不确定时要用动态，有存储过程封装动态语句#5 确定时用视图 e.g. 
CREATE VIEW v1
AS
SELECT [日期]
     , [小组]
     , SUM(SIGN(CHARINDEX(',吃,', ','+项目+','))) AS [吃]
     , SUM(SIGN(CHARINDEX(',喝,', ','+项目+','))) AS [喝]
     , SUM(SIGN(CHARINDEX(',乐,', ','+项目+','))) AS [乐]
     , SUM(SIGN(CHARINDEX(',玩,', ','+项目+','))) AS [玩]
FROM #1
GROUP BY [日期]
       , [小组];
中国风 2018-01-26
  • 打赏
  • 举报
 回复
e.g.
use Tempdb

go

--> --> 中国风(Roy)生成測試數據

 

if not object_id(N'Tempdb..#1') is null

	drop table #1

Go

Create table #1([日期] Date,[小组] nvarchar(21),[项目] nvarchar(25))

Insert #1

select '2010/1/1',N'赵',N'吃,喝' union all

select '2010/1/1',N'钱',N'玩' union all

select '2010/1/1',N'孙',N'吃' union all

select '2010/1/2',N'李',N'喝,乐' union all

select '2010/1/3',N'王',N'吃,乐' union all

select '2010/1/3',N'赵',N'玩' union all

select '2010/1/3',N'王',N'乐,玩' union all

select '2010/1/4',N'李',N'吃,喝,玩' union all

select '2010/1/4',N'王',N'乐' union all

select '2010/1/4',N'赵',N'吃' union all

select '2010/1/4',N'王',N'喝,乐' union all

select '2010/1/4',N'李',N'玩'

GO



DECLARE @Sql NVARCHAR(max)=''

--不确定项目名称时要分拆项目

SELECT  

@Sql+=','+'SUM(SIGN(CHARINDEX('','+ItemName+','','',''+项目+'',''))) AS '+QUOTENAME(ItemName)

FROM (SELECT DISTINCT SUBSTRING(a.项目,b.number,CHARINDEX(',',a.项目+',',b.number)-b.number) AS ItemName

FROM #1 AS a

   , master.dbo.spt_values AS b

WHERE b.type='P' AND CHARINDEX(',',','+a.项目,b.number)=b.number )AS t

;



EXEC ('SELECT [日期],[小组]'+@Sql+' FROM #1 GROUP BY [日期],[小组]')

/*

日期	小组	吃	喝	乐	玩

2010-01-02	李	0	1	1	0

2010-01-04	李	1	1	0	2

2010-01-01	钱	0	0	0	1

2010-01-01	孙	1	0	0	0

2010-01-03	王	1	0	2	1

2010-01-04	王	0	1	2	0

2010-01-01	赵	1	1	0	0

2010-01-03	赵	0	0	0	1

2010-01-04	赵	1	0	0	0

*/
学海无涯-回头是岸 2018-01-26
  • 打赏
  • 举报
 回复
CREAT function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10)) returns @t table(id int IDENTITY,val VARCHAR(200) ) as begin declare @getIndex int set @getIndex=charindex(@split,@str) while(@getIndex<>0) begin insert into @t(val) values (substring(@str,1,@getIndex-1)) set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'') set @getIndex=charindex(@split,@str) end insert into @t(val) values (@str) return EDN 这个自定义函数的代码
学海无涯-回头是岸 2018-01-26
  • 打赏
  • 举报
 回复
引用 2 楼 aday2005 的回复:
[quote=引用 1 楼 baidu_36457652 的回复:] 

select 日期,小组,
sum(case when 项目like '%喝%' then 1 else 0 end)  "喝",
sum(case when 项目like '%玩%' then 1 else 0 end)  "玩",
sum(case when 项目like '%乐%' then 1 else 0 end)  "乐",
sum(case when 项目like '%乐%' then 1 else 0 end)  "吃"
from table  group by 日期,小组
我需要动态列的 并不只是吃喝玩乐4个类，这只是个需求例子，实际上几十上百类呢，而且不定期更新[/quote] 那得先把 项目里面的拆开 然和做个去重. 

if  object_id(N'Tempdb.DBO.#T1') is not null
    drop table #T1
Go
Create table #T1([RQ] nvarchar(23),[XM] nvarchar(23),XMMC VARCHAR(200))
Insert INTO #T1 
SELECT '2010/1/1','赵','吃,喝' UNION ALL
SELECT '2010/1/1','钱','玩'	UNION ALL
SELECT '2010/1/1','孙','吃'	UNION ALL
SELECT '2010/1/2','李','喝,乐'	UNION ALL
SELECT '2010/1/3','王','吃,乐'	UNION ALL
SELECT '2010/1/3','赵','玩'	UNION ALL
SELECT'2010/1/3','王	','乐,玩'UNION ALL	
SELECT '2010/1/4','李','吃,喝,玩'UNION ALL
SELECT '2010/1/4','王','乐'	UNION ALL
SELECT '2010/1/4','赵','吃'	UNION ALL
SELECT '2010/1/4','王','喝,乐' UNION ALL	
SELECT '2010/1/4','李','玩'
--SELECT * FROM #T1
DECLARE @COL1 VARCHAR(290),@sql1 NVARCHAR(max)
select @COL1=ISNULL(@COL1+','+CHAR(32)+'[','[')+CAST(VAL AS VARCHAR)+']'  FROM (
SELECT DISTINCT VAL FROM (SELECT  * FROM #T1 A CROSS APPLY PX_ALL.DBO.Func_SplitStr(XMMC,',')B) A) C1
PRINT @COL1
--SELECT * FROM (SELECT A.RQ,A.XM,B.VAL FROM #T1 A CROSS APPLY PX_ALL.DBO.Func_SplitStr(XMMC,',')B) M1

SET @sql1 = 
'SELECT * FROM (SELECT A.RQ,A.XM,B.VAL FROM #T1 A CROSS APPLY PX_ALL.DBO.Func_SplitStr(XMMC,'','')B) M1
PIVOT (COUNT(VAL)FOR  VAL IN( '+ @COL1 +' )) AS M2'
PRINT @sql1
EXEC sp_executesql  @sql1;
/*
RQ	XM	吃	乐	喝	玩
2010/1/2	李	0	1	1	0
2010/1/4	李	1	0	1	2
2010/1/1	钱	0	0	0	1
2010/1/1	孙	1	0	0	0
2010/1/3	王	1	1	0	0
2010/1/4	王	0	2	1	0
2010/1/3	王		0	1	0	1
2010/1/1	赵	1	0	1	0
2010/1/3	赵	0	0	0	1
2010/1/4	赵	1	0	0	0
*/
aday2005 2018-01-26
  • 打赏
  • 举报
 回复
引用 1 楼 baidu_36457652 的回复:

select 日期,小组,
sum(case when 项目like '%喝%' then 1 else 0 end)  "喝",
sum(case when 项目like '%玩%' then 1 else 0 end)  "玩",
sum(case when 项目like '%乐%' then 1 else 0 end)  "乐",
sum(case when 项目like '%乐%' then 1 else 0 end)  "吃"
from table  group by 日期,小组
我需要动态列的 并不只是吃喝玩乐4个类，这只是个需求例子，实际上几十上百类呢，而且不定期更新
学海无涯-回头是岸 2018-01-26
  • 打赏
  • 举报
 回复 

select 日期,小组,
sum(case when 项目like '%喝%' then 1 else 0 end)  "喝",
sum(case when 项目like '%玩%' then 1 else 0 end)  "玩",
sum(case when 项目like '%乐%' then 1 else 0 end)  "乐",
sum(case when 项目like '%乐%' then 1 else 0 end)  "吃"
from table  group by 日期,小组
MSSQL用户表、视图、存储过程转SQL
MSSQL用户表、视图、存储过程转SQL MSSQL用户表、视图、存储过程转SQL
VB 演示创建视图 使用视图保存数据.rar
VB 演示创建视图 以及使用视图保存数据，数据库选择了MSSQL，根据各数据库字段创建视图，并使用这个视图进行数据保存，测试前请确保数据库连接成功。
MySQL 视图
Re： MySQL 视图=========================# 什么是视图 数据库中的视图是一个虚拟表（逻辑存在），同真实的表（物理存在）一样，视图包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所应用的表，并且在引用视图时动态生成。 # 视图的概念 保存在数据库中的查询语句 # 创建视图的目的 隐藏数据复杂性、实现数据访问安全、友好界面 # 视图的操作 创建、查看、修改、更新、删除等等-------------------------------------------------- 
sqlsever 刷新（更新）数据库中的所有视图
sqlsever 刷新（更新）数据库中的所有的视图，用于表添加字段，但是视图还是没有字段
mssql数据库维护工具 V2.0
Mssql数据库维护工具 v2.0 - 本工具由酉阳网盾科技独家开发 官方更新下载地址:http://www.itoa.cc/Download/DownShow.asp?id=57 主要功能如下: 1、目前测试支持Mssql 2000 、Mssql 2005、Mssql 2008。 2、修复置疑状态的数据库 3、备份数据库到指定位置 4、恢复备份文件到指名的数据库名,支持文件拖放。 5、支持数据库结构、视图、存储过程的校验与修复。 7、系统默认进行10次自动修复(建议先进行快速修复，再进行深度修复) 8、本工具便不保证100%的能修复好你的数据库， 如果你的数据库深度损坏可以联系我们在线工程师进行手工检查与修复。
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章