IF OBJECT_ID('dbo.統(tǒng)計(jì)表') IS NOT NULL DROP TABLE 統(tǒng)計(jì)表
CREATE TABLE 統(tǒng)計(jì)表 (方便面 INT,茶葉 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可樂 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事書 INT,味素 INT,食鹽 INT,榨菜 INT,食醋 INT)
INSERT INTO 統(tǒng)計(jì)表
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 統(tǒng)計(jì)表
統(tǒng)計(jì)表的商品名為n列，不只上面列出的那么多，如何實(shí)現(xiàn)n維數(shù)組輸出，即有多少個字段就輸出從一維直到n維的數(shù)組！
具體目標(biāo)：現(xiàn)有一參考值0.25，要輸出滿足條件的1到n維數(shù)組，其中一維數(shù)組統(tǒng)計(jì)單個商品名出現(xiàn)1的合計(jì)數(shù)，即每一個商品名出現(xiàn)一個1就算一次。
二維數(shù)組統(tǒng)計(jì)為從第一列方便面開始起與相鄰的茶葉都為1，1的關(guān)系（依次組合查詢每相鄰兩個滿足條件的關(guān)系）。
詳細(xì)：即從第一行找到最后一行，如發(fā)現(xiàn)循環(huán)組合滿足都為1，1這樣關(guān)系有5個，即sum記為5，依次再找從第一列方便面開始起與相隔一個位置的面包都為1，1的關(guān)系，如滿足這樣的關(guān)系從第一行到最后有3個即sum值記為3，依次類推再從第一列方便面開始與相隔兩位置的口香糖組合都為1，1的關(guān)系，這樣一直找到方便面與最后一列食醋（即第n列）的關(guān)系都為1，1的位置，再從開始的第二列循環(huán)與之后的每列依次組合查找1，1的關(guān)系，直到循環(huán)到從榨菜開始與最后一列食醋（第n列）為1，1的關(guān)系，二維數(shù)組循環(huán)完畢。
輸出：如每以組合列滿足條件的sum值/所有行數(shù)>=0.25則輸出 該組合商品名稱，例如（茶葉，冰糖）組合關(guān)系為（1，1）的sum數(shù)/總行數(shù)>=0.25 則需將結(jié)果（茶葉,冰糖）輸出到 結(jié)果表 的values 字段，三維數(shù)組與n維數(shù)組算法與二維數(shù)組相似
結(jié)果表輸出樣式：（中間以逗號分開）
結(jié)果表
values
{方便面,口香糖,牛奶,食醋}
{（茶葉,冰糖）（口香糖,玻璃杯）（榨菜,食醋）}
{（方便面,茶葉,牛奶）（茶葉,口香糖,冰糖）（玻璃杯,食鹽,食醋） }
{（n維數(shù)組1）（n維數(shù)組2）（n維數(shù)組3）（n維數(shù)組3）（...）（n維數(shù)組n）}
請問上面的數(shù)據(jù)查詢?nèi)绾螌?shí)現(xiàn)！請貼出代碼??！
IF OBJECT_ID('dbo.統(tǒng)計(jì)表') IS NOT NULL DROP TABLE 統(tǒng)計(jì)表
CREATE TABLE 統(tǒng)計(jì)表 (方便面 INT,茶葉 INT,面包 INT,口香糖 INT,牛奶 INT,玻璃杯 INT,可樂 INT,冰糖 INT,牙膏 INT,牙刷 INT,故事書 INT,味素 INT,食鹽 INT,榨菜 INT,食醋 INT)
INSERT INTO 統(tǒng)計(jì)表
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
Go
Create PROC xb_GetArray(@維數(shù) INT, @系數(shù) float ,@結(jié)果 VARCHAR(1000) out )
AS
SET NOCOUNT ON
IF OBJECT_ID('dbo.Tmp') IS NOT NULL DROP TABLE dbo.Tmp
CREATE TABLE Tmp(id INT,NAME VARCHAR(20))
INSERT Tmp SELECT colid,NAME FROM syscolumns s WHERE id=OBJECT_ID('統(tǒng)計(jì)表')
IF OBJECT_ID('tempdb.dbo.#') IS NOT NULL DROP TABLE #
CREATE TABLE #(ws INT,Result VARCHAR(1000))
DECLARE @num INT
DECLARE @f FLOAT
DECLARE @sql nVARCHAR(1000)
DECLARE @id VARCHAR(100),@w VARCHAR(100),@wh VARCHAR(100),@c VARCHAR(100)
DECLARE @Step int ,@i INT ,@j INT ,@s INT,@l INT
SET @f=@系數(shù)
SELECT @num=count(1) FROM tmp
SET @i=1
SET @Step=@維數(shù)
set @結(jié)果=''
WHILE @i<=@num
BEGIN
SET @wh=REPLICATE('1,',@Step)
SET @j=@i
WHILE @j<=@num-(@Step-1)
BEGIN
SELECT @l=1,@id='',@w='',@c=NULL
WHILE @l<=@Step-1
BEGIN
SET @id=@id+LTRIM(@j+@l)+','
SET @l=@l+1
END
SELECT @id=ltrim(@i)+','+@id
SELECT @w=@w+'+ltrim('+NAME+')+'+''',''',@c=ISNULL(@c+',','')+Name FROM tmp WHERE CHARINDEX(','+LTRIM(id)+',',','+@id)>0 ORDER BY id
SET @w=STUFF(@w,1,1,'')
SET @sql=N'if ((select count(*)*1.0 from 統(tǒng)計(jì)表 where '+@w+'='''+@wh+''')/'+LTRIM(@num)+'>' +LTRIM(@f) +') insert # select '+ltrim(@Step)+',''('+ @c+'）,'''
EXEC(@sql)
SET @j=@j+1
END
SET @i=@i+1
END
SET @sql=NULL
SELECT @sql=ISNULL(@sql+'','')+result FROM (SELECT DISTINCT result FROM # ) aa
SET @結(jié)果='{'+@sql+'}'
GO
DECLARE @sql VARCHAR(1000)
exec xb_GetArray 2,0.3,@sql OUT
SELECT @sql 二維
--result
/*
一維
---------------------------------------------------------------------------
{(冰糖）,(玻璃杯）,(茶葉）,(方便面）,(面包）,(食鹽）,(味素）,(牙刷）,(榨菜）,}
（所影響的行數(shù)為 1 行）*/
DECLARE @sql VARCHAR(1000)
exec xb_GetArray 2,0.3,@sql OUT
SELECT @sql 二維
/*二維
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{(冰糖,食鹽）,(冰糖,榨菜）,(玻璃杯,冰糖）,(玻璃杯,食鹽）,(玻璃杯,味素）,(玻璃杯,牙刷）,(玻璃杯,榨菜）,(茶葉,冰糖）,(茶葉,玻璃杯）,(茶葉,面包）,(茶葉,食鹽）,(茶葉,榨菜）,(方便面,冰糖）,(方便面,玻璃杯）,(方便面,茶葉）,(方便面,食鹽）,(面包,冰糖）,(面包,玻璃杯）,(面包,食鹽）,(面包,榨菜）,(食鹽,榨菜）,(牙刷,食鹽）,}
（所影響的行數(shù)為 1 行）*/