用什么方法都行!!!-- 创建统计表 IF OBJECT_ID('dbo.统计表') IS NOT NULL DROP TABLE 统计表
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1
Select * from 统计表
统计表的商品名为n列,不只上面列出的那么多,如何实现n维数组输出,即有多少个字段就输出从一维直到n维的数组!
具体目标:现有一参考值0.25,要输出满足条件的1到n维数组,其中一维数组统计单个商品名出现1的合计数,即每一个商品名出现一个1就算一次。 二维数组统计为从第一列方便面开始起与相邻的茶叶都为1,1的关系(依次组合查询每相邻两个满足条件的关系)。 详细:即从第一行找到最后一行,如发现循环组合满足都为1,1这样关系有5个,即sum记为5,依次再找从第一列方便面开始起与相隔一个位置的面包都为1,1的关系,如满足这样的关系从第一行到最后有3个即sum值记为3,依次类推再从第一列方便面开始与相隔两位置的口香糖组合都为1,1的关系,这样一直找到方便面与最后一列食醋(即第n列)的关系都为1,1的位置,再从开始的第二列循环与之后的每列依次组合查找1,1的关系,直到循环到从榨菜开始与最后一列食醋(第n列)为1,1的关系,二维数组循环完毕。 输出:如每以组合列满足条件的sum值/所有行数>=0.25则输出 该组合商品名称,例如(茶叶,冰糖)组合关系为(1,1)的sum数/总行数>=0.25 则需将结果(茶叶,冰糖)输出到 结果表 的values 字段,三维数组与n维数组算法与二维数组相似 结果表输出样式:(中间以逗号分开) 结果表
values
{方便面,口香糖,牛奶,食醋}
{(茶叶,冰糖)(口香糖,玻璃杯)(榨菜,食醋)}
{(方便面,茶叶,牛奶)(茶叶,口香糖,冰糖)(玻璃杯,食盐,食醋) }
{(n维数组1)(n维数组2)(n维数组3)(n维数组3)(...)(n维数组n)}
请问上面的数据查询如何实现!不知说的请不清楚,请贴出代码!!
CREATE TABLE 统计表 (方便面 INT,茶叶 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可乐 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事书 INT,味素 INT,食盐 INT,榨菜 INT,食醋 INT)
INSERT INTO 统计表
SELECT 1,0,0,0,0,1,0,0,0,1,0,0,1,0,0 UNION ALL
SELECT 1,1,0,0,0,1,0,0,0,0,1,1,0,1,0 UNION ALL
SELECT 0,1,1,1,0,1,1,1,0,1,0,0,1,1,0 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,1,1,0,0 UNION ALL
SELECT 0,1,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,0,0,1,1,0,1,1,0,0,0,0,1,0 UNION ALL
SELECT 1,1,1,0,0,1,0,1,0,0,0,1,1,0,0 UNION ALL
SELECT 0,0,1,0,0,1,0,1,0,0,0,0,1,1,1 UNION ALL
SELECT 1,1,1,0,0,1,0,1,1,0,1,1,0,1,0 UNION ALL
SELECT 1,1,1,1,0,1,1,1,0,1,1,0,1,0,1 UNION ALL
SELECT 0,0,1,1,1,1,0,1,0,1,0,1,1,1,0 UNION ALL
SELECT 1,1,1,1,1,1,0,1,0,1,0,0,1,1,1
Select * from 统计表
统计表的商品名为n列,不只上面列出的那么多,如何实现n维数组输出,即有多少个字段就输出从一维直到n维的数组!
具体目标:现有一参考值0.25,要输出满足条件的1到n维数组,其中一维数组统计单个商品名出现1的合计数,即每一个商品名出现一个1就算一次。 二维数组统计为从第一列方便面开始起与相邻的茶叶都为1,1的关系(依次组合查询每相邻两个满足条件的关系)。 详细:即从第一行找到最后一行,如发现循环组合满足都为1,1这样关系有5个,即sum记为5,依次再找从第一列方便面开始起与相隔一个位置的面包都为1,1的关系,如满足这样的关系从第一行到最后有3个即sum值记为3,依次类推再从第一列方便面开始与相隔两位置的口香糖组合都为1,1的关系,这样一直找到方便面与最后一列食醋(即第n列)的关系都为1,1的位置,再从开始的第二列循环与之后的每列依次组合查找1,1的关系,直到循环到从榨菜开始与最后一列食醋(第n列)为1,1的关系,二维数组循环完毕。 输出:如每以组合列满足条件的sum值/所有行数>=0.25则输出 该组合商品名称,例如(茶叶,冰糖)组合关系为(1,1)的sum数/总行数>=0.25 则需将结果(茶叶,冰糖)输出到 结果表 的values 字段,三维数组与n维数组算法与二维数组相似 结果表输出样式:(中间以逗号分开) 结果表
values
{方便面,口香糖,牛奶,食醋}
{(茶叶,冰糖)(口香糖,玻璃杯)(榨菜,食醋)}
{(方便面,茶叶,牛奶)(茶叶,口香糖,冰糖)(玻璃杯,食盐,食醋) }
{(n维数组1)(n维数组2)(n维数组3)(n维数组3)(...)(n维数组n)}
请问上面的数据查询如何实现!不知说的请不清楚,请贴出代码!!
--建立测试数据
create table food(面包 int, 牛奶 int, 苹果 int, 橙汁 int, 咖啡 int,香烟 int)
insert into food values(1,1,1,1,1,1)
insert into food values(1,1,1,1,1,0)
insert into food values(1,1,1,1,0,0)
insert into food values(1,1,1,0,0,0)
insert into food values(1,1,0,0,0,0)
insert into food values(1,0,0,0,0,0)
insert into food values(1,0,1,0,1,0)
insert into food values(0,1,0,1,0,1)
--建立组合筛选的存储过程
CREATE PROC [dbo].[TESTAAA]
@tblname SYSNAME, --组合条件数据
@allnum INT, --总数
@subnum INT --取几个数进行组合
AS
SET NOCOUNT ON
DECLARE @sql varchar(2000),@allchar varchar(300),@i INT
DECLARE @tmps varchar(500),@tmpq varchar(500),@tmpl varchar(500),@tmpm varchar(500)
SET @sql = 'SELECT TOP ' + CAST(@allnum AS VARCHAR) + ' IDENTITY(INT,1,1) AS ID,'' '' AS vname,col INTO tmptbl FROM '+@tblname
PRINT(@sql)
EXEC(@sql)
update tmptbl set vname = char(ID+96)
SET @i = 1
WHILE @i <= @allnum
BEGIN
SET @tmpl = ISNULL(@tmpl,'') + CHAR(@i + 96)
SET @i = @i + 1
END
SET @tmpl ='''' + @tmpl +''''
SET @i = 1
WHILE @i <= @subnum
BEGIN
SET @tmps = ISNULL(@tmps,'') + char(96+@I) +'.col + '','' + '
SET @tmpq = ISNULL(@tmpq,'') + 'tmptbl ' + char(96+@I) +','
SET @tmpl = 'REPLACE(' + ISNULL(@tmpl,'') + ',' + char(96+@I) + '.vname,'''')'
SET @tmpm = CASE WHEN @i >= 2 THEN ISNULL(@tmpm,'') + ' AND ' + CHAR(95+@i) +'.vname < ' + CHAR(96+@i) + '.vname' END
SET @i = @i + 1
END
SET @tmps = LEFT(@tmps,LEN(@tmps)-8)
SET @tmpq = LEFT(@tmpq,LEN(@tmpq)-1)
SET @sql = 'SELECT ' + @tmps + ' FROM ' + @tmpq + ' WHERE LEN(' + @tmpl + ') = ' + CAST((@allnum- @subnum) AS VARCHAR) + ISNULL(@tmpm,'')
EXEC(@sql)
DROP TABLE tmptbl
GO
--组合条件数据表作为参数传入[TESTAAA]
create table tmp (col varchar(50)) --注意COL名字不能变,如果要变要修改上面那个存储过程相关部分
insert into tmp select '面包'
insert into tmp select '牛奶'
insert into tmp select '苹果'
insert into tmp select '橙汁'
insert into tmp select '咖啡'
insert into tmp select '香烟'
--建立表存放全组合条件
create table tmp2 (col varchar(8000),pct float)
declare @i int,@j int
set @i = 1
set @j = 6
while (@i <= @j)
begin
insert into tmp2 exec [TESTAAA] 'tmp',@j,@i
set @i = @i + 1
end--计算每种组合达成购买条件的概率
declare cur_t cursor for
select col from tmp2
declare @col varchar(80)
declare @sql varchar(8000)
open cur_t
fetch next from cur_t into @col
while (@@fetch_status = 0)
begin
set @sql = 'update tmp2 set pct = (select count(*) as num from food where (' +
replace(@col,',',' = 1 and ') + ' = 1 ' +
'))/(select cast(count(*) as float) from food) from food where col = ''' + @col + ''''
exec(@sql)
fetch next from cur_t into @col
end
close cur_t
deallocate cur_t--显示结果
select case ID when 0 then
'{'+(select col + ',' from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) t where t.ID = a.ID for xml path('')) + '}'
else
'{'+(select '(' + col + ')' from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) t where t.ID = a.ID for xml path('')) + '}'
end
from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) a
group by ID--结果集
/*
{面包,牛奶,苹果,橙汁,咖啡,香烟,}
{(面包,牛奶)(面包,苹果)(面包,橙汁)(面包,咖啡)(牛奶,苹果)(牛奶,橙汁)(牛奶,咖啡)(牛奶,香烟)(苹果,橙汁)(苹果,咖啡)(橙汁,咖啡)(橙汁,香烟)}
{(面包,牛奶,苹果)(面包,牛奶,橙汁)(面包,牛奶,咖啡)(面包,苹果,橙汁)(面包,苹果,咖啡)(面包,橙汁,咖啡)(牛奶,苹果,橙汁)(牛奶,苹果,咖啡)(牛奶,橙汁,咖啡)(牛奶,橙汁,香烟)(苹果,橙汁,咖啡)}
{(面包,牛奶,苹果,橙汁)(面包,牛奶,苹果,咖啡)(面包,牛奶,橙汁,咖啡)(面包,苹果,橙汁,咖啡)(牛奶,苹果,橙汁,咖啡)}
{(面包,牛奶,苹果,橙汁,咖啡)}
*/
第 3 行: 'xml' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 5
在关键字 'for' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: 'a' 附近有语法错误。
在2005上写的
--显示结果
select case ID when 0 then
'{'+(select col + ',' from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) t where t.ID = a.ID for xml path('')) + '}'
else
'{'+(select '(' + col + ')' from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) t where t.ID = a.ID for xml path('')) + '}'
end
from (select len(col)-len(replace(col,',','')) as ID,* from tmp2 where pct >=0.25 ) a
group by ID--sql2000中要替换成下面语句:
Create function dbo.FC_Str(@col1 varchar(8000))
returns varchar(8000)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+',('+cast(col as varchar) + ')' from tmp2 where cast(len(col)-len(replace(col,',','')) as varchar)=@col1
return('{' + stuff(@i,1,1,'') + '}')
end select ID,dbo.FC_Str(ID) from (
select cast(len(col)-len(replace(col,',','')) as varchar) as ID,* from tmp2 ) t
group by ID